CAMERON DUTRO: OK. Sweet. Welcome, everybody. Thank you all for coming to this talk. This is the, the second talk that I've given at a RailsConf, and first one that I gave was on internationalization, and there were like, maybe, fifteen people in the audience. And now there are a lot more. I guess this is a more popular topic, I don't know. So, cool. So, again, thanks all for coming. I am Cameron Dutro. I work for the U.S. Government. No, no, not really. I work for Twitter. I'm on International Engineering, which explains the topic of my previous talk, two years ago. You can follow me at camertron on Twitter. Look at me on GitHub as well. A little bit of personal background, this is my cat. He's pretty cute. His name is Chester. You can follow him at @catwithtail. All right, so, we're gonna do a little bit of an interactive exercise before I dive into the deep technical portions of this. How many, raise your hand if you've ever written Rails 2 code? You guys, wow, look at that. OK. Nice. How many of you, in your Rails 2 code, saw maybe a query that looked like this? Yeah. So, a ton of strings in here. There's not really an object-oriented way to construct queries in Rails 2. So, thank heavens for Rails 3 and 4, because they introduced a much more chainable object-oriented pattern for doing query construction. So, how many of you wrote Rails 3 or 4 code that looks like this? That's pretty much the same thing as before. And, I don't want to say, you know, you're doing it wrong, but, you're doing it wrong. So, let's go into this code and maybe clean it up a little bit, right. So I just moved one of those strings that defined a join association, or a join, on, on this table, and I moved it up into a symbol, so I'm joining the comments table on Post. So this is a lot cleaner looking, and it's allowing ActiveRecord to introspect the associations between these two models, derive the columns to use, and then automatically construct that query for you to do the right thing. So that's awesome. But, we still have a couple other problems with this code, right. We still have these question marks in the where clause, and we still have that second join. And the reason that join is not yet a symbol is because it's an association that doesn't pertain immediately to Post, right. It's joining authors in relation to comments. And, by the way, in this system, authors are a part of comments, not part of posts. I know it's a little confusing. So, you could simplify this even further and you could use this cool hash rocket syntax that says, even though comments and author are not, well comments is, but author is not directly related to posts, I want to get there through comments. And so I can add this hash rocket style syntax also. But we still have these question marks. So, fortunately ActiveRecord has given us a lot over the past couple of years that have passed between 2 and 3 and 4. But this is still a little bit, you know, a little bit dirty, and, especially if you wanted to do something like an outer join in this join clause, or if you wanted to do something a little bit more than say I want to say something is equal to something else, right. A column is equal to a value. So I kind of look at this style as better but, you know, you're still handed a great weapon and not quite using it appropriately, right. So let's look at parts of this query. I'm gonna reuse the previous string example to identify what's wrong with these queries, but let's look at the previous queries. And we'll extract little bits of this and talk about why they're less than ideal. So you've got this join, and the first thing that's wrong with this is you have to write the words join and the words on. And that's not difficult for somebody to do. It's not difficult to type J-O-I-N on your keyboard. But it does mean that you have to know MySQL or PostGreSQL or SQLite syntax to get this done. You also have, essentially, no syntax checking. So, you don't have any idea, having written this string, you don't have any idea that authors is an actual table, or that authors dot id is a column. All you know is that you have this string and that's getting handed to the, to ActiveRecord, and then it's executing it for you. Let's look at the where clause here as well, and I kind of alluded to these problems before, but we'll go over these in detail. So, again, you have to know MySQL syntax, you have to know that there's an and between these two things. And, which of course, and all these syntactical issues, you know, these may not persist across different flavors of databases, so maybe MySQL has this particular construction for and, but it could be that PostGres has a different one. And it probably doesn't, but you know, you don't know that. It's confusing also to match arguments with question marks. I mean, how many of you have looked at a piece of code and thought to yourself, man, there are so many join conditions, or so many where conditions here? And I don't know which argument matches up to which question mark. Yeah, I mean I've done that a bunch of times. It's also not very object-oriented, right. You have this where string and then comma, comma, comma. It's almost functional in its style. And, again, we have that no syntax-checking problem. So, you might think to yourself, boy, I wish there was a better way to do this, and maybe I'll do a search, and you might come across a StackOverflow article that tells you to use strings in your queries. And you might come across RailsCast 202, which tells you to use strings in your queries. You might find blogs. You might ask coworkers. You might ask friends, family members, or even your cat, and they'll all tell you that you should use strings in your queries. But I'm here to tell you that no, you can avoid doing that. You can keep calm, you can avoid literal strings in your queries. Let's look at the previous query, but completely, I like to use the word, arelized. This is the query arelized. So, notice, there's a bunch of cool things going on here. First, you don't have to know SQL syntax. All you have to know is Ruby syntax. And, you know, of course, this is a little bit of a DSL, so you might have to go about learning Arel as well, learning what it can do. But that's what this talk is for. That's why you're here. Ruby syntax-checking, you get that for free. So if you have an unbalanced parenthesis or you're referencing a model that doesn't exist, it will throw an exception and you can go and it'll fail fast, in other words, you can go fix that bug before you deploy to production. So, because ActiveRecord 3 and 4 are chainable, it means Arel is chainable as well. It allows you to compose queries in kind of step-by-step pattern, a step-by-step fashion. So it's also chainable. And you have none of those dang question marks. It's also a lot easier to read, at least in my opinion, because what I'm reading is Ruby, not SQL embedded in Ruby, which to me is weird. So, here's what we're gonna cover today. I've kind of already gone over the, the initial, you know, diagram of, of how, kind of the state of the world now, and so when I cover what ActiveRecord is, what Arel is, there's kind of a, an indistinct line separating those two, and I'll try to separate that. Talk about how to reference tables and columns. What terminal methods are and why they're confusing and when they happen. We'll talk about, of course, the meat of SQL. So select, where, join, and this special thing called join association, and order. Group is, is way easy so I'm not gonna cover that. And, or, greater than, less than - all these great things you can do with Arel that you can't necessarily find online. And then we'll also talk about the match, which is executed SQL-like, and in, which allows for sub-queries. OK. So what is ActiveRecord exactly? Well, you all know this, but it bears repeating here. ActiveRecord is a database abstraction. So there's no need to speak a dialect of SQL. It connects to queries, it returns data back to you. And it also functions as a persistence layer, you know. Database rows are Ruby objects. You can pull them out, you can put them back in. And they also encapsulate, in many cases, your domain logic. And I know people say no fat models, no fat controllers, but the truth is that a lot of domain logic gets shoved into those models. They, they contain validations, they contain all that good stuff. And they also define associations between your models. So, Arel is distinct in that all that it's really used for is building queries. So it's described, on its GitHub page, as a relational algebra for Ruby. And that didn't make any sense to me because, it didn't until I started reading more, because relational algebra, those two words really don't, I don't know how those work put together, but that's how they describe it. Essentially what that means is that it just builds SQL queries in an object-oriented way. It generates abstract syntax trees, or ASTs for your queries, and then uses the visitor pattern to put those out to SQL strings and then hands those off to ActiveRecord to execute. It also enables chaining, as we talked about, because ASTs are kind of nicely composable, because all you have to do is reference a subtree in order to pull in, you know, some other branch of query logic. So, one blogger also described Arel as vexingly undocumented. So you can look at find pieces of Arel all over the place, but joining that all together is a, something that took me a long time. In fact, this talk came out of a project that I was working on at Twitter. I work on the Twitter translation center. We needed to implement an access control system. And to do that, we had permissions and we had groups and all that kind of thing, and in order to join all that logic together and decide whether a user was able to see a particular phrase, it turned out that that was a difficult thing to do. We would have had to do a lot of, a lot of, you know, strings in our SQL queries. And we wanted to avoid that. We wanted to be able to be composable and use scoping and all that great stuff. So, having done that project, I'm trying to, and I'm taking that knowledge and kind of imparting it to you. So that project was a, a direct reason why this talk was created. I find it very difficult to find the information I was looking for. So, as a, as a recap here, you know, Arel knows nothing about your models. It knows very little about your database. All that it knows is that you have these queries you're trying to construct. It doesn't care if those tables exist. It doesn't care if those columns exist. ActiveRecord cares. But Arel really doesn't care. And it does not store or retrieve data. All of these are ActiveRecord's responsibilities. So, in summary here, Arel constructs queries and ActiveRecord does everything else. If you were to look at this as a hierarchy, and kind of a diagram I guess, you've got ActiveRecord, which you would interact with as a developer. ActiveRecord calls out to Arel to, to process a query, form a query, and then sends that query to the database, and the data comes back and ActiveRecord will package that into nice Ruby objects for you. So, something also that bears some introduction here is what an AST is, because as DHH so eloquently put it in his keynote this morning, not everybody comes from a computer science background. And ASTs are very computer science-y. So, an AST essentially is, or really what a tree, in this case, this is a basic tree, this is actually a binary tree, and what this means is that, so, it's made up of nodes. Each node has left and right children, unless it's the bottom. It's called a leaf, or it's called a, yeah, a leaf node. So we have left and right children. In this case, it's a binary tree because you have two children. You could have an n tree which could have multiple children. So, why are trees important? Why are they cool? Well, a tree is cool because you can represent things like equations with them or, or expressions. So five times parenthesis six plus three, you can represent this in a tree by defining a node at the root here, which is multiplication. That's an operator. And then each left and right child represents the two different operands of that expressions, of that, of that operator. So five times six plus three. So the six plus three would be evaluated first, because it's in parenthesis. So, notice also the parenthesis are grouping this expression together. And then five times that. You can kind of expand this concept into how this would look for a SQL expression, by looking at this example. So, you might have a query root and then a select from, and select has two columns, id and text, and from has just one child post, posts. And this would be an entry. Now, I don't actually know if this is how Arel does this in the background. This is a representation that you could imagine is going on behind the scenes. OK. So let's get to some code. And, and the first thing I want to talk about is a little gem that I put together to kind of help with this talk and do some Arel things that just take kind of some of the verbosity out of Arel. So it's called arel-helpers. Go ahead and install this thing. It's got three kind of helpers. Very small. And let's also, so, let's jump on from there and we'll talk about how to reference tables and columns in Arel. So, let's pretend you have this table. It's called post. It has many comments. And let's say you wanted to reference the id in this table. Well, post dot arel_table(:id). So any class that inherits from ActiveRecord::Base has this method called Arel table. And it returns to you an instance of Arel colon colon table that was a, is a way of referencing your table in code. Now brackets :id of course gives me what's called an Arel attribute, and I can look at, I can do the same thing for text, and I can even do this for columns that don't exist. So if I wanted to derive a column, I could also use this to reference that column. Now, that's not always true. But that's almost always true. So this returns an Arel::Attributes::Attribute. Now you don't really care what it returns. You just want, you care that you can use it in your code. So, the arel-helpers gem kind of makes this a little easier. If you include ArelHelpers::ArelTable in your model, you can now reference columns off your tables with just square brackets and eliminate arel_table, it just delegates down to arel_table. And it still returns an attribute to you. All right. So kind of going off of that, what does this statement return? It's a little pop quiz. Does it return "Rails is Cool." Just an array of strings. Does it return an array of instantiated objects that are records, ActiveRecords? Or does it return an ActiveRecord::Relation? You guys might know the answer to this. Who thinks it's A? Who thinks it's B? OK. And who thinks it's C? Nice. OK. It is, in fact, C. One thing that's very cool about Rails 3 and Rails 4, ActiveRecord 3 and ActiveRecord 4, is that any of these methods, these database methods, like select, join, where, they actually return an ActiveRecord::Relation. They don't actually execute right away. So, that means that they are also chainable. So if I said post dot select title, and I assigned that the query, that's not actually executing anything. That's storing this intermediate AST in the variable called query. Which means that I can also say query equals query dot select another column name, and then if I were to say query dot to_sql, I would get out the string the ActiveRecord will send to the database to grab data from. So, it's also important to notice here, I've kind of introduced this to_sql method. Any ActiveRecord relation that you have, you can call to_sql on it and it will give you the string that it would execute against the database. So that's very handy. And I'll use that throughout the whole presentation. Yeah. So relationships can be changed. All right. So let's get into some of the specifics here. So let's talk about select. We talked, we looked at select a little bit. You can actually select multiple columns using array syntax, so this is actually just ActiveRecord at this point, we're not even diving into Arel right now. And you might think, OK, so we're using this to_sql again. We get :id, :text from Post. Everything's great. Let's say that we wanted to get the SQL, though, for count. So we just say give me all of the Posts, the counts here, and you, you probably wouldn't say select(:id) and then count, but, you know, it's an example, so. And instead of getting back a SQL string, you instead get back a NoMethodError. So why did that happen? Well, it turns out that count is a terminal method. So, let's dive into why that happened. So. Being a terminal method, it would, it's executing immediately and returning a result to you instead of giving you an ActiveRecord relation, so that's why, it's good to distinguish between, and know which ones are going to do this to you, right. The way to actually get a count, and get a SQL string back out for it, would be to kind of dive into Arel here. Use your Arel table knowledge from before and call count on an Arel attribute. And then, of course, you can pass that to select as an array. Pass text as a symbol. And out comes this count, like you're expecting. So let's look at what terminal methods are. So, terminal methods execute the query immediately. They do not return a relation. They're mostly count, first, and last, so first and last are kind of like array accessors, right. To_a, pluck, and any of the enumerable methods will all execute your queries immediately. So, for example, this, each over these objects, and you would expect this to happen, because this is not a lazy enumerator. This is a regular enumerator and you're iterating over all of these things and you're putting out the text. And that's obviously going to execute right away. But, more surprising is this one. So, if I say each slice here and it's a lazy enumerator, in other words, I don't pass a block to it, this will also execute immediately. So just beware of that. All right. So let's go actually for real into select here now. So we've already seen how we can say dot count on an attribute, on an Arel attribute. We can also say dot sum, and that will give us this sum method. This is, these are all, by the way, MySQL examples. Notice, also, that Arel and ActiveRecord have nicely added this AS sum_id for you. So, any table, any, any column like this will automatically get a derived column name as well. You can change that column name by adding an as here. So just chain as right on the end of sum. Post visitors sum as visitor_total, and that will give it kind of a custom derived column name for you. You can also say dot maximum and dot minimum, and these will all use kind of the built-in SQL methods that you're expecting. Let's say, though, that you're trying to, you know, run a function that isn't part of Arel's DSL or Arel's knowledge, I guess. It's kind of outside of Arel's abilities. You can actually define a function call that, of any arbitrary function. This covers things like length or, like, any of the other methods that MySQL provides, or PostGres, that may differ from database to database. So, in this case, I am saying a named function dot new, passing in the name of the function and then giving it an array of arguments that function takes. And, then again, I'm also aliasing the result of that as length, in this case. So giving it a derived column name. So, we can shorten this a little bit by, cause it's a little bit verbose, by including Arel::Nodes. And then this just becomes NamedFunction dot new. So that's kind of a little hack you can add to your queries. Something kind of also pretty cool about Arel, this is kind of one of the other little cool select things, you can also say Arel dot star here, if you wanted to select every single record, right, so, or every single column from the record. So select start from post. Arel dot star exists, you can use it in a bunch of different places. OK. So, many people also don't know that select and Arel and ActiveRecord, I should say, have the ability to also just specify a from. So you want to specify a, a sub-query inside a from. This is pretty common. You can do this instead of a join or instead of a, a more complicated query here. So I'm saying select ID, and then I'm passing a sub-query into the from. So one thing that's really important to notice here is you'll notice that the end of the sele, the second select, I say dot AST. So also at any point in your construction, you can call AST on your relation, and it will give you back this massive tree that it's constructed. And in this case, from requires you to pass it an AST otherwise it gets confused. It doesn't know what to do with a relation. It only knows what to do with it if it's an AST or the column or things like that. So. Or a table, in this case. All right. So that pretty much covers select. There's a lot of things you can do with select. I was kind of breezing past a lot of it. If you have any questions, you can always talk to me or, or you know, I guess, go online. But there's not that many resources. Anyway. OK. So, the wonder of where. So, where is also gotten a lot better since Rails 2. I can pass in a title to my where here, where post dot where title is "Arel is Cool," and this will do what you expect. It will say, it'll construct the SQL query, user dot title equals this value. So no question marks here, which is very nice. But what happens if you want to, you know, do something a little more. Oh, here's the, here's the Arel version of this. So, you can do this with pure Arel. It's a little more verbose. You wouldn't really do this. But let's say you wanted to say not equal to or less than, things like that. You can do the same thing. So you can say not equal in this case, and notice that it'll put the band equals in MySQL. You can also say, kind of something cool here, not equal to nil, and it will serialize that as is not nil, which is what your, what you should be expecting. Some of the cool methods. Greater than, GT. Less than. LT. All makes sense. And all of these are just chained right off of this, this, this column attribute here. Here's greater than equal to - GTEQ, and also LTEQ. So, we also, oftentimes, in our queries, need to be able to and and or our way to a kind of a multiple where. We can do the same thing here. So, if you were to say where multiple times just with ActiveRecord, it would automatically give you an and. But if you wanted to specify an or, you would need to do something like this. So we have our title and attribute from post, we're saying equal "Arel is Cool", and the post id is equal to twenty-two or twenty-three. And notice that I have actually injected, so I have put, inside the and, I put another attribute and a dot or, and this will, Arel will just know, it will, the visitor pattern will know how to add the right parenthesis to this query. So, notice the output query has an and and then parenthesis and the or, so that no operator precedence messes us up here. Now, notice that I said equals twenty-two or twenty-three. I could also have put an in here. So in also accepts arguments here that could, essentially an array of values here. So it's the same thing, this will have the same effect. You can also compose your wheres. You can add named functions in here. So you can also do dot count, dot sum, all that stuff works. Those are aggregate functions. Here's length. Oh, I'm aggregating this stuff together and it all, it all just kind of works. It's pretty cool. All right. So let's get to my favorite part of Arel, and that's joins. So ActiveRecord makes joins pretty easy, except for when you're trying to do an outer join, and we'll just, we'll just see how that looks in a second here. So let's pretend we have this data model. It's a basic blog, essentially, so you've got a post. Post has_many comments. And each comment has an author. So let's, let's see what happens when we dive into some joins here. So we say join. This is without any help from Arel. Joins comments, or comment in this case, where id is forty-two. So, very cool. ActiveRecord inspects this model. It inspects the association between author and comment. And automatically generates the right join conditions for you. Pretty cool. You can also specify, as I mentioned, kind of a, a through table in this case. So I want to join comments, and then I also want, you know, using author, I've got comments, and I also want to find the posts of the comment reference or that, that comment is part of. So, this will also do what you expect. It will grab all the correct attributes for the models. Hook them all together. A lot of cool introspection. But, again, what about outer joins? So, you notice back here, it's inter-joined. And there's really no way to specify an outer join without diving in to Arel. At least that I'm aware of. OK. So let's look at this guy again. So let's say I wanted to add an outjoin again. Well, first, it kind of bears mentioning here that you can turn this stuff into, also, pure Arel stuff. So, we have a joins comment. We can also say comment dot joins post, and then call this method called join sources. So, join sources is a way of saying, I have gotten this query from ActiveRecord. And this is, again, it's just using ActiveRecord. And I just want to grab the join sources. I don't care about the select or the where. Just give me the join sources and pass that onto whoever is wrapping. In this case, another query. You can do the same thing with first join. And then you can also specify the on conditions manually here. So, we have a, a join comment Arel table, and we're joining that on :comment_id is equal to author :comment_id, and then saying join sources. And we're doing this for both. Now, this is gonna get pretty verbose, but at this point, we can finally add our outer join. OK. So notice that the attribute, Arel attribute dot join takes a third argument, or a second argument, that is the join type. So you could say inner join here, or outer join. And that results in, essentially, the same query. But this time an outer join. You're thinking, that is a lot of code. Why would I ever type that much code? And you're right. That's way too much code. So, part of the Arel-helpers library is called JoinAssociation. And this will introspect your model, just like ActiveRecord would do. Hand those off to what's called a select manager, and then use the second argument Arel::OuterJoin to give you an outer join, but without all the tedium of specifying all these conditions. So I can slide this in here. JoinAssociation, and I supply the original model and then the association name, and then Arel::OuterJoin. And I can do that for both of these guys. Pretty cool. But wait, there's more. All right. So you've got this guy, and you're like, you know, I really want to supply some extra custom on conditions. Fortunately, join dependency or join association, rather, will yield a block to you that has both the association name, which is a string or symbol, and also the, a join condition's intermediate query object. So you can say join conditions and, and then pass in anything else you would like to construct. In this case, we're making sure that the created_at date is less than or equal to yesterday. So this will, again, in the same query, but this time. And it would have been an outer join except that I, I actually took out, for space reason, I took out the extra OuterJoin call there. So this, this would return inner join and then an outer join. All right, so let's talk about join tables. As for, for, so far we've been talking about tables that have defined associations between them, and those associations are not has_and_belongs_to_many. So I'm trying to kind of freshen us a little bit and say, let's talk a table that, or two tables that are associated but via a join table. So, you might have a series of courses that are taught by a series of teachers. Multiple teachers can teach one course, and courses can have multiple teachers. So, in this case, you've got three tables. Courses, teachers, and courses underscore teachers. This is pretty common in Rails, I think. So, it means you have, again, the course table. You can refer to that with course dot arel_table like we saw before. Teacher table, again, reference that with Teacher dot arel_table. But then we've got some magic in here for courses_teachers, because there, there really is no model for this. There's no constant we can use to reference this table. So instead, we need to create an object for it. So we'll create an Arel::Table. This is doing this manually. We're giving it the name courses_teachers, and now we can use this variable ct anywhere we cant to refer to this table. So, for example, this query. I want to say joins, and we have, you know, courses join teachers, and then just for the purposes of illustration here I've shown, you know, what you would do in Arel to construct the join conditions here, so. You know, on this on this and then dot join sources. And I didn't show the SQL output of this but I think it's pretty, pretty clear from the previous examples. OK. So that wraps up join. So let's talk about order. Order is actually the simplest of the kind of SQL constructs I'll talk about today. You all know that you can say post dot order visitors and visitors in an integer in this case, then dot to_sql. And this will give you a, a return value of, you know, all of your, all of your posts ordered by the number of visitors. You can also, and some people don't know this, but this also just an ActiveRecord construction here. Post.order, that should be (:visitors), reverse order. This will add a descending clause or a descending keyword to the end of your query. Now, you can also do this in Arel. You can say post(:views) descending to SQL here, and this is essentially the same thing. All right. So let's talk about sub-queries with in. So we talked about sub-queries with from. You can also do sub-queries with in, and you can use that AST that we talked about before. SO here's an example of that. This is a very trivial example, but we say post dot where and then we say Post.arel_table in and we supply it another query. So we're adding a sub-query into this in call. So that's pretty cool. And it does the right thing. It adds the correct parenthesis and everything's great. All right. And then like queries with matches, you can also do this with Arel. So most of the time you'd probably have to say, well, I'd like to, if I wanted to do a like, in other words match, kind of fuzzy match against a column in your database, you would need to add a like or, I guess, what is it in PostGres? Like. What is it, double ilike or something. Anyway. So it's one of those things. So and then usually you have to add wild card characters to this, and this would normally be a big string in your query. But, you can fix that with Arel. So, Post dot where, and we have a matches Arel and then I just put my - so you do have to add these. Unfortunately you have to add these, these wild card characters, but it's just dot matches. Super easy. And that gets serialized out into a like. And, interestingly enough, I didn't know this when I started out, but it looks like it's actually taking that string and encoding it into Base, Base16. Hexadecimal. All right. So we've learned all this awesome stuff about Arel. We know that it can do joins and wheres and likes and ins and all kinds of awesome stuff, all without the use of, well, mostly without the use of strings. We also learned that it's pretty chainable. So, when you think about something that's chainable, the thing that comes to mind, for me at least, almost immediately, is the builder pattern. And I think, well, why would I want to construct a huge query inside my model or controller? I'd much rather have an object do this for me. Now, is that always the right choice? No, of course not. But it's, it can be nice to encapsulate logic, like building a huge query. So that's why the next step, or the next point I'm gonna bring up, is how to construct a query builder. And this is also part of the ActiveRecord, or the arel-helpers gem that I mentioned before. So this is pretty much the entire implementation of query builder. You have something that can forward some methods for you. It'll query, it'll forward to_a, to_sql, and to_each, this variable called query inside your object. It accepts a query and sets that as an instance variable. Then it also has this kind of funky method name reflect, and reflect just instantiates a new instance of the class and adds the query. So it's essentially implementing that chaining idea. All right, so here's a lot of code, and we're gonna go over each of these pieces individually, but this is a, a post query builder. So I was trying to query some posts. Let's look at this first method. So, notice this first inherited from QueryBuilder. So let's say we have a method called with_title_matching and it accepts a title. Calls reflect and says dot where title matches and it adds, you know, you can see it adds the percent signs for the wild card characters. This is all encapsulated inside this guy. So you all you have to do is just call with_title_matching on your QueryBuilder. Let's go down to with_comments_by. With_comments_by(username), so we join comments and authors, and then where(Author[:username].in(usernames)). So we are expecting an array of usernames and this, again, reflects this query back to a new instance, returns a new instance to QueryBuilder, too, this time with. Cause, again, we're chaining all these things, right, so now we have both. If we call both these methods in a row, we would have find_by_username and with_title_matching, both together. So the last method I have here is since_yesterday. The post[:create_at] greater than equal to Date dot yesterday. Which means that now I can do something like this. There's not a lot of ugly syntax in here. It's all pretty clean. PostQueryBuilder.new with_comments_by pass in an array of usernames, dot to_sql. And notice it's doing all of the joining for me, like you'd expect. I can then just say dot with_title_matching("arel"), it adds that like, and then I can say since_yesterday and it will add that third condition. Pretty cool. Thanks. Cool. Appreciate that. But you might be sitting here thinking, god, this is super complicated. There's got to be something out there to help me write these queries better, because, you know, it's a lot of verbosity, it's a lot of code, it's a lot of extra stuff to remember. Well, fortunately, there is. So, I worked for a little while on a project called Scuttle. It was gonna be called Ariel, like Little Mermaid, Ariel, you know, but it was, unfortunately, too linguistically similar to Arel and so I thought I would have trouble saying it on stage. So I named it after Ariel in The Little Mermaid's best friend Scuttle. So if you go to scuttle dot io - thanks - if you go to scuttle dot io, you'll see a screen like this. It's an editor. You can enter in a SQL query, and at the bottom it will convert it to Arel for you. [applause] Thanks. Thanks. Again, it's gonna be pretty verbose, but you can tune your queries. There's a lot of other kind of text below explaining what it does. This is kind of a combination of three different projects, and you can check these out on GitHub if you're curious. So, I could not find a SQL parser in Ruby. I tried so hard to find one of these. And there just isn't one. The only, as far as I can tell, the only SQL parsers that I could find were written in Java, and using this thing called Antler, which is a parser generator. So, I finally found an Antler grammar for the Apache incubator tajo project, and grabbed that, generated these appropriate classes, and then wrote a JRuby wrapped around that that allows you to parse, it allows you to parse queries. So it only works currently with select and, I think, insert, because Apache incubator tajo apparently doesn't need to do anything else. So, and I wasn't ready to go write a bunch of other Antler stuff. So, if you guys, if somebody knows Antler around here, please, help me out. We can, we can make it better. And then the third part of this is something called scuttle-server, which is just a Sinatra app on top of JRuby that uses these two components. And, also I forgot to mention there's also this scuttle-Java. But anyway. Anyway, it uses these components to, to drive the website. OK. So that was a whirlwind. I have, like, five minutes left. Thank you guys all for coming and for listening.