 Good morning. Good morning. Good morning. Nice. So we are here today to present a talk which is active record, can't do it, Arial can. Hopefully some of you might know Arial. Anyone knows Arial? What is Arial? Great. Okay. Nice. How many know Rails? Okay. Nice. I am Vipul. I am Prathamesh. And we are supposed to say that in the other order, but fine. We work for a company called as BigBinary, which is based out of Miami. And it's a remote company, and we work from different places. Like I work from a home. We like to do a lot of videos, so you can check out our website, videos.bigbinary.com. We do a lot of tutorials. We are also currently looking for people, so you should check out bigbinary.com. Also, I don't know if anyone invited you to the conference. So welcome to the second conference.name. I don't know what that was, and we hopefully read till the end. Because this is a very big name. I don't know how to say that big name. So that's why conference.name is pretty easier. We happen to live at this place called as Pune. Anyone from Pune over here? Yeah. Very nice this time. Which is somewhere near Mumbai. And it was like a week back that we started, and it was just 167 hours that took us to get here. Walking. It was very leader walk. We traveled more than constant. Yeah. Back in Pune, we do regular meetups and lots of different events under Pune.rb. So if any time you are around in Pune, come visit us at our meetups. We last year started with Deccan RubyConf, which is a kind of regional conf for Pune. And it's more inclined towards fun. I don't know. Gotham nodes. We do that conf. I'm sorry? Yeah. Last year, in this conf, we launched something called as Ruby India. How many of you heard of Ruby India? Quite many. Ruby India was to highlight content from the Ruby Indian community, like send out links for submitting CFPs. What is happening in meetups? Different meetups and all those things. Do subscribe on Ruby India. Our next issue comes today again. Along with Ruby India in the last Deccan RubyConf we announced podcast.rubyindia.org. So do visit again podcasts where we interview a lot of people in the Indian community. Recently we interviewed C42. Anyone? So do check out the podcast. We interviewed Gotham and many other people from the Indian community. And we like to interview them and ask different things about how the Indian Ruby community, how it came into being. Also, this is not a magenta. So that's all about what we are going to present. Before I begin, I'll just let you know. So Prathamesh, I've been working on pretty funky things these days. This is one of the start-ups that I started called as Backpackers. So I've been travelling a lot recently and I've travelled and I like to stay at different places since my job is remote. So I also made a lot of friends who backpack. That's why Backpackers. It's an actual company which is registered. So these are some of the things that my start-up does. I like to help out my friends in basic task management. Finding good locations for when they are roaming around in different locations. Finding good places to visit. Finding good food centres because I don't know. We want food. We don't know what to eat. And finding good and cheap hostels or hotels around. But all of these tasks are very basic tasks. Every app has it. What is your unique feature? We also have many magical features. Luggage is always lost. So we also have this very magical feature called a Spind Your Lost Bag. So anyone who has heard of RKH Lost Bag, sorry, RKH Bag, only one person who has the tutor handle. So we also have these magical features which allow us to have these many happy customers who find their bags when they are lost for two weeks or I don't know. And our customers are mind blown with our features that are there. So before I go into the other features, I'll give you an idea of what I like to use, a lot of active record and all those things. So this is something that I have in my app. I have a traveler class which takes care of reviews and bookings. I have a task which belongs to a location like what I want to do for that particular location. I have a location which will keep information about the location and it will have reviews. It will have different bookings like for hostels. And it can have nearby locations, which is a self-join on that particular model. I also have bookings which are related to locations. Very sophisticated code. And you can't believe it but I actually have this app right now and it's in production. It's actually running. So do visit my website which is backpackers.herokuapp. So just fire up, I don't know if the instance is running or not because it's production. And I actually have live 31 users. Looks like your app is scaling. Yeah, which is like, I don't know if you have ever seen 31 users in your apps because it's production. So, I'm sorry. Yeah, I mean, yeah. I mean, 10, okay, I understand 10, but I mean, 10 are like from your company, but 31 is like a lot. And I'm a single person company, you know? Nice. And since I have 31 users on a single Heroku instance, I like to have performance like performance is very important when you have 31 users. So I like to scale my app a lot. That's why I use raw SQL, you know? Because active record is very slow for me. It's, I don't know. That's why I have very, you know, extremely awesome features, awesome queries that I write and very, you know, chilled out SQL queries that I use like something like this. So, you see, if I have this query over here which does searching for food, like searching for a QZ, if I'm going in some other country and I want to find Indian food, which is very hard. I don't know if they don't even know what is Indian food. So I like to have these raw SQL queries that I have in my app, which help me find that. So you can find, see, I'm using I like over here since I'm using Postgres. So you can find your Indian food or related cuisine. So this kind of works well. Yeah, this kind of works well. But you know what? It does work. And it scales. Yeah, I know. But it has some problems. So have you thought about the problems? What problems? So it has raw SQL. That is the first thing. Then it has some... It has performance, I don't know. Yeah, but it has some database-specific things also. Like I like. And if you want to move to some other database, I don't know whether you will move from Postgres to other database. But if you want to, then it will cause problems because that query will not be reusable. So in that case, Errol will help you. Have you heard of Errol? No, what? What is Errol? Errol is a Ruby library. It is used for generating SQL. So it is actually based on relational algebra. It has concepts like abstract syntax tree, nodes, visitors. And it stores that query information in all those AST and nodes. And then it just generates the SQL. So it is used by ActiveRecord under the hood for generating those chain ware clauses. It is used from Rails 3 onwards. But it has also lots of other features which can help in use cases like yours. And those are not exposed through ActiveRecord API. And also it is largely undocumented. So I know that you might not be knowing about it fully. I don't know what Errol is. Yeah, but I will help you. I will help you how to change the... We already have ActiveRecord. So why is there Errol or why do I need to care about Errol? Well, ActiveRecord is full-blown ORM. It does a lot of things. It runs your queries. It converts your SQL result into Ruby objects. It converts them back into SQL. But Job of Errol is just to generate SQL. Nothing else. So... True. Yeah, it doesn't even know about your database. It doesn't even know about what your table structure is, what your columns are. It doesn't know anything. Just generate the SQL. So ActiveRecord will actually connect to your database. It will get the columns information. It will get the table information. Give it to Errol. Errol will generate the query. And then it will give it back to ActiveRecord. ActiveRecord will execute it. And you're done. So that's how the flow is. And how exactly does this Errol work? And how do I use it? You don't have to do anything for using Errol because once you include Rails, which includes ActiveRecord, Errol is just a runtime dependency. So once you have the Rails app running, you already have Errol. You just don't know about it. In production? Yeah, in production. So for getting started with using Errol, we just have to grab the underlying Errol table object. This is the starting point. And once we grab that, then we can build our queries based on whatever attributes that are present in that table. So we can write this convenient method. So ActiveRecord core class has this Errol table method using which we can access the underlying Errol table of that model. And we can write convenient method like this. So location.table will give us the Errol table. So let's see how we can refactor your query using Errol. So here what we are doing is we are just matching our QZen attribute. We are checking whether it is matching with India or whatever your search terms are. And it will generate the same query. Nice. And it looks cleaner. Yes. So table of QZen will give you the attribute, Errol attribute. This is the basic structure for representing your column. So once you have the table, you also have attributes. And similar to QZen, you can get other attributes, like pricing, location, ID, whatever. All of your attributes are possible. And then you build a predicate on that attribute. So matches in. That attribute should match to your search terms. Got it. And this predicate will ultimately generate an abstract syntax tree. It will store all the query information for this particular clause into that AST. And then we can feed it to your bare block. So just like we can pass hash arguments, string to where, we can also pass AST. And ActiveRecord will internally handle how to pass that AST and how to generate query out of that. But yeah, you can basically pass any AST to the bare clause and it will generate the same query. This looks interesting. Yes. And once you run this query with MySQL, it will give you the light query. So you don't have to write database-specific things. Also, you can extract this predicate into a separate class method and then you can reuse it. So almost all of your problems are solved. And it becomes database agnostic. So once you write a query for one specific use case, it will run for almost all databases. So Errol supports MySQL, Postgres. It also supports Oracle, MSSQL. This looks interesting. Yes. So tell me, how you will handle this use case? Let's say the user doesn't want non-vegetarian food. Dude, whatever. I mean, this is pretty simple. I don't know why you're asking me. I just use this query called as I use not I like. I just have a lot of people who are like you, who are vegetarian. So I have this handy query which returns me all the queasy because we do not have non-vegetarian food. Yeah, but we can use Errol in this case also. So it's not just limited to generating positive predicate. For every positive predicate, it has a related negative predicate also. So just like match, you can use does not match and all kinds of things. So these are all the list of predicates that Errol supports. And one of the interesting feature of these predicates is there are star any and star all. So you can pass array arguments to these predicates and it will generate proper and or or SQL for you. So generally we have to kind of juggle around with the strings to possibly like properly handle that. But these methods are very convenient. I mean, okay. But you know, this is not, this is very, I mean, you're speaking very simple things. These are very like, you know, small things, which are easily done by, you know, a raw SQL or active record. Does Errol allow me to do hardcore things like, you know, I have this query over here, which is what is trying to do is find locations which have bookings. And it is trying to find locations which have ratings greater than three. So I'm using, you know, pretty hardcore stuff over here, which is joins. Okay. So I'm trying to find, do an inner join because active record doesn't allow, I don't know why to do an inner join on the same table. And then I'm trying to find all locations like. Yeah. You have a extra end condition. Yeah. And then I'm fine at doing a join on another table. And then I'm also doing an intersect to find all the places which match for this particular query. And this is very hardcore. I don't know. I mean, I don't think Errol will be. Well, Errol really really shines with the joints actually. So Errol will can generate any kind of possible complex joint very easily. Okay. So I will show you how to do that. So the key to generate queries using Errol is to break down those into smaller parts and then reuse it at the end, like break and then compose at the end to build the final query. So let's solve the first problem. You want all reviews having rating greater than three. So we can use the GT predicate. We are done. Okay. True. Now let's do the joint. The problem with active record join and why you have to go for SQL was that you have to specify the extra end condition, which was not possible. Right. And so active record join method is a bit of magical. Like you just specify the association name. It will figure out which foreign key to match. It will generate the inner joint properly. But in Errol you have to specify each thing as you go. So first you specify on which table you want to join. Which is the review. Which is the review. Okay. And then you specify on which condition, like what is your joint condition. So if you go to next, then you can specify on which condition you want to join. So this is just a simple predicate that we already saw. Right. And in active record you are limited to equality predicate for joint condition. But here you can just replace that predicate with any other predicate also. True. Okay. Which might not be a common use case, but you can do it. It's possible. And then you can specify the extra and conditions. So you can specify the extra and condition for your joint. And that can be on the review table. It can also be on the location table. It's like you can specify any of the predicate that you want. Okay. And at the end we just want to select something from it. So as Errol is based on relational algebra, it has its roots, like it has concepts like projections, which are similar to selections in SQL. So we'll project everything. We want to project star. So we do Errol.star. Errol.star is just a convenient method for accessing star. And we are not just limited to inner joint. So generally if you want to do any kind of left join or full join or outer join, then we have to write raw SQL. But Errol supports other joints also. So the second argument to join function is full outer join or outer join. Any other? I don't know where you're going, but try to focus. I don't know if everyone else is following, but I'm following over here. So please focus what we were trying to solve. We are not trying to solve the, I don't know what you're saying, outer joins and stuff like that. Try to focus on the query. Yes. Focus. Yes. I'm focusing. So we want to get all the locations with bookings and all the locations with rating greater than three. Actually, you know what? We already solved the problem. So the first query is we get all the locations with reviews and whatever your condition. And second is we just want location with bookings. We are not doing anything magical here. So we can use the active record way of handling the inner join. True. And then combine them using intersect. You're done. Again, intersect is what? Intersect will just intersect the two subqueries. It will get one AST from here. Something from here? Yeah. Obviously, it's from there. That's what I'm saying. So it gets one AST from the left side, one AST from the right side, and it will intersect that query properly. And you're not just limited to intersect. You can do unions. You can do except clauses. So not just, and you're also not just limited to two subqueries. You can run this on any number of subqueries. It's possible. So all kinds of combinations are for that, but okay. Fine. Well, you're not still convinced with error. I mean, fine. This is like, I don't know, very simple stuff. Okay. Joins is okay. Joins is also simple. Let me give you a harder one. So I have this, you know, hardcore thing, which is called as self-joins. I'm going over here and I'm trying to find all hotels within five kilometers. Like people try to, when they try to search, like my friends try to search for hotels. They usually look places where they have malls or they have places to visit. So I have also provided this functionality. You know, I have nearby locations, which is a self-joining model. And then I do something like this. I don't know what it is. It's like inner joint on the same table. Try to find locations and location is less than, the distance between the two locations are less than, you know, five kilometers or whatever. And it's like, this is something that only my app provides in the world right now. So it's very hard. I don't know. You will be able to do something about it. Well, Erl can help you in this also. So Erl has a concept of aliasing. You can create alias for different tables using the alias method. And then you can generate the same query back. So in your case, you want nearby locations, right? True. So we just generate the alias for nearby locations. And then the joint sources method is... The alias is referring to the same table? Yes, yes. As it is using the table method, it is referring to location. Location. Okay, got it. Then we just, as usual, we specify our joint condition. And the key here is the joint sources method. So what that does is it gives you the underlying Erl's joint representation of that node. Okay. It's a bit hard to understand, but just consider that you get Erl's representation of joint. That's it. No, I don't care about that. Yeah. We don't have to care about it. We can specify the extra end condition just like we had earlier nearby locations. Yeah. The same one. The same one. And once we get that joint sources, we get an array of Erl node joints. We can feed it to active record joints method. Cool. And... So just like we can feed abstract syntax trees to ware clauses, we can also feed Erl joint nodes to active record joints method. Is this something which active record uses? Internally, it does. So as it supports string joints, as it supports association joints, it also supports handling of Erl nodes. Okay. It's nice. So it will again generate you the same query back what you had in raw SQL. Fine. I don't know. But these are all... Okay, this is also a good feature, but I don't know. These are all simple things and I also have these very complicated things called as, you know... Yeah. Making my app secure because 31 users are sharing their information with me, so I want to make it secure. So I use very complicated things you can see over here, which is... I like to use direct... Direct... Yeah. Encryption functions directly from database because Ruby is slow. I don't know why. So I use these things called as pgcm encrypt. I'm using Postgres, so I'm using those kind of things. Yeah. I'm using the setsecret, which is fetching... encrypting it from database and then setting the value for the particular field or I'm using getsecret, which is executing again and trying to fetch this by decrypting that particular value. And this is very hardcore. Don't fit Arial into this. I don't know why you would like to do that. Yes. And any library can't do it, like Arial also doesn't do it, ActiveRecord also doesn't do it. See, I told you. But wait, Arial supports this with name functions. So Arial has this concept of functions, name functions, which can be used as wrappers for our SQL functions. So we can wrap the SQL function in a Ruby object and then we can reuse it for our needs. So your pgpcm encrypt can be wrapped into this name function very easily. You just pass the name of the function, its arguments. Optionally, you can pass the alias. So once you pass the alias, it will generate the proper ads clause. This looks like... I don't know. My one was simpler than this. I don't know. This is very big. Well, in this case, it can be big, but let's say you want to order your criteria. Using some ordering criteria, like police, then you can generate a function for it, wrap it in name function, and then pass it around for your order clauses. So, I mean, you can reuse it as per your needs. It's not just limited to selections. You can pass it for other active record methods, like order also. Because... I don't know. ...Indian, it just generates the AST. So whatever accepts the AST, you can pass it. Fine. No dials. I don't know. You're still not convinced? Fine. Okay. So, in the end, we covered a lot of things today. We covered kind of complex predicates that are possible through ARL. We covered combinations using intersections. We covered unions, except... Then we covered joints. We did all kinds of possible joints that are possible. And we saw, like, how to use ARL with an active record, to enhance it, or to use it in our... To use it to build our queries in a more object-oriented way. Yeah, if you put it that way, it was... I don't know if for them, but for me it was quite useful. I'll try to... Since my app is very... Can you take me as a co-founder? No. Okay. I need to scale. I don't know. With your expertise, I'll be able to scale or not. But anyway, this was pretty interesting for me. I don't know. I'll try to take a look and maybe refactor my app. It was pretty helpful. I don't know. And also, thanks again. And this is not... Again, this is not Majin Teh either. Thank you. Our code is at GitHub. And you can sign up for Vipul services. Yeah, so... sole proprietor of Backpackers. You should check out my app and register and help us get the user account greater than 31. You can register only if you're okay.