WEBVTT 00:00:17.190 --> 00:00:20.210 CAMERON DUTRO: OK. Sweet. Welcome, everybody. 00:00:20.210 --> 00:00:22.120 Thank you all for coming to this talk. 00:00:22.120 --> 00:00:25.970 This is the, the second talk that I've given at a RailsConf, 00:00:25.970 --> 00:00:28.140 and first one that I gave was on 00:00:28.140 --> 00:00:31.100 internationalization, and there were like, maybe, 00:00:31.100 --> 00:00:33.330 fifteen people in the audience. 00:00:33.330 --> 00:00:34.790 And now there are a lot more. 00:00:34.790 --> 00:00:36.510 I guess this is a more popular topic, 00:00:36.510 --> 00:00:38.879 I don't know. So, cool. 00:00:38.879 --> 00:00:41.900 So, again, thanks all for coming. I am Cameron 00:00:41.900 --> 00:00:44.980 Dutro. I work for the U.S. Government. No, no, 00:00:44.980 --> 00:00:49.769 not really. I work for Twitter. I'm on International 00:00:49.769 --> 00:00:52.739 Engineering, which explains the topic of my previous talk, 00:00:52.739 --> 00:00:55.659 two years ago. You can follow me at camertron 00:00:55.659 --> 00:00:58.589 on Twitter. Look at me on GitHub as well. 00:00:58.589 --> 00:01:01.230 A little bit of personal background, this is my 00:01:01.230 --> 00:01:04.569 cat. He's pretty cute. His name is Chester. You 00:01:04.569 --> 00:01:08.400 can follow him at @catwithtail. All right, so, we're 00:01:08.400 --> 00:01:11.479 gonna do a little bit of an interactive exercise 00:01:11.479 --> 00:01:14.900 before I dive into the deep technical portions of 00:01:14.900 --> 00:01:17.940 this. How many, raise your hand if you've ever 00:01:17.940 --> 00:01:22.710 written Rails 2 code? You guys, wow, look at 00:01:22.710 --> 00:01:25.180 that. OK. Nice. How many of you, in your 00:01:25.180 --> 00:01:28.040 Rails 2 code, saw maybe a query that looked 00:01:28.040 --> 00:01:29.650 like this? Yeah. 00:01:29.650 --> 00:01:33.370 So, a ton of strings in here. There's not 00:01:33.370 --> 00:01:36.120 really an object-oriented way to construct queries in Rails 00:01:36.120 --> 00:01:38.690 2. So, thank heavens for Rails 3 and 4, 00:01:38.690 --> 00:01:42.820 because they introduced a much more chainable object-oriented pattern 00:01:42.820 --> 00:01:46.220 for doing query construction. So, how many of you 00:01:46.220 --> 00:01:48.430 wrote Rails 3 or 4 code that looks like 00:01:48.430 --> 00:01:53.990 this? 00:01:53.990 --> 00:01:57.390 That's pretty much the same thing as before. And, 00:01:57.390 --> 00:01:59.030 I don't want to say, you know, you're doing 00:01:59.030 --> 00:02:06.030 it wrong, but, you're doing it wrong. So, let's 00:02:06.340 --> 00:02:08.720 go into this code and maybe clean it up 00:02:08.720 --> 00:02:10.869 a little bit, right. 00:02:10.869 --> 00:02:12.810 So I just moved one of those strings that 00:02:12.810 --> 00:02:15.620 defined a join association, or a join, on, on 00:02:15.620 --> 00:02:17.620 this table, and I moved it up into a 00:02:17.620 --> 00:02:20.480 symbol, so I'm joining the comments table on Post. 00:02:20.480 --> 00:02:23.450 So this is a lot cleaner looking, and it's 00:02:23.450 --> 00:02:27.120 allowing ActiveRecord to introspect the associations between these two 00:02:27.120 --> 00:02:30.290 models, derive the columns to use, and then automatically 00:02:30.290 --> 00:02:31.870 construct that query for you to do the right 00:02:31.870 --> 00:02:33.580 thing. 00:02:33.580 --> 00:02:35.870 So that's awesome. But, we still have a couple 00:02:35.870 --> 00:02:37.950 other problems with this code, right. We still have 00:02:37.950 --> 00:02:40.370 these question marks in the where clause, and we 00:02:40.370 --> 00:02:42.000 still have that second join. And the reason that 00:02:42.000 --> 00:02:47.110 join is not yet a symbol is because it's 00:02:47.110 --> 00:02:50.130 an association that doesn't pertain immediately to Post, right. 00:02:50.130 --> 00:02:52.900 It's joining authors in relation to comments. 00:02:52.900 --> 00:02:55.370 And, by the way, in this system, authors are 00:02:55.370 --> 00:02:57.310 a part of comments, not part of posts. I 00:02:57.310 --> 00:02:59.120 know it's a little confusing. So, you could simplify 00:02:59.120 --> 00:03:00.840 this even further and you could use this cool 00:03:00.840 --> 00:03:03.750 hash rocket syntax that says, even though comments and 00:03:03.750 --> 00:03:05.860 author are not, well comments is, but author is 00:03:05.860 --> 00:03:07.450 not directly related to posts, I want to get 00:03:07.450 --> 00:03:09.590 there through comments. And so I can add this 00:03:09.590 --> 00:03:12.069 hash rocket style syntax also. 00:03:12.069 --> 00:03:14.720 But we still have these question marks. So, fortunately 00:03:14.720 --> 00:03:17.050 ActiveRecord has given us a lot over the past 00:03:17.050 --> 00:03:19.240 couple of years that have passed between 2 and 00:03:19.240 --> 00:03:21.170 3 and 4. But this is still a little 00:03:21.170 --> 00:03:23.700 bit, you know, a little bit dirty, and, especially 00:03:23.700 --> 00:03:25.250 if you wanted to do something like an outer 00:03:25.250 --> 00:03:27.150 join in this join clause, or if you wanted 00:03:27.150 --> 00:03:29.780 to do something a little bit more than say 00:03:29.780 --> 00:03:31.160 I want to say something is equal to something 00:03:31.160 --> 00:03:34.110 else, right. A column is equal to a value. 00:03:34.110 --> 00:03:36.510 So I kind of look at this style as 00:03:36.510 --> 00:03:39.090 better but, you know, you're still handed a great 00:03:39.090 --> 00:03:44.450 weapon and not quite using it appropriately, right. So 00:03:44.450 --> 00:03:46.170 let's look at parts of this query. I'm gonna 00:03:46.170 --> 00:03:48.290 reuse the previous string example to identify what's wrong 00:03:48.290 --> 00:03:50.640 with these queries, but let's look at the previous 00:03:50.640 --> 00:03:52.700 queries. And we'll extract little bits of this and 00:03:52.700 --> 00:03:56.870 talk about why they're less than ideal. 00:03:56.870 --> 00:03:59.780 So you've got this join, and the first thing 00:03:59.780 --> 00:04:01.380 that's wrong with this is you have to write 00:04:01.380 --> 00:04:04.900 the words join and the words on. And that's 00:04:04.900 --> 00:04:07.880 not difficult for somebody to do. It's not difficult 00:04:07.880 --> 00:04:10.670 to type J-O-I-N on your keyboard. But it does 00:04:10.670 --> 00:04:13.599 mean that you have to know MySQL or PostGreSQL 00:04:13.599 --> 00:04:18.569 or SQLite syntax to get this done. You also 00:04:18.569 --> 00:04:21.569 have, essentially, no syntax checking. So, you don't have 00:04:21.569 --> 00:04:24.000 any idea, having written this string, you don't have 00:04:24.000 --> 00:04:27.750 any idea that authors is an actual table, or 00:04:27.750 --> 00:04:30.550 that authors dot id is a column. All you 00:04:30.550 --> 00:04:32.000 know is that you have this string and that's 00:04:32.000 --> 00:04:34.000 getting handed to the, to ActiveRecord, and then it's 00:04:34.000 --> 00:04:36.600 executing it for you. 00:04:36.600 --> 00:04:38.430 Let's look at the where clause here as well, 00:04:38.430 --> 00:04:40.430 and I kind of alluded to these problems before, 00:04:40.430 --> 00:04:42.380 but we'll go over these in detail. So, again, 00:04:42.380 --> 00:04:44.410 you have to know MySQL syntax, you have to 00:04:44.410 --> 00:04:47.400 know that there's an and between these two things. 00:04:47.400 --> 00:04:50.020 And, which of course, and all these syntactical issues, 00:04:50.020 --> 00:04:52.210 you know, these may not persist across different flavors 00:04:52.210 --> 00:04:56.180 of databases, so maybe MySQL has this particular construction 00:04:56.180 --> 00:04:57.990 for and, but it could be that PostGres has 00:04:57.990 --> 00:04:59.970 a different one. And it probably doesn't, but you 00:04:59.970 --> 00:05:02.850 know, you don't know that. 00:05:02.850 --> 00:05:05.340 It's confusing also to match arguments with question marks. 00:05:05.340 --> 00:05:06.560 I mean, how many of you have looked at 00:05:06.560 --> 00:05:08.440 a piece of code and thought to yourself, man, 00:05:08.440 --> 00:05:10.480 there are so many join conditions, or so many 00:05:10.480 --> 00:05:13.330 where conditions here? And I don't know which argument 00:05:13.330 --> 00:05:15.650 matches up to which question mark. Yeah, I mean 00:05:15.650 --> 00:05:19.120 I've done that a bunch of times. 00:05:19.120 --> 00:05:20.970 It's also not very object-oriented, right. You have this 00:05:20.970 --> 00:05:23.370 where string and then comma, comma, comma. It's almost 00:05:23.370 --> 00:05:26.380 functional in its style. And, again, we have that 00:05:26.380 --> 00:05:28.280 no syntax-checking problem. 00:05:28.280 --> 00:05:30.410 So, you might think to yourself, boy, I wish 00:05:30.410 --> 00:05:31.880 there was a better way to do this, and 00:05:31.880 --> 00:05:34.000 maybe I'll do a search, and you might come 00:05:34.000 --> 00:05:37.050 across a StackOverflow article that tells you to use 00:05:37.050 --> 00:05:39.880 strings in your queries. And you might come across 00:05:39.880 --> 00:05:43.690 RailsCast 202, which tells you to use strings in 00:05:43.690 --> 00:05:47.199 your queries. You might find blogs. You might ask 00:05:47.199 --> 00:05:50.340 coworkers. You might ask friends, family members, or even 00:05:50.340 --> 00:05:52.440 your cat, and they'll all tell you that you 00:05:52.440 --> 00:05:53.699 should use strings in your queries. 00:05:53.699 --> 00:05:56.030 But I'm here to tell you that no, you 00:05:56.030 --> 00:05:58.500 can avoid doing that. You can keep calm, you 00:05:58.500 --> 00:06:01.840 can avoid literal strings in your queries. Let's look 00:06:01.840 --> 00:06:04.430 at the previous query, but completely, I like to 00:06:04.430 --> 00:06:08.350 use the word, arelized. This is the query arelized. 00:06:08.350 --> 00:06:10.229 So, notice, there's a bunch of cool things going 00:06:10.229 --> 00:06:11.430 on here. 00:06:11.430 --> 00:06:13.710 First, you don't have to know SQL syntax. All 00:06:13.710 --> 00:06:15.690 you have to know is Ruby syntax. And, you 00:06:15.690 --> 00:06:17.199 know, of course, this is a little bit of 00:06:17.199 --> 00:06:18.870 a DSL, so you might have to go about 00:06:18.870 --> 00:06:21.009 learning Arel as well, learning what it can do. 00:06:21.009 --> 00:06:23.979 But that's what this talk is for. That's why 00:06:23.979 --> 00:06:24.639 you're here. 00:06:24.639 --> 00:06:26.620 Ruby syntax-checking, you get that for free. So if 00:06:26.620 --> 00:06:28.600 you have an unbalanced parenthesis or you're referencing a 00:06:28.600 --> 00:06:31.240 model that doesn't exist, it will throw an exception 00:06:31.240 --> 00:06:33.139 and you can go and it'll fail fast, in 00:06:33.139 --> 00:06:34.449 other words, you can go fix that bug before 00:06:34.449 --> 00:06:36.940 you deploy to production. 00:06:36.940 --> 00:06:41.160 So, because ActiveRecord 3 and 4 are chainable, it 00:06:41.160 --> 00:06:42.570 means Arel is chainable as well. It allows you 00:06:42.570 --> 00:06:46.020 to compose queries in kind of step-by-step pattern, a 00:06:46.020 --> 00:06:49.960 step-by-step fashion. So it's also chainable. And you have 00:06:49.960 --> 00:06:52.199 none of those dang question marks. 00:06:52.199 --> 00:06:55.229 It's also a lot easier to read, at least 00:06:55.229 --> 00:06:57.600 in my opinion, because what I'm reading is Ruby, 00:06:57.600 --> 00:06:59.860 not SQL embedded in Ruby, which to me is 00:06:59.860 --> 00:07:03.680 weird. So, here's what we're gonna cover today. I've 00:07:03.680 --> 00:07:05.919 kind of already gone over the, the initial, you 00:07:05.919 --> 00:07:07.690 know, diagram of, of how, kind of the state 00:07:07.690 --> 00:07:09.740 of the world now, and so when I cover 00:07:09.740 --> 00:07:11.680 what ActiveRecord is, what Arel is, there's kind of 00:07:11.680 --> 00:07:14.210 a, an indistinct line separating those two, and I'll 00:07:14.210 --> 00:07:16.120 try to separate that. 00:07:16.120 --> 00:07:19.400 Talk about how to reference tables and columns. What 00:07:19.400 --> 00:07:22.220 terminal methods are and why they're confusing and when 00:07:22.220 --> 00:07:24.889 they happen. We'll talk about, of course, the meat 00:07:24.889 --> 00:07:27.740 of SQL. So select, where, join, and this special 00:07:27.740 --> 00:07:31.400 thing called join association, and order. Group is, is 00:07:31.400 --> 00:07:34.039 way easy so I'm not gonna cover that. And, 00:07:34.039 --> 00:07:35.789 or, greater than, less than - all these great 00:07:35.789 --> 00:07:37.800 things you can do with Arel that you can't 00:07:37.800 --> 00:07:40.330 necessarily find online. And then we'll also talk about 00:07:40.330 --> 00:07:43.310 the match, which is executed SQL-like, and in, which 00:07:43.310 --> 00:07:45.699 allows for sub-queries. 00:07:45.699 --> 00:07:49.410 OK. So what is ActiveRecord exactly? Well, you all 00:07:49.410 --> 00:07:53.479 know this, but it bears repeating here. ActiveRecord is 00:07:53.479 --> 00:07:56.110 a database abstraction. So there's no need to speak 00:07:56.110 --> 00:07:58.330 a dialect of SQL. It connects to queries, it 00:07:58.330 --> 00:08:00.449 returns data back to you. And it also functions 00:08:00.449 --> 00:08:02.710 as a persistence layer, you know. Database rows are 00:08:02.710 --> 00:08:04.800 Ruby objects. You can pull them out, you can 00:08:04.800 --> 00:08:08.199 put them back in. And they also encapsulate, in 00:08:08.199 --> 00:08:09.800 many cases, your domain logic. And I know people 00:08:09.800 --> 00:08:12.669 say no fat models, no fat controllers, but the 00:08:12.669 --> 00:08:15.009 truth is that a lot of domain logic gets 00:08:15.009 --> 00:08:16.300 shoved into those models. 00:08:16.300 --> 00:08:20.530 They, they contain validations, they contain all that good 00:08:20.530 --> 00:08:24.080 stuff. And they also define associations between your models. 00:08:24.080 --> 00:08:26.870 So, Arel is distinct in that all that it's 00:08:26.870 --> 00:08:31.789 really used for is building queries. So it's described, 00:08:31.789 --> 00:08:35.809 on its GitHub page, as a relational algebra for 00:08:35.809 --> 00:08:38.309 Ruby. And that didn't make any sense to me 00:08:38.309 --> 00:08:40.789 because, it didn't until I started reading more, because 00:08:40.789 --> 00:08:43.299 relational algebra, those two words really don't, I don't 00:08:43.299 --> 00:08:45.790 know how those work put together, but that's how 00:08:45.790 --> 00:08:46.990 they describe it. 00:08:46.990 --> 00:08:48.069 Essentially what that means is that it just builds 00:08:48.069 --> 00:08:50.880 SQL queries in an object-oriented way. It generates abstract 00:08:50.880 --> 00:08:54.330 syntax trees, or ASTs for your queries, and then 00:08:54.330 --> 00:08:56.529 uses the visitor pattern to put those out to 00:08:56.529 --> 00:08:58.550 SQL strings and then hands those off to ActiveRecord 00:08:58.550 --> 00:09:01.700 to execute. It also enables chaining, as we talked 00:09:01.700 --> 00:09:04.890 about, because ASTs are kind of nicely composable, because 00:09:04.890 --> 00:09:06.670 all you have to do is reference a subtree 00:09:06.670 --> 00:09:08.760 in order to pull in, you know, some other 00:09:08.760 --> 00:09:10.390 branch of query logic. 00:09:10.390 --> 00:09:15.160 So, one blogger also described Arel as vexingly undocumented. 00:09:15.160 --> 00:09:16.950 So you can look at find pieces of Arel 00:09:16.950 --> 00:09:19.950 all over the place, but joining that all together 00:09:19.950 --> 00:09:22.649 is a, something that took me a long time. 00:09:22.649 --> 00:09:25.740 In fact, this talk came out of a project 00:09:25.740 --> 00:09:27.020 that I was working on at Twitter. I work 00:09:27.020 --> 00:09:29.649 on the Twitter translation center. We needed to implement 00:09:29.649 --> 00:09:32.980 an access control system. And to do that, we 00:09:32.980 --> 00:09:35.050 had permissions and we had groups and all that 00:09:35.050 --> 00:09:37.399 kind of thing, and in order to join all 00:09:37.399 --> 00:09:39.510 that logic together and decide whether a user was 00:09:39.510 --> 00:09:42.000 able to see a particular phrase, it turned out 00:09:42.000 --> 00:09:44.149 that that was a difficult thing to do. We 00:09:44.149 --> 00:09:45.360 would have had to do a lot of, a 00:09:45.360 --> 00:09:48.440 lot of, you know, strings in our SQL queries. 00:09:48.440 --> 00:09:50.020 And we wanted to avoid that. We wanted to 00:09:50.020 --> 00:09:51.760 be able to be composable and use scoping and 00:09:51.760 --> 00:09:53.570 all that great stuff. 00:09:53.570 --> 00:09:55.589 So, having done that project, I'm trying to, and 00:09:55.589 --> 00:09:57.880 I'm taking that knowledge and kind of imparting it 00:09:57.880 --> 00:09:59.890 to you. So that project was a, a direct 00:09:59.890 --> 00:10:02.510 reason why this talk was created. I find it 00:10:02.510 --> 00:10:06.100 very difficult to find the information I was looking 00:10:06.100 --> 00:10:06.740 for. 00:10:06.740 --> 00:10:08.550 So, as a, as a recap here, you know, 00:10:08.550 --> 00:10:11.330 Arel knows nothing about your models. It knows very 00:10:11.330 --> 00:10:14.060 little about your database. All that it knows is 00:10:14.060 --> 00:10:17.820 that you have these queries you're trying to construct. 00:10:17.820 --> 00:10:20.040 It doesn't care if those tables exist. It doesn't 00:10:20.040 --> 00:10:22.730 care if those columns exist. ActiveRecord cares. But Arel 00:10:22.730 --> 00:10:25.500 really doesn't care. And it does not store or 00:10:25.500 --> 00:10:28.589 retrieve data. All of these are ActiveRecord's responsibilities. So, 00:10:28.589 --> 00:10:32.550 in summary here, Arel constructs queries and ActiveRecord does 00:10:32.550 --> 00:10:33.220 everything else. 00:10:33.220 --> 00:10:35.790 If you were to look at this as a 00:10:35.790 --> 00:10:38.880 hierarchy, and kind of a diagram I guess, you've 00:10:38.880 --> 00:10:42.620 got ActiveRecord, which you would interact with as a 00:10:42.620 --> 00:10:45.940 developer. ActiveRecord calls out to Arel to, to process 00:10:45.940 --> 00:10:48.330 a query, form a query, and then sends that 00:10:48.330 --> 00:10:50.370 query to the database, and the data comes back 00:10:50.370 --> 00:10:52.740 and ActiveRecord will package that into nice Ruby objects 00:10:52.740 --> 00:10:53.720 for you. 00:10:53.720 --> 00:10:57.670 So, something also that bears some introduction here is 00:10:57.670 --> 00:11:02.510 what an AST is, because as DHH so eloquently 00:11:02.510 --> 00:11:05.630 put it in his keynote this morning, not everybody 00:11:05.630 --> 00:11:07.839 comes from a computer science background. And ASTs are 00:11:07.839 --> 00:11:10.430 very computer science-y. So, an AST essentially is, or 00:11:10.430 --> 00:11:12.060 really what a tree, in this case, this is 00:11:12.060 --> 00:11:13.930 a basic tree, this is actually a binary tree, 00:11:13.930 --> 00:11:15.850 and what this means is that, so, it's made 00:11:15.850 --> 00:11:18.610 up of nodes. Each node has left and right 00:11:18.610 --> 00:11:20.660 children, unless it's the bottom. It's called a leaf, 00:11:20.660 --> 00:11:22.670 or it's called a, yeah, a leaf node. 00:11:22.670 --> 00:11:24.390 So we have left and right children. In this 00:11:24.390 --> 00:11:26.260 case, it's a binary tree because you have two 00:11:26.260 --> 00:11:27.540 children. You could have an n tree which could 00:11:27.540 --> 00:11:31.120 have multiple children. So, why are trees important? Why 00:11:31.120 --> 00:11:33.019 are they cool? Well, a tree is cool because 00:11:33.019 --> 00:11:36.459 you can represent things like equations with them or, 00:11:36.459 --> 00:11:40.230 or expressions. So five times parenthesis six plus three, 00:11:40.230 --> 00:11:43.640 you can represent this in a tree by defining 00:11:43.640 --> 00:11:45.380 a node at the root here, which is multiplication. 00:11:45.380 --> 00:11:48.450 That's an operator. And then each left and right 00:11:48.450 --> 00:11:52.630 child represents the two different operands of that expressions, 00:11:52.630 --> 00:11:55.019 of that, of that operator. So five times six 00:11:55.019 --> 00:11:55.490 plus three. 00:11:55.490 --> 00:11:58.370 So the six plus three would be evaluated first, 00:11:58.370 --> 00:12:00.279 because it's in parenthesis. So, notice also the parenthesis 00:12:00.279 --> 00:12:03.050 are grouping this expression together. And then five times 00:12:03.050 --> 00:12:06.860 that. You can kind of expand this concept into 00:12:06.860 --> 00:12:09.660 how this would look for a SQL expression, by 00:12:09.660 --> 00:12:11.420 looking at this example. So, you might have a 00:12:11.420 --> 00:12:14.170 query root and then a select from, and select 00:12:14.170 --> 00:12:17.760 has two columns, id and text, and from has 00:12:17.760 --> 00:12:19.529 just one child post, posts. And this would be 00:12:19.529 --> 00:12:20.040 an entry. 00:12:20.040 --> 00:12:21.589 Now, I don't actually know if this is how 00:12:21.589 --> 00:12:23.260 Arel does this in the background. This is a 00:12:23.260 --> 00:12:25.700 representation that you could imagine is going on behind 00:12:25.700 --> 00:12:26.899 the scenes. 00:12:26.899 --> 00:12:29.260 OK. So let's get to some code. And, and 00:12:29.260 --> 00:12:30.589 the first thing I want to talk about is 00:12:30.589 --> 00:12:32.700 a little gem that I put together to kind 00:12:32.700 --> 00:12:34.680 of help with this talk and do some Arel 00:12:34.680 --> 00:12:36.800 things that just take kind of some of the 00:12:36.800 --> 00:12:39.910 verbosity out of Arel. So it's called arel-helpers. Go 00:12:39.910 --> 00:12:42.110 ahead and install this thing. It's got three kind 00:12:42.110 --> 00:12:43.610 of helpers. Very small. 00:12:43.610 --> 00:12:46.720 And let's also, so, let's jump on from there 00:12:46.720 --> 00:12:49.089 and we'll talk about how to reference tables and 00:12:49.089 --> 00:12:52.910 columns in Arel. So, let's pretend you have this 00:12:52.910 --> 00:12:56.850 table. It's called post. It has many comments. And 00:12:56.850 --> 00:12:59.100 let's say you wanted to reference the id in 00:12:59.100 --> 00:13:04.050 this table. Well, post dot arel_table(:id). So any class 00:13:04.050 --> 00:13:06.990 that inherits from ActiveRecord::Base has this method called Arel 00:13:06.990 --> 00:13:09.550 table. And it returns to you an instance of 00:13:09.550 --> 00:13:12.510 Arel colon colon table that was a, is a 00:13:12.510 --> 00:13:15.490 way of referencing your table in code. 00:13:15.490 --> 00:13:17.430 Now brackets :id of course gives me what's called 00:13:17.430 --> 00:13:18.579 an Arel attribute, and I can look at, I 00:13:18.579 --> 00:13:20.760 can do the same thing for text, and I 00:13:20.760 --> 00:13:23.640 can even do this for columns that don't exist. 00:13:23.640 --> 00:13:25.180 So if I wanted to derive a column, I 00:13:25.180 --> 00:13:27.320 could also use this to reference that column. Now, 00:13:27.320 --> 00:13:30.570 that's not always true. But that's almost always true. 00:13:30.570 --> 00:13:31.779 So this returns an Arel::Attributes::Attribute. 00:13:31.779 --> 00:13:34.570 Now you don't really care what it returns. You 00:13:34.570 --> 00:13:35.700 just want, you care that you can use it 00:13:35.700 --> 00:13:39.480 in your code. So, the arel-helpers gem kind of 00:13:39.480 --> 00:13:43.100 makes this a little easier. If you include ArelHelpers::ArelTable 00:13:43.100 --> 00:13:46.630 in your model, you can now reference columns off 00:13:46.630 --> 00:13:49.709 your tables with just square brackets and eliminate arel_table, 00:13:49.709 --> 00:13:52.480 it just delegates down to arel_table. And it still 00:13:52.480 --> 00:13:54.890 returns an attribute to you. 00:13:54.890 --> 00:13:57.300 All right. So kind of going off of that, 00:13:57.300 --> 00:14:00.060 what does this statement return? It's a little pop 00:14:00.060 --> 00:14:04.139 quiz. Does it return "Rails is Cool." Just an 00:14:04.139 --> 00:14:08.589 array of strings. Does it return an array of 00:14:08.589 --> 00:14:13.459 instantiated objects that are records, ActiveRecords? Or does it 00:14:13.459 --> 00:14:16.110 return an ActiveRecord::Relation? You guys might know the answer 00:14:16.110 --> 00:14:19.139 to this. Who thinks it's A? Who thinks it's 00:14:19.139 --> 00:14:22.860 B? OK. And who thinks it's C? Nice. OK. 00:14:22.860 --> 00:14:25.600 It is, in fact, C. 00:14:25.600 --> 00:14:28.860 One thing that's very cool about Rails 3 and 00:14:28.860 --> 00:14:31.820 Rails 4, ActiveRecord 3 and ActiveRecord 4, is that 00:14:31.820 --> 00:14:34.760 any of these methods, these database methods, like select, 00:14:34.760 --> 00:14:37.610 join, where, they actually return an ActiveRecord::Relation. They don't 00:14:37.610 --> 00:14:41.329 actually execute right away. So, that means that they 00:14:41.329 --> 00:14:43.519 are also chainable. So if I said post dot 00:14:43.519 --> 00:14:47.339 select title, and I assigned that the query, that's 00:14:47.339 --> 00:14:51.279 not actually executing anything. That's storing this intermediate AST 00:14:51.279 --> 00:14:53.290 in the variable called query. Which means that I 00:14:53.290 --> 00:14:55.680 can also say query equals query dot select another 00:14:55.680 --> 00:14:57.660 column name, and then if I were to say 00:14:57.660 --> 00:15:00.829 query dot to_sql, I would get out the string 00:15:00.829 --> 00:15:03.050 the ActiveRecord will send to the database to grab 00:15:03.050 --> 00:15:04.079 data from. 00:15:04.079 --> 00:15:07.160 So, it's also important to notice here, I've kind 00:15:07.160 --> 00:15:10.540 of introduced this to_sql method. Any ActiveRecord relation that 00:15:10.540 --> 00:15:12.200 you have, you can call to_sql on it and 00:15:12.200 --> 00:15:13.589 it will give you the string that it would 00:15:13.589 --> 00:15:17.050 execute against the database. So that's very handy. And 00:15:17.050 --> 00:15:18.839 I'll use that throughout the whole presentation. 00:15:18.839 --> 00:15:21.660 Yeah. So relationships can be changed. 00:15:21.660 --> 00:15:23.310 All right. So let's get into some of the 00:15:23.310 --> 00:15:25.010 specifics here. So let's talk about select. We talked, 00:15:25.010 --> 00:15:28.620 we looked at select a little bit. You can 00:15:28.620 --> 00:15:32.130 actually select multiple columns using array syntax, so this 00:15:32.130 --> 00:15:34.040 is actually just ActiveRecord at this point, we're not 00:15:34.040 --> 00:15:36.600 even diving into Arel right now. And you might 00:15:36.600 --> 00:15:38.570 think, OK, so we're using this to_sql again. We 00:15:38.570 --> 00:15:41.290 get :id, :text from Post. Everything's great. 00:15:41.290 --> 00:15:42.519 Let's say that we wanted to get the SQL, 00:15:42.519 --> 00:15:44.350 though, for count. So we just say give me 00:15:44.350 --> 00:15:47.089 all of the Posts, the counts here, and you, 00:15:47.089 --> 00:15:49.500 you probably wouldn't say select(:id) and then count, but, 00:15:49.500 --> 00:15:52.110 you know, it's an example, so. And instead of 00:15:52.110 --> 00:15:54.510 getting back a SQL string, you instead get back 00:15:54.510 --> 00:15:56.970 a NoMethodError. 00:15:56.970 --> 00:15:58.880 So why did that happen? Well, it turns out 00:15:58.880 --> 00:16:03.820 that count is a terminal method. So, let's dive 00:16:03.820 --> 00:16:09.110 into why that happened. So. Being a terminal method, 00:16:09.110 --> 00:16:10.810 it would, it's executing immediately and returning a result 00:16:10.810 --> 00:16:12.630 to you instead of giving you an ActiveRecord relation, 00:16:12.630 --> 00:16:15.220 so that's why, it's good to distinguish between, and 00:16:15.220 --> 00:16:16.360 know which ones are going to do this to 00:16:16.360 --> 00:16:18.040 you, right. 00:16:18.040 --> 00:16:21.220 The way to actually get a count, and get 00:16:21.220 --> 00:16:22.930 a SQL string back out for it, would be 00:16:22.930 --> 00:16:24.459 to kind of dive into Arel here. Use your 00:16:24.459 --> 00:16:27.510 Arel table knowledge from before and call count on 00:16:27.510 --> 00:16:29.800 an Arel attribute. And then, of course, you can 00:16:29.800 --> 00:16:32.209 pass that to select as an array. Pass text 00:16:32.209 --> 00:16:34.519 as a symbol. And out comes this count, like 00:16:34.519 --> 00:16:35.399 you're expecting. 00:16:35.399 --> 00:16:37.399 So let's look at what terminal methods are. So, 00:16:37.399 --> 00:16:40.399 terminal methods execute the query immediately. They do not 00:16:40.399 --> 00:16:44.269 return a relation. They're mostly count, first, and last, 00:16:44.269 --> 00:16:45.630 so first and last are kind of like array 00:16:45.630 --> 00:16:49.220 accessors, right. To_a, pluck, and any of the enumerable 00:16:49.220 --> 00:16:53.750 methods will all execute your queries immediately. So, for 00:16:53.750 --> 00:16:56.760 example, this, each over these objects, and you would 00:16:56.760 --> 00:16:58.420 expect this to happen, because this is not a 00:16:58.420 --> 00:17:00.740 lazy enumerator. This is a regular enumerator and you're 00:17:00.740 --> 00:17:02.110 iterating over all of these things and you're putting 00:17:02.110 --> 00:17:04.049 out the text. And that's obviously going to execute 00:17:04.049 --> 00:17:04.569 right away. 00:17:04.569 --> 00:17:06.888 But, more surprising is this one. So, if I 00:17:06.888 --> 00:17:08.648 say each slice here and it's a lazy enumerator, 00:17:08.648 --> 00:17:10.159 in other words, I don't pass a block to 00:17:10.159 --> 00:17:14.839 it, this will also execute immediately. So just beware 00:17:14.839 --> 00:17:16.079 of that. 00:17:16.079 --> 00:17:19.549 All right. So let's go actually for real into 00:17:19.549 --> 00:17:22.470 select here now. So we've already seen how we 00:17:22.470 --> 00:17:24.929 can say dot count on an attribute, on an 00:17:24.929 --> 00:17:27.309 Arel attribute. We can also say dot sum, and 00:17:27.309 --> 00:17:29.450 that will give us this sum method. This is, 00:17:29.450 --> 00:17:32.320 these are all, by the way, MySQL examples. Notice, 00:17:32.320 --> 00:17:36.279 also, that Arel and ActiveRecord have nicely added this 00:17:36.279 --> 00:17:40.619 AS sum_id for you. So, any table, any, any 00:17:40.619 --> 00:17:43.169 column like this will automatically get a derived column 00:17:43.169 --> 00:17:44.669 name as well. 00:17:44.669 --> 00:17:46.649 You can change that column name by adding an 00:17:46.649 --> 00:17:49.059 as here. So just chain as right on the 00:17:49.059 --> 00:17:51.970 end of sum. Post visitors sum as visitor_total, and 00:17:51.970 --> 00:17:53.919 that will give it kind of a custom derived 00:17:53.919 --> 00:17:56.789 column name for you. You can also say dot 00:17:56.789 --> 00:18:00.190 maximum and dot minimum, and these will all use 00:18:00.190 --> 00:18:05.019 kind of the built-in SQL methods that you're expecting. 00:18:05.019 --> 00:18:07.309 Let's say, though, that you're trying to, you know, 00:18:07.309 --> 00:18:09.899 run a function that isn't part of Arel's DSL 00:18:09.899 --> 00:18:12.450 or Arel's knowledge, I guess. It's kind of outside 00:18:12.450 --> 00:18:16.899 of Arel's abilities. You can actually define a function 00:18:16.899 --> 00:18:19.830 call that, of any arbitrary function. This covers things 00:18:19.830 --> 00:18:22.249 like length or, like, any of the other methods 00:18:22.249 --> 00:18:24.869 that MySQL provides, or PostGres, that may differ from 00:18:24.869 --> 00:18:27.840 database to database. So, in this case, I am 00:18:27.840 --> 00:18:30.059 saying a named function dot new, passing in the 00:18:30.059 --> 00:18:31.600 name of the function and then giving it an 00:18:31.600 --> 00:18:33.200 array of arguments that function takes. 00:18:33.200 --> 00:18:36.549 And, then again, I'm also aliasing the result of 00:18:36.549 --> 00:18:37.899 that as length, in this case. So giving it 00:18:37.899 --> 00:18:40.690 a derived column name. 00:18:40.690 --> 00:18:43.979 So, we can shorten this a little bit by, 00:18:43.979 --> 00:18:46.279 cause it's a little bit verbose, by including Arel::Nodes. 00:18:46.279 --> 00:18:48.529 And then this just becomes NamedFunction dot new. So 00:18:48.529 --> 00:18:50.509 that's kind of a little hack you can add 00:18:50.509 --> 00:18:53.259 to your queries. 00:18:53.259 --> 00:18:54.679 Something kind of also pretty cool about Arel, this 00:18:54.679 --> 00:18:56.440 is kind of one of the other little cool 00:18:56.440 --> 00:18:58.799 select things, you can also say Arel dot star 00:18:58.799 --> 00:19:01.700 here, if you wanted to select every single record, 00:19:01.700 --> 00:19:03.580 right, so, or every single column from the record. 00:19:03.580 --> 00:19:06.169 So select start from post. Arel dot star exists, 00:19:06.169 --> 00:19:08.059 you can use it in a bunch of different 00:19:08.059 --> 00:19:09.049 places. 00:19:09.049 --> 00:19:13.299 OK. So, many people also don't know that select 00:19:13.299 --> 00:19:15.769 and Arel and ActiveRecord, I should say, have the 00:19:15.769 --> 00:19:17.210 ability to also just specify a from. So you 00:19:17.210 --> 00:19:20.559 want to specify a, a sub-query inside a from. 00:19:20.559 --> 00:19:21.940 This is pretty common. You can do this instead 00:19:21.940 --> 00:19:23.499 of a join or instead of a, a more 00:19:23.499 --> 00:19:25.899 complicated query here. So I'm saying select ID, and 00:19:25.899 --> 00:19:29.229 then I'm passing a sub-query into the from. 00:19:29.229 --> 00:19:30.710 So one thing that's really important to notice here 00:19:30.710 --> 00:19:33.070 is you'll notice that the end of the sele, 00:19:33.070 --> 00:19:36.509 the second select, I say dot AST. So also 00:19:36.509 --> 00:19:39.379 at any point in your construction, you can call 00:19:39.379 --> 00:19:40.909 AST on your relation, and it will give you 00:19:40.909 --> 00:19:43.639 back this massive tree that it's constructed. And in 00:19:43.639 --> 00:19:46.429 this case, from requires you to pass it an 00:19:46.429 --> 00:19:48.200 AST otherwise it gets confused. It doesn't know what 00:19:48.200 --> 00:19:49.470 to do with a relation. It only knows what 00:19:49.470 --> 00:19:51.279 to do with it if it's an AST or 00:19:51.279 --> 00:19:54.340 the column or things like that. So. Or a 00:19:54.340 --> 00:19:55.609 table, in this case. 00:19:55.609 --> 00:19:58.429 All right. So that pretty much covers select. There's 00:19:58.429 --> 00:19:59.600 a lot of things you can do with select. 00:19:59.600 --> 00:20:01.289 I was kind of breezing past a lot of 00:20:01.289 --> 00:20:02.129 it. If you have any questions, you can always 00:20:02.129 --> 00:20:04.559 talk to me or, or you know, I guess, 00:20:04.559 --> 00:20:07.570 go online. But there's not that many resources. Anyway. 00:20:07.570 --> 00:20:08.399 OK. So, the wonder of where. 00:20:08.399 --> 00:20:12.940 So, where is also gotten a lot better since 00:20:12.940 --> 00:20:16.389 Rails 2. I can pass in a title to 00:20:16.389 --> 00:20:18.799 my where here, where post dot where title is 00:20:18.799 --> 00:20:21.139 "Arel is Cool," and this will do what you 00:20:21.139 --> 00:20:23.849 expect. It will say, it'll construct the SQL query, 00:20:23.849 --> 00:20:25.720 user dot title equals this value. So no question 00:20:25.720 --> 00:20:27.539 marks here, which is very nice. 00:20:27.539 --> 00:20:30.049 But what happens if you want to, you know, 00:20:30.049 --> 00:20:31.749 do something a little more. Oh, here's the, here's 00:20:31.749 --> 00:20:33.279 the Arel version of this. So, you can do 00:20:33.279 --> 00:20:34.779 this with pure Arel. It's a little more verbose. 00:20:34.779 --> 00:20:36.919 You wouldn't really do this. But let's say you 00:20:36.919 --> 00:20:39.470 wanted to say not equal to or less than, 00:20:39.470 --> 00:20:41.299 things like that. You can do the same thing. 00:20:41.299 --> 00:20:43.869 So you can say not equal in this case, 00:20:43.869 --> 00:20:45.669 and notice that it'll put the band equals in 00:20:45.669 --> 00:20:48.849 MySQL. You can also say, kind of something cool 00:20:48.849 --> 00:20:51.299 here, not equal to nil, and it will serialize 00:20:51.299 --> 00:20:53.950 that as is not nil, which is what your, 00:20:53.950 --> 00:20:55.899 what you should be expecting. 00:20:55.899 --> 00:20:59.529 Some of the cool methods. Greater than, GT. Less 00:20:59.529 --> 00:21:02.479 than. LT. All makes sense. And all of these 00:21:02.479 --> 00:21:04.999 are just chained right off of this, this, this 00:21:04.999 --> 00:21:08.479 column attribute here. Here's greater than equal to - 00:21:08.479 --> 00:21:12.749 GTEQ, and also LTEQ. 00:21:12.749 --> 00:21:17.679 So, we also, oftentimes, in our queries, need to 00:21:17.679 --> 00:21:20.659 be able to and and or our way to 00:21:20.659 --> 00:21:23.119 a kind of a multiple where. We can do 00:21:23.119 --> 00:21:25.139 the same thing here. So, if you were to 00:21:25.139 --> 00:21:28.479 say where multiple times just with ActiveRecord, it would 00:21:28.479 --> 00:21:30.049 automatically give you an and. But if you wanted 00:21:30.049 --> 00:21:31.999 to specify an or, you would need to do 00:21:31.999 --> 00:21:34.830 something like this. So we have our title and 00:21:34.830 --> 00:21:37.529 attribute from post, we're saying equal "Arel is Cool", 00:21:37.529 --> 00:21:39.580 and the post id is equal to twenty-two or 00:21:39.580 --> 00:21:42.580 twenty-three. And notice that I have actually injected, so 00:21:42.580 --> 00:21:45.379 I have put, inside the and, I put another 00:21:45.379 --> 00:21:47.729 attribute and a dot or, and this will, Arel 00:21:47.729 --> 00:21:49.519 will just know, it will, the visitor pattern will 00:21:49.519 --> 00:21:52.070 know how to add the right parenthesis to this 00:21:52.070 --> 00:21:52.279 query. 00:21:52.279 --> 00:21:54.289 So, notice the output query has an and and 00:21:54.289 --> 00:21:57.789 then parenthesis and the or, so that no operator 00:21:57.789 --> 00:22:02.629 precedence messes us up here. Now, notice that I 00:22:02.629 --> 00:22:05.849 said equals twenty-two or twenty-three. I could also have 00:22:05.849 --> 00:22:10.139 put an in here. So in also accepts arguments 00:22:10.139 --> 00:22:13.229 here that could, essentially an array of values here. 00:22:13.229 --> 00:22:14.759 So it's the same thing, this will have the 00:22:14.759 --> 00:22:16.940 same effect. 00:22:16.940 --> 00:22:19.289 You can also compose your wheres. You can add 00:22:19.289 --> 00:22:21.440 named functions in here. So you can also do 00:22:21.440 --> 00:22:23.739 dot count, dot sum, all that stuff works. Those 00:22:23.739 --> 00:22:26.919 are aggregate functions. Here's length. Oh, I'm aggregating this 00:22:26.919 --> 00:22:28.210 stuff together and it all, it all just kind 00:22:28.210 --> 00:22:30.249 of works. It's pretty cool. 00:22:30.249 --> 00:22:33.320 All right. So let's get to my favorite part 00:22:33.320 --> 00:22:37.169 of Arel, and that's joins. So ActiveRecord makes joins 00:22:37.169 --> 00:22:39.669 pretty easy, except for when you're trying to do 00:22:39.669 --> 00:22:41.499 an outer join, and we'll just, we'll just see 00:22:41.499 --> 00:22:43.190 how that looks in a second here. 00:22:43.190 --> 00:22:45.330 So let's pretend we have this data model. It's 00:22:45.330 --> 00:22:48.789 a basic blog, essentially, so you've got a post. 00:22:48.789 --> 00:22:54.289 Post has_many comments. And each comment has an author. 00:22:54.289 --> 00:22:55.830 So let's, let's see what happens when we dive 00:22:55.830 --> 00:22:57.359 into some joins here. So we say join. This 00:22:57.359 --> 00:23:00.509 is without any help from Arel. Joins comments, or 00:23:00.509 --> 00:23:02.590 comment in this case, where id is forty-two. 00:23:02.590 --> 00:23:06.700 So, very cool. ActiveRecord inspects this model. It inspects 00:23:06.700 --> 00:23:10.549 the association between author and comment. And automatically generates 00:23:10.549 --> 00:23:15.549 the right join conditions for you. Pretty cool. You 00:23:15.549 --> 00:23:17.049 can also specify, as I mentioned, kind of a, 00:23:17.049 --> 00:23:18.779 a through table in this case. So I want 00:23:18.779 --> 00:23:20.919 to join comments, and then I also want, you 00:23:20.919 --> 00:23:22.899 know, using author, I've got comments, and I also 00:23:22.899 --> 00:23:24.729 want to find the posts of the comment reference 00:23:24.729 --> 00:23:27.200 or that, that comment is part of. 00:23:27.200 --> 00:23:29.599 So, this will also do what you expect. It 00:23:29.599 --> 00:23:32.919 will grab all the correct attributes for the models. 00:23:32.919 --> 00:23:36.039 Hook them all together. A lot of cool introspection. 00:23:36.039 --> 00:23:37.979 But, again, what about outer joins? So, you notice 00:23:37.979 --> 00:23:40.879 back here, it's inter-joined. And there's really no way 00:23:40.879 --> 00:23:43.169 to specify an outer join without diving in to 00:23:43.169 --> 00:23:46.159 Arel. At least that I'm aware of. 00:23:46.159 --> 00:23:48.409 OK. So let's look at this guy again. So 00:23:48.409 --> 00:23:49.950 let's say I wanted to add an outjoin again. 00:23:49.950 --> 00:23:54.019 Well, first, it kind of bears mentioning here that 00:23:54.019 --> 00:23:57.340 you can turn this stuff into, also, pure Arel 00:23:57.340 --> 00:24:00.359 stuff. So, we have a joins comment. We can 00:24:00.359 --> 00:24:02.259 also say comment dot joins post, and then call 00:24:02.259 --> 00:24:05.830 this method called join sources. So, join sources is 00:24:05.830 --> 00:24:08.090 a way of saying, I have gotten this query 00:24:08.090 --> 00:24:10.179 from ActiveRecord. And this is, again, it's just using 00:24:10.179 --> 00:24:11.879 ActiveRecord. And I just want to grab the join 00:24:11.879 --> 00:24:13.340 sources. I don't care about the select or the 00:24:13.340 --> 00:24:15.119 where. Just give me the join sources and pass 00:24:15.119 --> 00:24:17.919 that onto whoever is wrapping. In this case, another 00:24:17.919 --> 00:24:18.629 query. 00:24:18.629 --> 00:24:22.469 You can do the same thing with first join. 00:24:22.469 --> 00:24:25.999 And then you can also specify the on conditions 00:24:25.999 --> 00:24:28.719 manually here. So, we have a, a join comment 00:24:28.719 --> 00:24:32.169 Arel table, and we're joining that on :comment_id is 00:24:32.169 --> 00:24:34.440 equal to author :comment_id, and then saying join sources. 00:24:34.440 --> 00:24:35.489 And we're doing this for both. 00:24:35.489 --> 00:24:36.999 Now, this is gonna get pretty verbose, but at 00:24:36.999 --> 00:24:39.159 this point, we can finally add our outer join. 00:24:39.159 --> 00:24:43.259 OK. So notice that the attribute, Arel attribute dot 00:24:43.259 --> 00:24:45.379 join takes a third argument, or a second argument, 00:24:45.379 --> 00:24:46.690 that is the join type. So you could say 00:24:46.690 --> 00:24:50.599 inner join here, or outer join. And that results 00:24:50.599 --> 00:24:54.309 in, essentially, the same query. But this time an 00:24:54.309 --> 00:24:55.049 outer join. 00:24:55.049 --> 00:24:58.210 You're thinking, that is a lot of code. Why 00:24:58.210 --> 00:25:00.769 would I ever type that much code? And you're 00:25:00.769 --> 00:25:03.059 right. That's way too much code. So, part of 00:25:03.059 --> 00:25:08.899 the Arel-helpers library is called JoinAssociation. And this will 00:25:08.899 --> 00:25:12.039 introspect your model, just like ActiveRecord would do. Hand 00:25:12.039 --> 00:25:14.849 those off to what's called a select manager, and 00:25:14.849 --> 00:25:17.799 then use the second argument Arel::OuterJoin to give you 00:25:17.799 --> 00:25:19.919 an outer join, but without all the tedium of 00:25:19.919 --> 00:25:21.609 specifying all these conditions. 00:25:21.609 --> 00:25:24.249 So I can slide this in here. JoinAssociation, and 00:25:24.249 --> 00:25:27.469 I supply the original model and then the association 00:25:27.469 --> 00:25:30.379 name, and then Arel::OuterJoin. And I can do that 00:25:30.379 --> 00:25:33.919 for both of these guys. Pretty cool. 00:25:33.919 --> 00:25:38.519 But wait, there's more. All right. So you've got 00:25:38.519 --> 00:25:40.049 this guy, and you're like, you know, I really 00:25:40.049 --> 00:25:46.609 want to supply some extra custom on conditions. Fortunately, 00:25:46.609 --> 00:25:48.950 join dependency or join association, rather, will yield a 00:25:48.950 --> 00:25:52.259 block to you that has both the association name, 00:25:52.259 --> 00:25:55.719 which is a string or symbol, and also the, 00:25:55.719 --> 00:25:59.690 a join condition's intermediate query object. So you can 00:25:59.690 --> 00:26:02.379 say join conditions and, and then pass in anything 00:26:02.379 --> 00:26:04.929 else you would like to construct. In this case, 00:26:04.929 --> 00:26:06.499 we're making sure that the created_at date is less 00:26:06.499 --> 00:26:10.429 than or equal to yesterday. 00:26:10.429 --> 00:26:12.599 So this will, again, in the same query, but 00:26:12.599 --> 00:26:14.279 this time. And it would have been an outer 00:26:14.279 --> 00:26:16.489 join except that I, I actually took out, for 00:26:16.489 --> 00:26:18.999 space reason, I took out the extra OuterJoin call 00:26:18.999 --> 00:26:21.049 there. So this, this would return inner join and 00:26:21.049 --> 00:26:22.749 then an outer join. 00:26:22.749 --> 00:26:25.989 All right, so let's talk about join tables. As 00:26:25.989 --> 00:26:29.229 for, for, so far we've been talking about tables 00:26:29.229 --> 00:26:33.019 that have defined associations between them, and those associations 00:26:33.019 --> 00:26:35.629 are not has_and_belongs_to_many. So I'm trying to kind of 00:26:35.629 --> 00:26:37.549 freshen us a little bit and say, let's talk 00:26:37.549 --> 00:26:40.259 a table that, or two tables that are associated 00:26:40.259 --> 00:26:43.379 but via a join table. So, you might have 00:26:43.379 --> 00:26:45.499 a series of courses that are taught by a 00:26:45.499 --> 00:26:48.690 series of teachers. Multiple teachers can teach one course, 00:26:48.690 --> 00:26:50.739 and courses can have multiple teachers. 00:26:50.739 --> 00:26:54.590 So, in this case, you've got three tables. Courses, 00:26:54.590 --> 00:26:57.379 teachers, and courses underscore teachers. This is pretty common 00:26:57.379 --> 00:27:00.559 in Rails, I think. So, it means you have, 00:27:00.559 --> 00:27:02.629 again, the course table. You can refer to that 00:27:02.629 --> 00:27:06.499 with course dot arel_table like we saw before. Teacher 00:27:06.499 --> 00:27:09.320 table, again, reference that with Teacher dot arel_table. But 00:27:09.320 --> 00:27:12.109 then we've got some magic in here for courses_teachers, 00:27:12.109 --> 00:27:14.989 because there, there really is no model for this. 00:27:14.989 --> 00:27:17.330 There's no constant we can use to reference this 00:27:17.330 --> 00:27:19.940 table. So instead, we need to create an object 00:27:19.940 --> 00:27:20.879 for it. 00:27:20.879 --> 00:27:22.950 So we'll create an Arel::Table. This is doing this 00:27:22.950 --> 00:27:25.849 manually. We're giving it the name courses_teachers, and now 00:27:25.849 --> 00:27:28.090 we can use this variable ct anywhere we cant 00:27:28.090 --> 00:27:30.830 to refer to this table. So, for example, this 00:27:30.830 --> 00:27:33.149 query. I want to say joins, and we have, 00:27:33.149 --> 00:27:35.889 you know, courses join teachers, and then just for 00:27:35.889 --> 00:27:38.539 the purposes of illustration here I've shown, you know, 00:27:38.539 --> 00:27:40.529 what you would do in Arel to construct the 00:27:40.529 --> 00:27:42.919 join conditions here, so. You know, on this on 00:27:42.919 --> 00:27:44.899 this and then dot join sources. 00:27:44.899 --> 00:27:47.399 And I didn't show the SQL output of this 00:27:47.399 --> 00:27:49.119 but I think it's pretty, pretty clear from the 00:27:49.119 --> 00:27:50.109 previous examples. 00:27:50.109 --> 00:27:53.179 OK. So that wraps up join. So let's talk 00:27:53.179 --> 00:27:56.999 about order. Order is actually the simplest of the 00:27:56.999 --> 00:27:59.989 kind of SQL constructs I'll talk about today. You 00:27:59.989 --> 00:28:01.499 all know that you can say post dot order 00:28:01.499 --> 00:28:03.809 visitors and visitors in an integer in this case, 00:28:03.809 --> 00:28:07.269 then dot to_sql. And this will give you a, 00:28:07.269 --> 00:28:09.809 a return value of, you know, all of your, 00:28:09.809 --> 00:28:12.700 all of your posts ordered by the number of 00:28:12.700 --> 00:28:15.119 visitors. You can also, and some people don't know 00:28:15.119 --> 00:28:17.619 this, but this also just an ActiveRecord construction here. 00:28:17.619 --> 00:28:21.359 Post.order, that should be (:visitors), reverse order. This will 00:28:21.359 --> 00:28:25.570 add a descending clause or a descending keyword to 00:28:25.570 --> 00:28:26.479 the end of your query. 00:28:26.479 --> 00:28:29.320 Now, you can also do this in Arel. You 00:28:29.320 --> 00:28:31.659 can say post(:views) descending to SQL here, and this 00:28:31.659 --> 00:28:36.059 is essentially the same thing. All right. So let's 00:28:36.059 --> 00:28:37.899 talk about sub-queries with in. 00:28:37.899 --> 00:28:40.019 So we talked about sub-queries with from. You can 00:28:40.019 --> 00:28:41.519 also do sub-queries with in, and you can use 00:28:41.519 --> 00:28:43.539 that AST that we talked about before. SO here's 00:28:43.539 --> 00:28:45.609 an example of that. This is a very trivial 00:28:45.609 --> 00:28:47.899 example, but we say post dot where and then 00:28:47.899 --> 00:28:50.830 we say Post.arel_table in and we supply it another 00:28:50.830 --> 00:28:53.279 query. So we're adding a sub-query into this in 00:28:53.279 --> 00:28:54.149 call. 00:28:54.149 --> 00:28:56.960 So that's pretty cool. And it does the right 00:28:56.960 --> 00:28:59.809 thing. It adds the correct parenthesis and everything's great. 00:28:59.809 --> 00:29:02.609 All right. And then like queries with matches, you 00:29:02.609 --> 00:29:04.489 can also do this with Arel. So most of 00:29:04.489 --> 00:29:06.019 the time you'd probably have to say, well, I'd 00:29:06.019 --> 00:29:07.330 like to, if I wanted to do a like, 00:29:07.330 --> 00:29:09.409 in other words match, kind of fuzzy match against 00:29:09.409 --> 00:29:11.599 a column in your database, you would need to 00:29:11.599 --> 00:29:13.830 add a like or, I guess, what is it 00:29:13.830 --> 00:29:17.499 in PostGres? Like. What is it, double ilike or 00:29:17.499 --> 00:29:19.769 something. Anyway. So it's one of those things. 00:29:19.769 --> 00:29:21.409 So and then usually you have to add wild 00:29:21.409 --> 00:29:23.969 card characters to this, and this would normally be 00:29:23.969 --> 00:29:26.859 a big string in your query. But, you can 00:29:26.859 --> 00:29:28.989 fix that with Arel. So, Post dot where, and 00:29:28.989 --> 00:29:30.820 we have a matches Arel and then I just 00:29:30.820 --> 00:29:31.539 put my - so you do have to add 00:29:31.539 --> 00:29:34.339 these. Unfortunately you have to add these, these wild 00:29:34.339 --> 00:29:37.859 card characters, but it's just dot matches. Super easy. 00:29:37.859 --> 00:29:40.539 And that gets serialized out into a like. 00:29:40.539 --> 00:29:42.450 And, interestingly enough, I didn't know this when I 00:29:42.450 --> 00:29:44.979 started out, but it looks like it's actually taking 00:29:44.979 --> 00:29:50.080 that string and encoding it into Base, Base16. Hexadecimal. 00:29:50.080 --> 00:29:54.029 All right. So we've learned all this awesome stuff 00:29:54.029 --> 00:29:56.159 about Arel. We know that it can do joins 00:29:56.159 --> 00:29:58.599 and wheres and likes and ins and all kinds 00:29:58.599 --> 00:30:01.219 of awesome stuff, all without the use of, well, 00:30:01.219 --> 00:30:04.349 mostly without the use of strings. We also learned 00:30:04.349 --> 00:30:07.409 that it's pretty chainable. So, when you think about 00:30:07.409 --> 00:30:09.359 something that's chainable, the thing that comes to mind, 00:30:09.359 --> 00:30:11.759 for me at least, almost immediately, is the builder 00:30:11.759 --> 00:30:12.839 pattern. 00:30:12.839 --> 00:30:14.409 And I think, well, why would I want to 00:30:14.409 --> 00:30:17.099 construct a huge query inside my model or controller? 00:30:17.099 --> 00:30:20.009 I'd much rather have an object do this for 00:30:20.009 --> 00:30:22.109 me. Now, is that always the right choice? No, 00:30:22.109 --> 00:30:23.649 of course not. But it's, it can be nice 00:30:23.649 --> 00:30:27.210 to encapsulate logic, like building a huge query. So 00:30:27.210 --> 00:30:28.940 that's why the next step, or the next point 00:30:28.940 --> 00:30:31.690 I'm gonna bring up, is how to construct a 00:30:31.690 --> 00:30:33.609 query builder. And this is also part of the 00:30:33.609 --> 00:30:36.159 ActiveRecord, or the arel-helpers gem that I mentioned before. 00:30:36.159 --> 00:30:39.009 So this is pretty much the entire implementation of 00:30:39.009 --> 00:30:42.830 query builder. You have something that can forward some 00:30:42.830 --> 00:30:45.469 methods for you. It'll query, it'll forward to_a, to_sql, 00:30:45.469 --> 00:30:48.339 and to_each, this variable called query inside your object. 00:30:48.339 --> 00:30:50.700 It accepts a query and sets that as an 00:30:50.700 --> 00:30:52.450 instance variable. Then it also has this kind of 00:30:52.450 --> 00:30:55.109 funky method name reflect, and reflect just instantiates a 00:30:55.109 --> 00:30:57.039 new instance of the class and adds the query. 00:30:57.039 --> 00:30:58.989 So it's essentially implementing that chaining idea. 00:30:58.989 --> 00:31:01.299 All right, so here's a lot of code, and 00:31:01.299 --> 00:31:03.029 we're gonna go over each of these pieces individually, 00:31:03.029 --> 00:31:05.429 but this is a, a post query builder. So 00:31:05.429 --> 00:31:07.369 I was trying to query some posts. Let's look 00:31:07.369 --> 00:31:10.289 at this first method. So, notice this first inherited 00:31:10.289 --> 00:31:12.969 from QueryBuilder. So let's say we have a method 00:31:12.969 --> 00:31:16.399 called with_title_matching and it accepts a title. Calls reflect 00:31:16.399 --> 00:31:18.759 and says dot where title matches and it adds, 00:31:18.759 --> 00:31:19.869 you know, you can see it adds the percent 00:31:19.869 --> 00:31:22.139 signs for the wild card characters. This is all 00:31:22.139 --> 00:31:23.659 encapsulated inside this guy. So you all you have 00:31:23.659 --> 00:31:26.599 to do is just call with_title_matching on your QueryBuilder. 00:31:26.599 --> 00:31:31.190 Let's go down to with_comments_by. With_comments_by(username), so we join 00:31:31.190 --> 00:31:35.709 comments and authors, and then where(Author[:username].in(usernames)). So we are 00:31:35.709 --> 00:31:39.399 expecting an array of usernames and this, again, reflects 00:31:39.399 --> 00:31:40.779 this query back to a new instance, returns a 00:31:40.779 --> 00:31:43.599 new instance to QueryBuilder, too, this time with. Cause, 00:31:43.599 --> 00:31:45.879 again, we're chaining all these things, right, so now 00:31:45.879 --> 00:31:47.700 we have both. If we call both these methods 00:31:47.700 --> 00:31:49.839 in a row, we would have find_by_username and with_title_matching, 00:31:49.839 --> 00:31:50.429 both together. 00:31:50.429 --> 00:31:56.669 So the last method I have here is since_yesterday. 00:31:56.669 --> 00:32:01.409 The post[:create_at] greater than equal to Date dot yesterday. 00:32:01.409 --> 00:32:04.119 Which means that now I can do something like 00:32:04.119 --> 00:32:06.019 this. There's not a lot of ugly syntax in 00:32:06.019 --> 00:32:09.749 here. It's all pretty clean. PostQueryBuilder.new with_comments_by pass in 00:32:09.749 --> 00:32:12.539 an array of usernames, dot to_sql. And notice it's 00:32:12.539 --> 00:32:14.200 doing all of the joining for me, like you'd 00:32:14.200 --> 00:32:16.629 expect. I can then just say dot with_title_matching("arel"), it 00:32:16.629 --> 00:32:20.059 adds that like, and then I can say since_yesterday 00:32:20.059 --> 00:32:23.469 and it will add that third condition. 00:32:23.469 --> 00:32:28.379 Pretty cool. Thanks. 00:32:28.379 --> 00:32:34.559 Cool. Appreciate that. But you might be sitting here 00:32:34.559 --> 00:32:37.379 thinking, god, this is super complicated. There's got to 00:32:37.379 --> 00:32:39.749 be something out there to help me write these 00:32:39.749 --> 00:32:41.859 queries better, because, you know, it's a lot of 00:32:41.859 --> 00:32:42.969 verbosity, it's a lot of code, it's a lot 00:32:42.969 --> 00:32:46.190 of extra stuff to remember. Well, fortunately, there is. 00:32:46.190 --> 00:32:47.489 So, I worked for a little while on a 00:32:47.489 --> 00:32:51.269 project called Scuttle. It was gonna be called Ariel, 00:32:51.269 --> 00:32:53.200 like Little Mermaid, Ariel, you know, but it was, 00:32:53.200 --> 00:32:56.969 unfortunately, too linguistically similar to Arel and so I 00:32:56.969 --> 00:32:58.169 thought I would have trouble saying it on stage. 00:32:58.169 --> 00:33:01.179 So I named it after Ariel in The Little 00:33:01.179 --> 00:33:03.190 Mermaid's best friend Scuttle. So if you go to 00:33:03.190 --> 00:33:05.809 scuttle dot io - thanks - if you go 00:33:05.809 --> 00:33:07.789 to scuttle dot io, you'll see a screen like 00:33:07.789 --> 00:33:10.859 this. It's an editor. You can enter in a 00:33:10.859 --> 00:33:12.609 SQL query, and at the bottom it will convert 00:33:12.609 --> 00:33:14.799 it to Arel for you. 00:33:14.799 --> 00:33:16.129 [applause] 00:33:16.129 --> 00:33:18.799 Thanks. Thanks. 00:33:18.799 --> 00:33:25.080 Again, it's gonna be pretty verbose, but you can 00:33:25.080 --> 00:33:26.759 tune your queries. There's a lot of other kind 00:33:26.759 --> 00:33:29.830 of text below explaining what it does. This is 00:33:29.830 --> 00:33:32.019 kind of a combination of three different projects, and 00:33:32.019 --> 00:33:33.209 you can check these out on GitHub if you're 00:33:33.209 --> 00:33:36.749 curious. So, I could not find a SQL parser 00:33:36.749 --> 00:33:39.029 in Ruby. I tried so hard to find one 00:33:39.029 --> 00:33:41.349 of these. And there just isn't one. The only, 00:33:41.349 --> 00:33:43.599 as far as I can tell, the only SQL 00:33:43.599 --> 00:33:45.149 parsers that I could find were written in Java, 00:33:45.149 --> 00:33:47.979 and using this thing called Antler, which is a 00:33:47.979 --> 00:33:49.059 parser generator. 00:33:49.059 --> 00:33:52.830 So, I finally found an Antler grammar for the 00:33:52.830 --> 00:33:57.099 Apache incubator tajo project, and grabbed that, generated these 00:33:57.099 --> 00:34:01.169 appropriate classes, and then wrote a JRuby wrapped around 00:34:01.169 --> 00:34:03.469 that that allows you to parse, it allows you 00:34:03.469 --> 00:34:07.469 to parse queries. So it only works currently with 00:34:07.469 --> 00:34:10.300 select and, I think, insert, because Apache incubator tajo 00:34:10.300 --> 00:34:13.540 apparently doesn't need to do anything else. So, and 00:34:13.540 --> 00:34:15.060 I wasn't ready to go write a bunch of 00:34:15.060 --> 00:34:16.619 other Antler stuff. So, if you guys, if somebody 00:34:16.619 --> 00:34:18.369 knows Antler around here, please, help me out. We 00:34:18.369 --> 00:34:18.859 can, we can make it better. 00:34:18.859 --> 00:34:21.520 And then the third part of this is something 00:34:21.520 --> 00:34:25.010 called scuttle-server, which is just a Sinatra app on 00:34:25.010 --> 00:34:27.909 top of JRuby that uses these two components. And, 00:34:27.909 --> 00:34:29.829 also I forgot to mention there's also this scuttle-Java. 00:34:29.829 --> 00:34:32.480 But anyway. Anyway, it uses these components to, to 00:34:32.480 --> 00:34:33.719 drive the website. 00:34:33.719 --> 00:34:37.099 OK. So that was a whirlwind. I have, like, 00:34:37.099 --> 00:34:39.579 five minutes left. Thank you guys all for coming 00:34:39.579 --> 00:34:40.208 and for listening.