 We are going to speak on lots of things on Arial today. It'll be kind of an interview, sorry, it'll be kind of a learning session for me from Prathamesh. And we'll try to explore a lot of lots of things which are not kind of exposed through ActiveRecord. So, I'm Prathamesh, sorry, I'm Gripul. I'm Prathamesh. Yeah. And I'm so sorry. This was not supposed to be the joke. We work together at a consultancy named as Big Binary and it's based out of Miami. And we are from Pune where we work remotely with the consultancy over there. And we got to know each other a lot from Rails while working on Rails code base actually. So, yeah, some of the fault like why I read that was, it has been quite hectic getting over here and I could not get sleep last night. We are currently staying at this hostel named as Plot and yeah, it's been quite hectic. So, what are you working on these days? Okay, this is actually the conversation part, so yeah. Okay, so what are you working on these days? You see, I'm working on a pretty hot startup which is like backpacker's LLC and I've been traveling to a lot of places recently where I've made a lot of friends from hostels and they are backpackers and I wanted to help them out in some way. So, I'm working on this app which does basic functionalities and it helps them out in like maybe task management or finding great locations, finding food centers or finding cheap hostels and hotels. Looks good. Yeah. And you know, it has a very special feature also like it can help you out to find lost bags. So, yeah. And special mention over here, like you should go over to this handle and start following this because it needs more followers. And yeah. And we already have a pretty happy customers right now who use our app and yeah, this is one of the features like which helped them to find the bag. So, just to give brother mission overview like I'm working on pretty hardcore stuff and some of the things that I work on like I have this traveler model over here which is encapsulates data from about the traveler. Something I have tasks which handles information for tasks, locations for finding different information about locations and bookings associated with the customers and things like that. Cool. Looks good. And you know what? It's like, it's a current hotness and I have like 10 active users right now. Wow. And I just launched it last. Yeah, I launched it last week and I actually have 10 active users. So you actually can go backpackers.herokuapp.com and you can sign up yourself. And this is actual live app. And so while I was working like I have 10 actual users. So performance was the most important thing for me. Like, you know, 10 active users you don't get that much these days. So yeah. So that's why like I was very pretty much into hardcore SQL stuff. So I'm like everything that you can't get through ActiveRecord, I would, I try to do it through raw SQL because you know, actual power of database, it's pretty important. So yeah, I used a lot of raw SQL and it was, you know, I'm into, I'm a lot into SQL. So yeah, it's pretty simple for me. Cool. But you know what? Sometimes, I don't know. It's sometimes it's pretty hard for me using it in ActiveRecord. Okay. So yeah. So like this is an example that I was working on which tries to, what it does is it's trying to find out cuisine. So for my friends, it tries to find out food or places where we can find Indian cuisine. And what it tries to do is it's using I like over here because you know, I didn't like the ware closets and things like that. And I wanted to do raw SQL. So yeah. I'm doing pretty hardcore stuff over here. Oh, you have heard of Errol? Yeah, I've heard of it but I didn't use because you know, hardcore raw SQL stuff. Okay. So let me tell you how we can improve this query using Errol. So Errol is actually SQL, a generator for Ruby. It can generate all kinds of complex SQL in an object oriented way. You don't have to write raw SQL for that. And it is used by ActiveRecord internally. But it has all the other features which are not exposed by default by ActiveRecord API. So in your scenario, Errol can help you. So let's start. Yeah. So the first thing that we have to do is we have to first grab the underlying Errol object. Okay. Every ActiveRecord model will have this method ErrolTable and using which we can grab the underlying Errol object. Okay. So once we got that object. Why do you have this table, ErrolTableTable? Because it can give you access to the underlying Errol object and you can call Errol methods on that. Otherwise they're not exposed directly through ActiveRecord API. So once you have got that object, you can call methods like this where you're accessing the QGN attribute and you're calling matchesAny, which is like it is doing the same stuff that you did by writing raw SQL. I like an or. So this is the Errol thing that you're speaking about. Yeah, right. This is the ErrolPredicate method. So you're matching your QGN attribute over your arguments and you can pass any number of arguments. So it has generated the or clause for you because you're doing matchesAny and most probably you're using PostgreSQL. So it has generated the I like also. You don't have to remember that I like. So it is database agnostic. This looks interesting. Yeah. So let me ask you one more question. Oh, don't look at the slide. Sorry. Okay, so let me ask you one more question. Can your app handle this condition where you want Indian food also but you want vegetarian Indian food? Yeah. Whatever, like my app is the current hotness. So I have current like whatever. This is like pretty basic stuff that my app is able to do. So what I'm basically doing is, you know, I use the I like. So I'm using just not I like over here and it's filtering out all the results for non veg food. Okay. Yeah. It's like, you know, whatever. But Errol can help you in this also. So as it has matches, it also has does not match. So it can generate negative predicates also. And using that, you don't have to write that. Not like I like raw SQL. Oh, that's interesting. Yeah. And not just this. It has all these full list of predicates. You can do lots of stuff here. You can compare on arrays. You can do greater than, less than, greater than equal to less than equal to, you can do lots of predicates. Looks interesting. Yeah. And it is very simple. You don't have to do much for this. Wait, but this is like pretty basic stuff. I mean, it's possible to do it in Ruby also like equals two and stuff like that. I mean, it's not, not, not so hard. Can Errol do like kind of hardcore stuff that I'm doing over here because like I'm into raw SQL. So I'm doing an intersect over here. Is it possible to do that? Yes, it is also possible. So I think what we will have to do here is we will have to break this complex query into different parts. And Errol can easily do that. Would you like me to explain what this is doing? Yeah. So it's trying to, what this is doing is it is trying to find all the locations with bookings for a book. I mean, whichever location which has a booking. And then on top of that, it is going to find locations. I mean, it is going to find the result of the ratings which are for locations which are greater than three. Okay. And that's why I'm using intersect over here. Okay. So you want to combine the result of both the subqueries. Exactly. Right. So let's start with your booking query. The rating should be more than three, right? Yeah. We can use our greater than predicate to generate this clause. And let's just keep it as it is. We will use it later. So let's move next. Now we want to join. So here we will not join using active record way. When you are joining using active record, active record will take care of matching the primary keys and foreign keys automatically. But as you are doing Errol way, you have to go in steps. So first you pass the review tables, Errol object, then you specify on which condition you want to join. So you specify that you want to join reviews location ID column. It should be equal to locations primary key. And now you can specify one more extra joint condition. So this is not possible by default in active record. You have to write SQL for that. But here you can specify the extra joint condition where we are using that our existing clause. And now once we have generated this joint condition, now we want to select something from it. So as Errol is based on relational algebra, it has some relational algebra terms. So select in SQL, it's similar to project in Errol. So you are projecting star, Errol dot star, which gets converted into SQL literal. Yeah, I know about the star. It's like select star. Yeah. You know SQL. OK. And the join method is not just inner join. So if you want to do outer joins in active record, it's not possible by default. But here Errol join method can take a second argument. By default, it will be inner join always. But you can pass outer join. You can pass right outer join, full outer join. And it will do those joins also. So joins are very simple using Errol. Wait. We were not discussing on joins. Please focus. OK. Let's come back. Let's come back. Yeah, we were speaking about intersect. Yeah. I don't see that over there. OK. So now we have got our two queries, subqueries. And now we want to just do the final step. And let's do that. It's very simple. So just combine them using intersect. You're done. That looks simple. So here I'm using join, which is from Errol. And then I'm doing joins, which is from active record. Right. And it works because of Errol, actually. So how it works is the intersect method is defined in Errol select manager. So you want the earlier clause, the result. You can chain on that clause. And the intersect method works in this way that you can pass any object to it which responds to AST. And here, location.joins.bookings responds to AST. So Errol will convert this into its AST and will apply the intersect method on both nodes. That looks interesting. Yeah, you get the same query back. Wow, it's much cleaner than what I had written, like the raw SQL stuff. Exactly, exactly. It looks good. Yeah. And it's just not just intersect. You can do unions also. You can do accept clauses also. So you can combine your subquery in all possible ways. I mean, I don't want to use those. But yeah, fine, whatever. OK. Fine. I mean, that's OK. It's just basic intersect that you're using. But you're not convinced yet? I'm using pretty hardcore stuff that I told you. So here's one other thing. I'm using the stable as an adjacency list. What it's trying to do is it is having a, I'm trying to provide a self-join over here. There's a parent location. There may be locations and sublocations. And I'm doing this using hardcore raw SQL stuff, like inner join on the same table using the alias and things like that. And I don't think that, OK, this is possible through error. Well, it is also possible through error. So let's see that. The first thing that we want to do is we want to create alias on our existing table. And it's just a simple method, alias. You call that method and you get alias for your table. So we created the alias for nearby locations. Now let's do the join. So in previous case, we joined on a review table. But here we will join on our own alias. So location will join on nearby locations. And then you can just specify your join condition as we did last time. And again, you can pass the extra end condition. OK. And what is this join sources that I'm seeing over here? Yeah, so if you want to pass this to Active Records Joins method, you want the internal join object. OK. And this join sources, what it does is it gets that internal object. The object is now pluggable. So you can pick it up, plug it into Joins method. You get your Active Record relation object back. OK. Something like this, what you're doing over here? Yeah, something like this. So you combine the result, pass it to Active Record Joins method, and you get the same SQL. But notice the difference. Here you are getting the table name as locations 2, which is generated by Errol. The alias one? Right. So you don't have to make sure that you are using unique names. Errol will do that for you. Nice. Again, I don't have to say anything. I think it's getting quite boring using this slide again again. OK. But anyway. So yeah, this is all fine. This is all raw SQL stuff. But yeah, I'm using pretty secure. I mean, I have 10 users. So I needed to make it super secure. So I'm using like hardcore, again, database stuff. I didn't want to do the Ruby stuff because I believe on database. So I'm using these functions like PgSim Encrypt, which is, I mean, I'm trying to directly encrypt data directly from the database. So yeah, I have these set and get methods which are getting the key and value. They are directly encrypted data from database and they're associating it to that particular record. And you can see over here. You're just running that query. Executing it. And you know, this is pretty hardcore. I don't think Irel will be possible. I mean, I don't see why Irel should come into this picture. OK. So Irel has an answer to this also. It has a concept of name functions. So what name functions are, they will act as a Ruby wrapper for your SQL functions. So you just pass the database function name and its arguments. And then you'll get a name function back, which you can use in your select queries just like you use your normal attributes. So yeah, Irel can help you in this also. And I see one more use case. What are you trying to do here? See, again, hardcore SQL stuff. I'm using coalesce over here. Like, I mean, some of the time my users want to sort tasks based on when they were completed. And sometimes the completed at time that I have over here, it may not be present over here. So instead of that, I'll be sorting on created that's why I'm using coalesce. OK. So you can use name functions here also. Let me show you. OK. So just like previous, we defined a name function for order criteria. And we can pass that to our order clause. And it will generate the same query. So name functions can be used in these scenarios. You just don't have to rely on select. You can use it in multiple cases. Depends on what function you want to use. Very interesting. Yeah. I mean, this has been quite informative. And you know what? I'm going to try and hack into my app and start using this little stuff. And it was pretty nice. OK. So let's just give me a summary for it. Right. So let's just summarize all the things that we discussed. We discussed complex predicates. You can do all kinds of positive stuff, negative stuff. You can generate them. You can chain them. Then combinations. You can generate your subqueries. You can combine them using intersect, union, except all kinds of possible combinations. And joints. Joints. You can do inner joints, self joints. You can do outer joints. OK. All kinds of possible it's possible. And then the last part that what we seen was you can use ARL with Active Record. Right. So you can leverage the existing Active Records features with the power of ARL. Man, this was quite informative. And thanks for all, I mean, giving an idea of whatever. I mean, able to use through ARL. Don't thank me, actually. Thank these people. So Nick Kellen originally wrote ARL. Then Brian and Emilio, they integrated it into Rails in Summer of Code. And Aaron and Ernie for adding new features, maintaining and everything. Thanks. Can we have a clap for them? Yeah. Talk was short because there was tea break, so. Yeah. That's about it. There's one other thing that, yeah, the conversation is over, actually. So there was one other thing that we wanted to include, which was Windows. So if you must have used Windows in SQL, it's possible, again, through ARL, to use in a pretty neat way. So this is how actually ARL works behind the scenes. It creates a select manager. And this is the engine. I mean, every model that you have, it has an engine associated. That is an Active Record-related object. And we can use things like window over here. So it is something that is useful when you want to group together your results. So this is a running PR right now. I mean, Aaron is over here. Maybe he can take a look. So we'll have this functionality, complete functionality of Windows, possible through ARL also. The code that we have in the slides is available on GitHub at this particular URL. And yeah, the app is live, actually, and you can start using it at backpackers.herukyab.com. Maybe you can check it out. Yeah, that's it. I'm sorry. So the question is, will you agree that some of these examples are simpler in SQL than in ARL? If it was simpler, you wouldn't even use Active Record. I mean, in that terms. But in lots of cases, it seems simpler in the beginning, but in the end, it's like you're adding on lots of conditions and it gets quite messy. There are also other things involved that you need to take care of whether the syntax is correct or you have actual coding correct or things like that which are given by free for us from ARL and Active Record. So things like that are pretty useful for us. And again, it helps us out to reuse a lot of things that we are breaking a lot of things into smaller chunks. So imagine a case that you have a big query and you're writing it in one single line. Instead of that, you can actually break it down into a separate class and then you can have a pretty decent test for that particular class. So yeah. And then again, you can combine all these results and use them again in terms of Active Record, just like we did for Joins. So you leverage what Active Record does for you. So you can chain on those in other Active Record ways. So your one class can be ARL and other class can be normal Active Record way. But still you can chain on them. OK. Have you been trying the SQL library? And how do you find it comparable to RL? I think we both have not used SQL. We have not used SQL, but one of the advantage of using ARL is that it's under the Rails umbrella. So you can be somewhat sure that, OK. So you wanted to make inside of Rails, inside of Active Record. So this is because you did it. It's aligned with that, actually. OK. OK, thank you. Thanks, Pradamesh and Vipul. Yeah, thanks.