1 00:00:17,190 --> 00:00:20,210 CAMERON DUTRO: OK. Sweet. Welcome, everybody. 2 00:00:20,210 --> 00:00:22,120 Thank you all for coming to this talk. 3 00:00:22,120 --> 00:00:25,970 This is the, the second talk that I've given at a RailsConf, 4 00:00:25,970 --> 00:00:28,140 and first one that I gave was on 5 00:00:28,140 --> 00:00:31,100 internationalization, and there were like, maybe, 6 00:00:31,100 --> 00:00:33,330 fifteen people in the audience. 7 00:00:33,330 --> 00:00:34,790 And now there are a lot more. 8 00:00:34,790 --> 00:00:36,510 I guess this is a more popular topic, 9 00:00:36,510 --> 00:00:38,879 I don't know. So, cool. 10 00:00:38,879 --> 00:00:41,900 So, again, thanks all for coming. I am Cameron 11 00:00:41,900 --> 00:00:44,980 Dutro. I work for the U.S. Government. No, no, 12 00:00:44,980 --> 00:00:49,769 not really. I work for Twitter. I'm on International 13 00:00:49,769 --> 00:00:52,739 Engineering, which explains the topic of my previous talk, 14 00:00:52,739 --> 00:00:55,659 two years ago. You can follow me at camertron 15 00:00:55,659 --> 00:00:58,589 on Twitter. Look at me on GitHub as well. 16 00:00:58,589 --> 00:01:01,230 A little bit of personal background, this is my 17 00:01:01,230 --> 00:01:04,569 cat. He's pretty cute. His name is Chester. You 18 00:01:04,569 --> 00:01:08,400 can follow him at @catwithtail. All right, so, we're 19 00:01:08,400 --> 00:01:11,479 gonna do a little bit of an interactive exercise 20 00:01:11,479 --> 00:01:14,900 before I dive into the deep technical portions of 21 00:01:14,900 --> 00:01:17,940 this. How many, raise your hand if you've ever 22 00:01:17,940 --> 00:01:22,710 written Rails 2 code? You guys, wow, look at 23 00:01:22,710 --> 00:01:25,180 that. OK. Nice. How many of you, in your 24 00:01:25,180 --> 00:01:28,040 Rails 2 code, saw maybe a query that looked 25 00:01:28,040 --> 00:01:29,650 like this? Yeah. 26 00:01:29,650 --> 00:01:33,370 So, a ton of strings in here. There's not 27 00:01:33,370 --> 00:01:36,120 really an object-oriented way to construct queries in Rails 28 00:01:36,120 --> 00:01:38,690 2. So, thank heavens for Rails 3 and 4, 29 00:01:38,690 --> 00:01:42,820 because they introduced a much more chainable object-oriented pattern 30 00:01:42,820 --> 00:01:46,220 for doing query construction. So, how many of you 31 00:01:46,220 --> 00:01:48,430 wrote Rails 3 or 4 code that looks like 32 00:01:48,430 --> 00:01:53,990 this? 33 00:01:53,990 --> 00:01:57,390 That's pretty much the same thing as before. And, 34 00:01:57,390 --> 00:01:59,030 I don't want to say, you know, you're doing 35 00:01:59,030 --> 00:02:06,030 it wrong, but, you're doing it wrong. So, let's 36 00:02:06,340 --> 00:02:08,720 go into this code and maybe clean it up 37 00:02:08,720 --> 00:02:10,869 a little bit, right. 38 00:02:10,869 --> 00:02:12,810 So I just moved one of those strings that 39 00:02:12,810 --> 00:02:15,620 defined a join association, or a join, on, on 40 00:02:15,620 --> 00:02:17,620 this table, and I moved it up into a 41 00:02:17,620 --> 00:02:20,480 symbol, so I'm joining the comments table on Post. 42 00:02:20,480 --> 00:02:23,450 So this is a lot cleaner looking, and it's 43 00:02:23,450 --> 00:02:27,120 allowing ActiveRecord to introspect the associations between these two 44 00:02:27,120 --> 00:02:30,290 models, derive the columns to use, and then automatically 45 00:02:30,290 --> 00:02:31,870 construct that query for you to do the right 46 00:02:31,870 --> 00:02:33,580 thing. 47 00:02:33,580 --> 00:02:35,870 So that's awesome. But, we still have a couple 48 00:02:35,870 --> 00:02:37,950 other problems with this code, right. We still have 49 00:02:37,950 --> 00:02:40,370 these question marks in the where clause, and we 50 00:02:40,370 --> 00:02:42,000 still have that second join. And the reason that 51 00:02:42,000 --> 00:02:47,110 join is not yet a symbol is because it's 52 00:02:47,110 --> 00:02:50,130 an association that doesn't pertain immediately to Post, right. 53 00:02:50,130 --> 00:02:52,900 It's joining authors in relation to comments. 54 00:02:52,900 --> 00:02:55,370 And, by the way, in this system, authors are 55 00:02:55,370 --> 00:02:57,310 a part of comments, not part of posts. I 56 00:02:57,310 --> 00:02:59,120 know it's a little confusing. So, you could simplify 57 00:02:59,120 --> 00:03:00,840 this even further and you could use this cool 58 00:03:00,840 --> 00:03:03,750 hash rocket syntax that says, even though comments and 59 00:03:03,750 --> 00:03:05,860 author are not, well comments is, but author is 60 00:03:05,860 --> 00:03:07,450 not directly related to posts, I want to get 61 00:03:07,450 --> 00:03:09,590 there through comments. And so I can add this 62 00:03:09,590 --> 00:03:12,069 hash rocket style syntax also. 63 00:03:12,069 --> 00:03:14,720 But we still have these question marks. So, fortunately 64 00:03:14,720 --> 00:03:17,050 ActiveRecord has given us a lot over the past 65 00:03:17,050 --> 00:03:19,240 couple of years that have passed between 2 and 66 00:03:19,240 --> 00:03:21,170 3 and 4. But this is still a little 67 00:03:21,170 --> 00:03:23,700 bit, you know, a little bit dirty, and, especially 68 00:03:23,700 --> 00:03:25,250 if you wanted to do something like an outer 69 00:03:25,250 --> 00:03:27,150 join in this join clause, or if you wanted 70 00:03:27,150 --> 00:03:29,780 to do something a little bit more than say 71 00:03:29,780 --> 00:03:31,160 I want to say something is equal to something 72 00:03:31,160 --> 00:03:34,110 else, right. A column is equal to a value. 73 00:03:34,110 --> 00:03:36,510 So I kind of look at this style as 74 00:03:36,510 --> 00:03:39,090 better but, you know, you're still handed a great 75 00:03:39,090 --> 00:03:44,450 weapon and not quite using it appropriately, right. So 76 00:03:44,450 --> 00:03:46,170 let's look at parts of this query. I'm gonna 77 00:03:46,170 --> 00:03:48,290 reuse the previous string example to identify what's wrong 78 00:03:48,290 --> 00:03:50,640 with these queries, but let's look at the previous 79 00:03:50,640 --> 00:03:52,700 queries. And we'll extract little bits of this and 80 00:03:52,700 --> 00:03:56,870 talk about why they're less than ideal. 81 00:03:56,870 --> 00:03:59,780 So you've got this join, and the first thing 82 00:03:59,780 --> 00:04:01,380 that's wrong with this is you have to write 83 00:04:01,380 --> 00:04:04,900 the words join and the words on. And that's 84 00:04:04,900 --> 00:04:07,880 not difficult for somebody to do. It's not difficult 85 00:04:07,880 --> 00:04:10,670 to type J-O-I-N on your keyboard. But it does 86 00:04:10,670 --> 00:04:13,599 mean that you have to know MySQL or PostGreSQL 87 00:04:13,599 --> 00:04:18,569 or SQLite syntax to get this done. You also 88 00:04:18,569 --> 00:04:21,569 have, essentially, no syntax checking. So, you don't have 89 00:04:21,569 --> 00:04:24,000 any idea, having written this string, you don't have 90 00:04:24,000 --> 00:04:27,750 any idea that authors is an actual table, or 91 00:04:27,750 --> 00:04:30,550 that authors dot id is a column. All you 92 00:04:30,550 --> 00:04:32,000 know is that you have this string and that's 93 00:04:32,000 --> 00:04:34,000 getting handed to the, to ActiveRecord, and then it's 94 00:04:34,000 --> 00:04:36,600 executing it for you. 95 00:04:36,600 --> 00:04:38,430 Let's look at the where clause here as well, 96 00:04:38,430 --> 00:04:40,430 and I kind of alluded to these problems before, 97 00:04:40,430 --> 00:04:42,380 but we'll go over these in detail. So, again, 98 00:04:42,380 --> 00:04:44,410 you have to know MySQL syntax, you have to 99 00:04:44,410 --> 00:04:47,400 know that there's an and between these two things. 100 00:04:47,400 --> 00:04:50,020 And, which of course, and all these syntactical issues, 101 00:04:50,020 --> 00:04:52,210 you know, these may not persist across different flavors 102 00:04:52,210 --> 00:04:56,180 of databases, so maybe MySQL has this particular construction 103 00:04:56,180 --> 00:04:57,990 for and, but it could be that PostGres has 104 00:04:57,990 --> 00:04:59,970 a different one. And it probably doesn't, but you 105 00:04:59,970 --> 00:05:02,850 know, you don't know that. 106 00:05:02,850 --> 00:05:05,340 It's confusing also to match arguments with question marks. 107 00:05:05,340 --> 00:05:06,560 I mean, how many of you have looked at 108 00:05:06,560 --> 00:05:08,440 a piece of code and thought to yourself, man, 109 00:05:08,440 --> 00:05:10,480 there are so many join conditions, or so many 110 00:05:10,480 --> 00:05:13,330 where conditions here? And I don't know which argument 111 00:05:13,330 --> 00:05:15,650 matches up to which question mark. Yeah, I mean 112 00:05:15,650 --> 00:05:19,120 I've done that a bunch of times. 113 00:05:19,120 --> 00:05:20,970 It's also not very object-oriented, right. You have this 114 00:05:20,970 --> 00:05:23,370 where string and then comma, comma, comma. It's almost 115 00:05:23,370 --> 00:05:26,380 functional in its style. And, again, we have that 116 00:05:26,380 --> 00:05:28,280 no syntax-checking problem. 117 00:05:28,280 --> 00:05:30,410 So, you might think to yourself, boy, I wish 118 00:05:30,410 --> 00:05:31,880 there was a better way to do this, and 119 00:05:31,880 --> 00:05:34,000 maybe I'll do a search, and you might come 120 00:05:34,000 --> 00:05:37,050 across a StackOverflow article that tells you to use 121 00:05:37,050 --> 00:05:39,880 strings in your queries. And you might come across 122 00:05:39,880 --> 00:05:43,690 RailsCast 202, which tells you to use strings in 123 00:05:43,690 --> 00:05:47,199 your queries. You might find blogs. You might ask 124 00:05:47,199 --> 00:05:50,340 coworkers. You might ask friends, family members, or even 125 00:05:50,340 --> 00:05:52,440 your cat, and they'll all tell you that you 126 00:05:52,440 --> 00:05:53,699 should use strings in your queries. 127 00:05:53,699 --> 00:05:56,030 But I'm here to tell you that no, you 128 00:05:56,030 --> 00:05:58,500 can avoid doing that. You can keep calm, you 129 00:05:58,500 --> 00:06:01,840 can avoid literal strings in your queries. Let's look 130 00:06:01,840 --> 00:06:04,430 at the previous query, but completely, I like to 131 00:06:04,430 --> 00:06:08,350 use the word, arelized. This is the query arelized. 132 00:06:08,350 --> 00:06:10,229 So, notice, there's a bunch of cool things going 133 00:06:10,229 --> 00:06:11,430 on here. 134 00:06:11,430 --> 00:06:13,710 First, you don't have to know SQL syntax. All 135 00:06:13,710 --> 00:06:15,690 you have to know is Ruby syntax. And, you 136 00:06:15,690 --> 00:06:17,199 know, of course, this is a little bit of 137 00:06:17,199 --> 00:06:18,870 a DSL, so you might have to go about 138 00:06:18,870 --> 00:06:21,009 learning Arel as well, learning what it can do. 139 00:06:21,009 --> 00:06:23,979 But that's what this talk is for. That's why 140 00:06:23,979 --> 00:06:24,639 you're here. 141 00:06:24,639 --> 00:06:26,620 Ruby syntax-checking, you get that for free. So if 142 00:06:26,620 --> 00:06:28,600 you have an unbalanced parenthesis or you're referencing a 143 00:06:28,600 --> 00:06:31,240 model that doesn't exist, it will throw an exception 144 00:06:31,240 --> 00:06:33,139 and you can go and it'll fail fast, in 145 00:06:33,139 --> 00:06:34,449 other words, you can go fix that bug before 146 00:06:34,449 --> 00:06:36,940 you deploy to production. 147 00:06:36,940 --> 00:06:41,160 So, because ActiveRecord 3 and 4 are chainable, it 148 00:06:41,160 --> 00:06:42,570 means Arel is chainable as well. It allows you 149 00:06:42,570 --> 00:06:46,020 to compose queries in kind of step-by-step pattern, a 150 00:06:46,020 --> 00:06:49,960 step-by-step fashion. So it's also chainable. And you have 151 00:06:49,960 --> 00:06:52,199 none of those dang question marks. 152 00:06:52,199 --> 00:06:55,229 It's also a lot easier to read, at least 153 00:06:55,229 --> 00:06:57,600 in my opinion, because what I'm reading is Ruby, 154 00:06:57,600 --> 00:06:59,860 not SQL embedded in Ruby, which to me is 155 00:06:59,860 --> 00:07:03,680 weird. So, here's what we're gonna cover today. I've 156 00:07:03,680 --> 00:07:05,919 kind of already gone over the, the initial, you 157 00:07:05,919 --> 00:07:07,690 know, diagram of, of how, kind of the state 158 00:07:07,690 --> 00:07:09,740 of the world now, and so when I cover 159 00:07:09,740 --> 00:07:11,680 what ActiveRecord is, what Arel is, there's kind of 160 00:07:11,680 --> 00:07:14,210 a, an indistinct line separating those two, and I'll 161 00:07:14,210 --> 00:07:16,120 try to separate that. 162 00:07:16,120 --> 00:07:19,400 Talk about how to reference tables and columns. What 163 00:07:19,400 --> 00:07:22,220 terminal methods are and why they're confusing and when 164 00:07:22,220 --> 00:07:24,889 they happen. We'll talk about, of course, the meat 165 00:07:24,889 --> 00:07:27,740 of SQL. So select, where, join, and this special 166 00:07:27,740 --> 00:07:31,400 thing called join association, and order. Group is, is 167 00:07:31,400 --> 00:07:34,039 way easy so I'm not gonna cover that. And, 168 00:07:34,039 --> 00:07:35,789 or, greater than, less than - all these great 169 00:07:35,789 --> 00:07:37,800 things you can do with Arel that you can't 170 00:07:37,800 --> 00:07:40,330 necessarily find online. And then we'll also talk about 171 00:07:40,330 --> 00:07:43,310 the match, which is executed SQL-like, and in, which 172 00:07:43,310 --> 00:07:45,699 allows for sub-queries. 173 00:07:45,699 --> 00:07:49,410 OK. So what is ActiveRecord exactly? Well, you all 174 00:07:49,410 --> 00:07:53,479 know this, but it bears repeating here. ActiveRecord is 175 00:07:53,479 --> 00:07:56,110 a database abstraction. So there's no need to speak 176 00:07:56,110 --> 00:07:58,330 a dialect of SQL. It connects to queries, it 177 00:07:58,330 --> 00:08:00,449 returns data back to you. And it also functions 178 00:08:00,449 --> 00:08:02,710 as a persistence layer, you know. Database rows are 179 00:08:02,710 --> 00:08:04,800 Ruby objects. You can pull them out, you can 180 00:08:04,800 --> 00:08:08,199 put them back in. And they also encapsulate, in 181 00:08:08,199 --> 00:08:09,800 many cases, your domain logic. And I know people 182 00:08:09,800 --> 00:08:12,669 say no fat models, no fat controllers, but the 183 00:08:12,669 --> 00:08:15,009 truth is that a lot of domain logic gets 184 00:08:15,009 --> 00:08:16,300 shoved into those models. 185 00:08:16,300 --> 00:08:20,530 They, they contain validations, they contain all that good 186 00:08:20,530 --> 00:08:24,080 stuff. And they also define associations between your models. 187 00:08:24,080 --> 00:08:26,870 So, Arel is distinct in that all that it's 188 00:08:26,870 --> 00:08:31,789 really used for is building queries. So it's described, 189 00:08:31,789 --> 00:08:35,809 on its GitHub page, as a relational algebra for 190 00:08:35,809 --> 00:08:38,309 Ruby. And that didn't make any sense to me 191 00:08:38,309 --> 00:08:40,789 because, it didn't until I started reading more, because 192 00:08:40,789 --> 00:08:43,299 relational algebra, those two words really don't, I don't 193 00:08:43,299 --> 00:08:45,790 know how those work put together, but that's how 194 00:08:45,790 --> 00:08:46,990 they describe it. 195 00:08:46,990 --> 00:08:48,069 Essentially what that means is that it just builds 196 00:08:48,069 --> 00:08:50,880 SQL queries in an object-oriented way. It generates abstract 197 00:08:50,880 --> 00:08:54,330 syntax trees, or ASTs for your queries, and then 198 00:08:54,330 --> 00:08:56,529 uses the visitor pattern to put those out to 199 00:08:56,529 --> 00:08:58,550 SQL strings and then hands those off to ActiveRecord 200 00:08:58,550 --> 00:09:01,700 to execute. It also enables chaining, as we talked 201 00:09:01,700 --> 00:09:04,890 about, because ASTs are kind of nicely composable, because 202 00:09:04,890 --> 00:09:06,670 all you have to do is reference a subtree 203 00:09:06,670 --> 00:09:08,760 in order to pull in, you know, some other 204 00:09:08,760 --> 00:09:10,390 branch of query logic. 205 00:09:10,390 --> 00:09:15,160 So, one blogger also described Arel as vexingly undocumented. 206 00:09:15,160 --> 00:09:16,950 So you can look at find pieces of Arel 207 00:09:16,950 --> 00:09:19,950 all over the place, but joining that all together 208 00:09:19,950 --> 00:09:22,649 is a, something that took me a long time. 209 00:09:22,649 --> 00:09:25,740 In fact, this talk came out of a project 210 00:09:25,740 --> 00:09:27,020 that I was working on at Twitter. I work 211 00:09:27,020 --> 00:09:29,649 on the Twitter translation center. We needed to implement 212 00:09:29,649 --> 00:09:32,980 an access control system. And to do that, we 213 00:09:32,980 --> 00:09:35,050 had permissions and we had groups and all that 214 00:09:35,050 --> 00:09:37,399 kind of thing, and in order to join all 215 00:09:37,399 --> 00:09:39,510 that logic together and decide whether a user was 216 00:09:39,510 --> 00:09:42,000 able to see a particular phrase, it turned out 217 00:09:42,000 --> 00:09:44,149 that that was a difficult thing to do. We 218 00:09:44,149 --> 00:09:45,360 would have had to do a lot of, a 219 00:09:45,360 --> 00:09:48,440 lot of, you know, strings in our SQL queries. 220 00:09:48,440 --> 00:09:50,020 And we wanted to avoid that. We wanted to 221 00:09:50,020 --> 00:09:51,760 be able to be composable and use scoping and 222 00:09:51,760 --> 00:09:53,570 all that great stuff. 223 00:09:53,570 --> 00:09:55,589 So, having done that project, I'm trying to, and 224 00:09:55,589 --> 00:09:57,880 I'm taking that knowledge and kind of imparting it 225 00:09:57,880 --> 00:09:59,890 to you. So that project was a, a direct 226 00:09:59,890 --> 00:10:02,510 reason why this talk was created. I find it 227 00:10:02,510 --> 00:10:06,100 very difficult to find the information I was looking 228 00:10:06,100 --> 00:10:06,740 for. 229 00:10:06,740 --> 00:10:08,550 So, as a, as a recap here, you know, 230 00:10:08,550 --> 00:10:11,330 Arel knows nothing about your models. It knows very 231 00:10:11,330 --> 00:10:14,060 little about your database. All that it knows is 232 00:10:14,060 --> 00:10:17,820 that you have these queries you're trying to construct. 233 00:10:17,820 --> 00:10:20,040 It doesn't care if those tables exist. It doesn't 234 00:10:20,040 --> 00:10:22,730 care if those columns exist. ActiveRecord cares. But Arel 235 00:10:22,730 --> 00:10:25,500 really doesn't care. And it does not store or 236 00:10:25,500 --> 00:10:28,589 retrieve data. All of these are ActiveRecord's responsibilities. So, 237 00:10:28,589 --> 00:10:32,550 in summary here, Arel constructs queries and ActiveRecord does 238 00:10:32,550 --> 00:10:33,220 everything else. 239 00:10:33,220 --> 00:10:35,790 If you were to look at this as a 240 00:10:35,790 --> 00:10:38,880 hierarchy, and kind of a diagram I guess, you've 241 00:10:38,880 --> 00:10:42,620 got ActiveRecord, which you would interact with as a 242 00:10:42,620 --> 00:10:45,940 developer. ActiveRecord calls out to Arel to, to process 243 00:10:45,940 --> 00:10:48,330 a query, form a query, and then sends that 244 00:10:48,330 --> 00:10:50,370 query to the database, and the data comes back 245 00:10:50,370 --> 00:10:52,740 and ActiveRecord will package that into nice Ruby objects 246 00:10:52,740 --> 00:10:53,720 for you. 247 00:10:53,720 --> 00:10:57,670 So, something also that bears some introduction here is 248 00:10:57,670 --> 00:11:02,510 what an AST is, because as DHH so eloquently 249 00:11:02,510 --> 00:11:05,630 put it in his keynote this morning, not everybody 250 00:11:05,630 --> 00:11:07,839 comes from a computer science background. And ASTs are 251 00:11:07,839 --> 00:11:10,430 very computer science-y. So, an AST essentially is, or 252 00:11:10,430 --> 00:11:12,060 really what a tree, in this case, this is 253 00:11:12,060 --> 00:11:13,930 a basic tree, this is actually a binary tree, 254 00:11:13,930 --> 00:11:15,850 and what this means is that, so, it's made 255 00:11:15,850 --> 00:11:18,610 up of nodes. Each node has left and right 256 00:11:18,610 --> 00:11:20,660 children, unless it's the bottom. It's called a leaf, 257 00:11:20,660 --> 00:11:22,670 or it's called a, yeah, a leaf node. 258 00:11:22,670 --> 00:11:24,390 So we have left and right children. In this 259 00:11:24,390 --> 00:11:26,260 case, it's a binary tree because you have two 260 00:11:26,260 --> 00:11:27,540 children. You could have an n tree which could 261 00:11:27,540 --> 00:11:31,120 have multiple children. So, why are trees important? Why 262 00:11:31,120 --> 00:11:33,019 are they cool? Well, a tree is cool because 263 00:11:33,019 --> 00:11:36,459 you can represent things like equations with them or, 264 00:11:36,459 --> 00:11:40,230 or expressions. So five times parenthesis six plus three, 265 00:11:40,230 --> 00:11:43,640 you can represent this in a tree by defining 266 00:11:43,640 --> 00:11:45,380 a node at the root here, which is multiplication. 267 00:11:45,380 --> 00:11:48,450 That's an operator. And then each left and right 268 00:11:48,450 --> 00:11:52,630 child represents the two different operands of that expressions, 269 00:11:52,630 --> 00:11:55,019 of that, of that operator. So five times six 270 00:11:55,019 --> 00:11:55,490 plus three. 271 00:11:55,490 --> 00:11:58,370 So the six plus three would be evaluated first, 272 00:11:58,370 --> 00:12:00,279 because it's in parenthesis. So, notice also the parenthesis 273 00:12:00,279 --> 00:12:03,050 are grouping this expression together. And then five times 274 00:12:03,050 --> 00:12:06,860 that. You can kind of expand this concept into 275 00:12:06,860 --> 00:12:09,660 how this would look for a SQL expression, by 276 00:12:09,660 --> 00:12:11,420 looking at this example. So, you might have a 277 00:12:11,420 --> 00:12:14,170 query root and then a select from, and select 278 00:12:14,170 --> 00:12:17,760 has two columns, id and text, and from has 279 00:12:17,760 --> 00:12:19,529 just one child post, posts. And this would be 280 00:12:19,529 --> 00:12:20,040 an entry. 281 00:12:20,040 --> 00:12:21,589 Now, I don't actually know if this is how 282 00:12:21,589 --> 00:12:23,260 Arel does this in the background. This is a 283 00:12:23,260 --> 00:12:25,700 representation that you could imagine is going on behind 284 00:12:25,700 --> 00:12:26,899 the scenes. 285 00:12:26,899 --> 00:12:29,260 OK. So let's get to some code. And, and 286 00:12:29,260 --> 00:12:30,589 the first thing I want to talk about is 287 00:12:30,589 --> 00:12:32,700 a little gem that I put together to kind 288 00:12:32,700 --> 00:12:34,680 of help with this talk and do some Arel 289 00:12:34,680 --> 00:12:36,800 things that just take kind of some of the 290 00:12:36,800 --> 00:12:39,910 verbosity out of Arel. So it's called arel-helpers. Go 291 00:12:39,910 --> 00:12:42,110 ahead and install this thing. It's got three kind 292 00:12:42,110 --> 00:12:43,610 of helpers. Very small. 293 00:12:43,610 --> 00:12:46,720 And let's also, so, let's jump on from there 294 00:12:46,720 --> 00:12:49,089 and we'll talk about how to reference tables and 295 00:12:49,089 --> 00:12:52,910 columns in Arel. So, let's pretend you have this 296 00:12:52,910 --> 00:12:56,850 table. It's called post. It has many comments. And 297 00:12:56,850 --> 00:12:59,100 let's say you wanted to reference the id in 298 00:12:59,100 --> 00:13:04,050 this table. Well, post dot arel_table(:id). So any class 299 00:13:04,050 --> 00:13:06,990 that inherits from ActiveRecord::Base has this method called Arel 300 00:13:06,990 --> 00:13:09,550 table. And it returns to you an instance of 301 00:13:09,550 --> 00:13:12,510 Arel colon colon table that was a, is a 302 00:13:12,510 --> 00:13:15,490 way of referencing your table in code. 303 00:13:15,490 --> 00:13:17,430 Now brackets :id of course gives me what's called 304 00:13:17,430 --> 00:13:18,579 an Arel attribute, and I can look at, I 305 00:13:18,579 --> 00:13:20,760 can do the same thing for text, and I 306 00:13:20,760 --> 00:13:23,640 can even do this for columns that don't exist. 307 00:13:23,640 --> 00:13:25,180 So if I wanted to derive a column, I 308 00:13:25,180 --> 00:13:27,320 could also use this to reference that column. Now, 309 00:13:27,320 --> 00:13:30,570 that's not always true. But that's almost always true. 310 00:13:30,570 --> 00:13:31,779 So this returns an Arel::Attributes::Attribute. 311 00:13:31,779 --> 00:13:34,570 Now you don't really care what it returns. You 312 00:13:34,570 --> 00:13:35,700 just want, you care that you can use it 313 00:13:35,700 --> 00:13:39,480 in your code. So, the arel-helpers gem kind of 314 00:13:39,480 --> 00:13:43,100 makes this a little easier. If you include ArelHelpers::ArelTable 315 00:13:43,100 --> 00:13:46,630 in your model, you can now reference columns off 316 00:13:46,630 --> 00:13:49,709 your tables with just square brackets and eliminate arel_table, 317 00:13:49,709 --> 00:13:52,480 it just delegates down to arel_table. And it still 318 00:13:52,480 --> 00:13:54,890 returns an attribute to you. 319 00:13:54,890 --> 00:13:57,300 All right. So kind of going off of that, 320 00:13:57,300 --> 00:14:00,060 what does this statement return? It's a little pop 321 00:14:00,060 --> 00:14:04,139 quiz. Does it return "Rails is Cool." Just an 322 00:14:04,139 --> 00:14:08,589 array of strings. Does it return an array of 323 00:14:08,589 --> 00:14:13,459 instantiated objects that are records, ActiveRecords? Or does it 324 00:14:13,459 --> 00:14:16,110 return an ActiveRecord::Relation? You guys might know the answer 325 00:14:16,110 --> 00:14:19,139 to this. Who thinks it's A? Who thinks it's 326 00:14:19,139 --> 00:14:22,860 B? OK. And who thinks it's C? Nice. OK. 327 00:14:22,860 --> 00:14:25,600 It is, in fact, C. 328 00:14:25,600 --> 00:14:28,860 One thing that's very cool about Rails 3 and 329 00:14:28,860 --> 00:14:31,820 Rails 4, ActiveRecord 3 and ActiveRecord 4, is that 330 00:14:31,820 --> 00:14:34,760 any of these methods, these database methods, like select, 331 00:14:34,760 --> 00:14:37,610 join, where, they actually return an ActiveRecord::Relation. They don't 332 00:14:37,610 --> 00:14:41,329 actually execute right away. So, that means that they 333 00:14:41,329 --> 00:14:43,519 are also chainable. So if I said post dot 334 00:14:43,519 --> 00:14:47,339 select title, and I assigned that the query, that's 335 00:14:47,339 --> 00:14:51,279 not actually executing anything. That's storing this intermediate AST 336 00:14:51,279 --> 00:14:53,290 in the variable called query. Which means that I 337 00:14:53,290 --> 00:14:55,680 can also say query equals query dot select another 338 00:14:55,680 --> 00:14:57,660 column name, and then if I were to say 339 00:14:57,660 --> 00:15:00,829 query dot to_sql, I would get out the string 340 00:15:00,829 --> 00:15:03,050 the ActiveRecord will send to the database to grab 341 00:15:03,050 --> 00:15:04,079 data from. 342 00:15:04,079 --> 00:15:07,160 So, it's also important to notice here, I've kind 343 00:15:07,160 --> 00:15:10,540 of introduced this to_sql method. Any ActiveRecord relation that 344 00:15:10,540 --> 00:15:12,200 you have, you can call to_sql on it and 345 00:15:12,200 --> 00:15:13,589 it will give you the string that it would 346 00:15:13,589 --> 00:15:17,050 execute against the database. So that's very handy. And 347 00:15:17,050 --> 00:15:18,839 I'll use that throughout the whole presentation. 348 00:15:18,839 --> 00:15:21,660 Yeah. So relationships can be changed. 349 00:15:21,660 --> 00:15:23,310 All right. So let's get into some of the 350 00:15:23,310 --> 00:15:25,010 specifics here. So let's talk about select. We talked, 351 00:15:25,010 --> 00:15:28,620 we looked at select a little bit. You can 352 00:15:28,620 --> 00:15:32,130 actually select multiple columns using array syntax, so this 353 00:15:32,130 --> 00:15:34,040 is actually just ActiveRecord at this point, we're not 354 00:15:34,040 --> 00:15:36,600 even diving into Arel right now. And you might 355 00:15:36,600 --> 00:15:38,570 think, OK, so we're using this to_sql again. We 356 00:15:38,570 --> 00:15:41,290 get :id, :text from Post. Everything's great. 357 00:15:41,290 --> 00:15:42,519 Let's say that we wanted to get the SQL, 358 00:15:42,519 --> 00:15:44,350 though, for count. So we just say give me 359 00:15:44,350 --> 00:15:47,089 all of the Posts, the counts here, and you, 360 00:15:47,089 --> 00:15:49,500 you probably wouldn't say select(:id) and then count, but, 361 00:15:49,500 --> 00:15:52,110 you know, it's an example, so. And instead of 362 00:15:52,110 --> 00:15:54,510 getting back a SQL string, you instead get back 363 00:15:54,510 --> 00:15:56,970 a NoMethodError. 364 00:15:56,970 --> 00:15:58,880 So why did that happen? Well, it turns out 365 00:15:58,880 --> 00:16:03,820 that count is a terminal method. So, let's dive 366 00:16:03,820 --> 00:16:09,110 into why that happened. So. Being a terminal method, 367 00:16:09,110 --> 00:16:10,810 it would, it's executing immediately and returning a result 368 00:16:10,810 --> 00:16:12,630 to you instead of giving you an ActiveRecord relation, 369 00:16:12,630 --> 00:16:15,220 so that's why, it's good to distinguish between, and 370 00:16:15,220 --> 00:16:16,360 know which ones are going to do this to 371 00:16:16,360 --> 00:16:18,040 you, right. 372 00:16:18,040 --> 00:16:21,220 The way to actually get a count, and get 373 00:16:21,220 --> 00:16:22,930 a SQL string back out for it, would be 374 00:16:22,930 --> 00:16:24,459 to kind of dive into Arel here. Use your 375 00:16:24,459 --> 00:16:27,510 Arel table knowledge from before and call count on 376 00:16:27,510 --> 00:16:29,800 an Arel attribute. And then, of course, you can 377 00:16:29,800 --> 00:16:32,209 pass that to select as an array. Pass text 378 00:16:32,209 --> 00:16:34,519 as a symbol. And out comes this count, like 379 00:16:34,519 --> 00:16:35,399 you're expecting. 380 00:16:35,399 --> 00:16:37,399 So let's look at what terminal methods are. So, 381 00:16:37,399 --> 00:16:40,399 terminal methods execute the query immediately. They do not 382 00:16:40,399 --> 00:16:44,269 return a relation. They're mostly count, first, and last, 383 00:16:44,269 --> 00:16:45,630 so first and last are kind of like array 384 00:16:45,630 --> 00:16:49,220 accessors, right. To_a, pluck, and any of the enumerable 385 00:16:49,220 --> 00:16:53,750 methods will all execute your queries immediately. So, for 386 00:16:53,750 --> 00:16:56,760 example, this, each over these objects, and you would 387 00:16:56,760 --> 00:16:58,420 expect this to happen, because this is not a 388 00:16:58,420 --> 00:17:00,740 lazy enumerator. This is a regular enumerator and you're 389 00:17:00,740 --> 00:17:02,110 iterating over all of these things and you're putting 390 00:17:02,110 --> 00:17:04,049 out the text. And that's obviously going to execute 391 00:17:04,049 --> 00:17:04,569 right away. 392 00:17:04,569 --> 00:17:06,888 But, more surprising is this one. So, if I 393 00:17:06,888 --> 00:17:08,648 say each slice here and it's a lazy enumerator, 394 00:17:08,648 --> 00:17:10,159 in other words, I don't pass a block to 395 00:17:10,159 --> 00:17:14,839 it, this will also execute immediately. So just beware 396 00:17:14,839 --> 00:17:16,079 of that. 397 00:17:16,079 --> 00:17:19,549 All right. So let's go actually for real into 398 00:17:19,549 --> 00:17:22,470 select here now. So we've already seen how we 399 00:17:22,470 --> 00:17:24,929 can say dot count on an attribute, on an 400 00:17:24,929 --> 00:17:27,309 Arel attribute. We can also say dot sum, and 401 00:17:27,309 --> 00:17:29,450 that will give us this sum method. This is, 402 00:17:29,450 --> 00:17:32,320 these are all, by the way, MySQL examples. Notice, 403 00:17:32,320 --> 00:17:36,279 also, that Arel and ActiveRecord have nicely added this 404 00:17:36,279 --> 00:17:40,619 AS sum_id for you. So, any table, any, any 405 00:17:40,619 --> 00:17:43,169 column like this will automatically get a derived column 406 00:17:43,169 --> 00:17:44,669 name as well. 407 00:17:44,669 --> 00:17:46,649 You can change that column name by adding an 408 00:17:46,649 --> 00:17:49,059 as here. So just chain as right on the 409 00:17:49,059 --> 00:17:51,970 end of sum. Post visitors sum as visitor_total, and 410 00:17:51,970 --> 00:17:53,919 that will give it kind of a custom derived 411 00:17:53,919 --> 00:17:56,789 column name for you. You can also say dot 412 00:17:56,789 --> 00:18:00,190 maximum and dot minimum, and these will all use 413 00:18:00,190 --> 00:18:05,019 kind of the built-in SQL methods that you're expecting. 414 00:18:05,019 --> 00:18:07,309 Let's say, though, that you're trying to, you know, 415 00:18:07,309 --> 00:18:09,899 run a function that isn't part of Arel's DSL 416 00:18:09,899 --> 00:18:12,450 or Arel's knowledge, I guess. It's kind of outside 417 00:18:12,450 --> 00:18:16,899 of Arel's abilities. You can actually define a function 418 00:18:16,899 --> 00:18:19,830 call that, of any arbitrary function. This covers things 419 00:18:19,830 --> 00:18:22,249 like length or, like, any of the other methods 420 00:18:22,249 --> 00:18:24,869 that MySQL provides, or PostGres, that may differ from 421 00:18:24,869 --> 00:18:27,840 database to database. So, in this case, I am 422 00:18:27,840 --> 00:18:30,059 saying a named function dot new, passing in the 423 00:18:30,059 --> 00:18:31,600 name of the function and then giving it an 424 00:18:31,600 --> 00:18:33,200 array of arguments that function takes. 425 00:18:33,200 --> 00:18:36,549 And, then again, I'm also aliasing the result of 426 00:18:36,549 --> 00:18:37,899 that as length, in this case. So giving it 427 00:18:37,899 --> 00:18:40,690 a derived column name. 428 00:18:40,690 --> 00:18:43,979 So, we can shorten this a little bit by, 429 00:18:43,979 --> 00:18:46,279 cause it's a little bit verbose, by including Arel::Nodes. 430 00:18:46,279 --> 00:18:48,529 And then this just becomes NamedFunction dot new. So 431 00:18:48,529 --> 00:18:50,509 that's kind of a little hack you can add 432 00:18:50,509 --> 00:18:53,259 to your queries. 433 00:18:53,259 --> 00:18:54,679 Something kind of also pretty cool about Arel, this 434 00:18:54,679 --> 00:18:56,440 is kind of one of the other little cool 435 00:18:56,440 --> 00:18:58,799 select things, you can also say Arel dot star 436 00:18:58,799 --> 00:19:01,700 here, if you wanted to select every single record, 437 00:19:01,700 --> 00:19:03,580 right, so, or every single column from the record. 438 00:19:03,580 --> 00:19:06,169 So select start from post. Arel dot star exists, 439 00:19:06,169 --> 00:19:08,059 you can use it in a bunch of different 440 00:19:08,059 --> 00:19:09,049 places. 441 00:19:09,049 --> 00:19:13,299 OK. So, many people also don't know that select 442 00:19:13,299 --> 00:19:15,769 and Arel and ActiveRecord, I should say, have the 443 00:19:15,769 --> 00:19:17,210 ability to also just specify a from. So you 444 00:19:17,210 --> 00:19:20,559 want to specify a, a sub-query inside a from. 445 00:19:20,559 --> 00:19:21,940 This is pretty common. You can do this instead 446 00:19:21,940 --> 00:19:23,499 of a join or instead of a, a more 447 00:19:23,499 --> 00:19:25,899 complicated query here. So I'm saying select ID, and 448 00:19:25,899 --> 00:19:29,229 then I'm passing a sub-query into the from. 449 00:19:29,229 --> 00:19:30,710 So one thing that's really important to notice here 450 00:19:30,710 --> 00:19:33,070 is you'll notice that the end of the sele, 451 00:19:33,070 --> 00:19:36,509 the second select, I say dot AST. So also 452 00:19:36,509 --> 00:19:39,379 at any point in your construction, you can call 453 00:19:39,379 --> 00:19:40,909 AST on your relation, and it will give you 454 00:19:40,909 --> 00:19:43,639 back this massive tree that it's constructed. And in 455 00:19:43,639 --> 00:19:46,429 this case, from requires you to pass it an 456 00:19:46,429 --> 00:19:48,200 AST otherwise it gets confused. It doesn't know what 457 00:19:48,200 --> 00:19:49,470 to do with a relation. It only knows what 458 00:19:49,470 --> 00:19:51,279 to do with it if it's an AST or 459 00:19:51,279 --> 00:19:54,340 the column or things like that. So. Or a 460 00:19:54,340 --> 00:19:55,609 table, in this case. 461 00:19:55,609 --> 00:19:58,429 All right. So that pretty much covers select. There's 462 00:19:58,429 --> 00:19:59,600 a lot of things you can do with select. 463 00:19:59,600 --> 00:20:01,289 I was kind of breezing past a lot of 464 00:20:01,289 --> 00:20:02,129 it. If you have any questions, you can always 465 00:20:02,129 --> 00:20:04,559 talk to me or, or you know, I guess, 466 00:20:04,559 --> 00:20:07,570 go online. But there's not that many resources. Anyway. 467 00:20:07,570 --> 00:20:08,399 OK. So, the wonder of where. 468 00:20:08,399 --> 00:20:12,940 So, where is also gotten a lot better since 469 00:20:12,940 --> 00:20:16,389 Rails 2. I can pass in a title to 470 00:20:16,389 --> 00:20:18,799 my where here, where post dot where title is 471 00:20:18,799 --> 00:20:21,139 "Arel is Cool," and this will do what you 472 00:20:21,139 --> 00:20:23,849 expect. It will say, it'll construct the SQL query, 473 00:20:23,849 --> 00:20:25,720 user dot title equals this value. So no question 474 00:20:25,720 --> 00:20:27,539 marks here, which is very nice. 475 00:20:27,539 --> 00:20:30,049 But what happens if you want to, you know, 476 00:20:30,049 --> 00:20:31,749 do something a little more. Oh, here's the, here's 477 00:20:31,749 --> 00:20:33,279 the Arel version of this. So, you can do 478 00:20:33,279 --> 00:20:34,779 this with pure Arel. It's a little more verbose. 479 00:20:34,779 --> 00:20:36,919 You wouldn't really do this. But let's say you 480 00:20:36,919 --> 00:20:39,470 wanted to say not equal to or less than, 481 00:20:39,470 --> 00:20:41,299 things like that. You can do the same thing. 482 00:20:41,299 --> 00:20:43,869 So you can say not equal in this case, 483 00:20:43,869 --> 00:20:45,669 and notice that it'll put the band equals in 484 00:20:45,669 --> 00:20:48,849 MySQL. You can also say, kind of something cool 485 00:20:48,849 --> 00:20:51,299 here, not equal to nil, and it will serialize 486 00:20:51,299 --> 00:20:53,950 that as is not nil, which is what your, 487 00:20:53,950 --> 00:20:55,899 what you should be expecting. 488 00:20:55,899 --> 00:20:59,529 Some of the cool methods. Greater than, GT. Less 489 00:20:59,529 --> 00:21:02,479 than. LT. All makes sense. And all of these 490 00:21:02,479 --> 00:21:04,999 are just chained right off of this, this, this 491 00:21:04,999 --> 00:21:08,479 column attribute here. Here's greater than equal to - 492 00:21:08,479 --> 00:21:12,749 GTEQ, and also LTEQ. 493 00:21:12,749 --> 00:21:17,679 So, we also, oftentimes, in our queries, need to 494 00:21:17,679 --> 00:21:20,659 be able to and and or our way to 495 00:21:20,659 --> 00:21:23,119 a kind of a multiple where. We can do 496 00:21:23,119 --> 00:21:25,139 the same thing here. So, if you were to 497 00:21:25,139 --> 00:21:28,479 say where multiple times just with ActiveRecord, it would 498 00:21:28,479 --> 00:21:30,049 automatically give you an and. But if you wanted 499 00:21:30,049 --> 00:21:31,999 to specify an or, you would need to do 500 00:21:31,999 --> 00:21:34,830 something like this. So we have our title and 501 00:21:34,830 --> 00:21:37,529 attribute from post, we're saying equal "Arel is Cool", 502 00:21:37,529 --> 00:21:39,580 and the post id is equal to twenty-two or 503 00:21:39,580 --> 00:21:42,580 twenty-three. And notice that I have actually injected, so 504 00:21:42,580 --> 00:21:45,379 I have put, inside the and, I put another 505 00:21:45,379 --> 00:21:47,729 attribute and a dot or, and this will, Arel 506 00:21:47,729 --> 00:21:49,519 will just know, it will, the visitor pattern will 507 00:21:49,519 --> 00:21:52,070 know how to add the right parenthesis to this 508 00:21:52,070 --> 00:21:52,279 query. 509 00:21:52,279 --> 00:21:54,289 So, notice the output query has an and and 510 00:21:54,289 --> 00:21:57,789 then parenthesis and the or, so that no operator 511 00:21:57,789 --> 00:22:02,629 precedence messes us up here. Now, notice that I 512 00:22:02,629 --> 00:22:05,849 said equals twenty-two or twenty-three. I could also have 513 00:22:05,849 --> 00:22:10,139 put an in here. So in also accepts arguments 514 00:22:10,139 --> 00:22:13,229 here that could, essentially an array of values here. 515 00:22:13,229 --> 00:22:14,759 So it's the same thing, this will have the 516 00:22:14,759 --> 00:22:16,940 same effect. 517 00:22:16,940 --> 00:22:19,289 You can also compose your wheres. You can add 518 00:22:19,289 --> 00:22:21,440 named functions in here. So you can also do 519 00:22:21,440 --> 00:22:23,739 dot count, dot sum, all that stuff works. Those 520 00:22:23,739 --> 00:22:26,919 are aggregate functions. Here's length. Oh, I'm aggregating this 521 00:22:26,919 --> 00:22:28,210 stuff together and it all, it all just kind 522 00:22:28,210 --> 00:22:30,249 of works. It's pretty cool. 523 00:22:30,249 --> 00:22:33,320 All right. So let's get to my favorite part 524 00:22:33,320 --> 00:22:37,169 of Arel, and that's joins. So ActiveRecord makes joins 525 00:22:37,169 --> 00:22:39,669 pretty easy, except for when you're trying to do 526 00:22:39,669 --> 00:22:41,499 an outer join, and we'll just, we'll just see 527 00:22:41,499 --> 00:22:43,190 how that looks in a second here. 528 00:22:43,190 --> 00:22:45,330 So let's pretend we have this data model. It's 529 00:22:45,330 --> 00:22:48,789 a basic blog, essentially, so you've got a post. 530 00:22:48,789 --> 00:22:54,289 Post has_many comments. And each comment has an author. 531 00:22:54,289 --> 00:22:55,830 So let's, let's see what happens when we dive 532 00:22:55,830 --> 00:22:57,359 into some joins here. So we say join. This 533 00:22:57,359 --> 00:23:00,509 is without any help from Arel. Joins comments, or 534 00:23:00,509 --> 00:23:02,590 comment in this case, where id is forty-two. 535 00:23:02,590 --> 00:23:06,700 So, very cool. ActiveRecord inspects this model. It inspects 536 00:23:06,700 --> 00:23:10,549 the association between author and comment. And automatically generates 537 00:23:10,549 --> 00:23:15,549 the right join conditions for you. Pretty cool. You 538 00:23:15,549 --> 00:23:17,049 can also specify, as I mentioned, kind of a, 539 00:23:17,049 --> 00:23:18,779 a through table in this case. So I want 540 00:23:18,779 --> 00:23:20,919 to join comments, and then I also want, you 541 00:23:20,919 --> 00:23:22,899 know, using author, I've got comments, and I also 542 00:23:22,899 --> 00:23:24,729 want to find the posts of the comment reference 543 00:23:24,729 --> 00:23:27,200 or that, that comment is part of. 544 00:23:27,200 --> 00:23:29,599 So, this will also do what you expect. It 545 00:23:29,599 --> 00:23:32,919 will grab all the correct attributes for the models. 546 00:23:32,919 --> 00:23:36,039 Hook them all together. A lot of cool introspection. 547 00:23:36,039 --> 00:23:37,979 But, again, what about outer joins? So, you notice 548 00:23:37,979 --> 00:23:40,879 back here, it's inter-joined. And there's really no way 549 00:23:40,879 --> 00:23:43,169 to specify an outer join without diving in to 550 00:23:43,169 --> 00:23:46,159 Arel. At least that I'm aware of. 551 00:23:46,159 --> 00:23:48,409 OK. So let's look at this guy again. So 552 00:23:48,409 --> 00:23:49,950 let's say I wanted to add an outjoin again. 553 00:23:49,950 --> 00:23:54,019 Well, first, it kind of bears mentioning here that 554 00:23:54,019 --> 00:23:57,340 you can turn this stuff into, also, pure Arel 555 00:23:57,340 --> 00:24:00,359 stuff. So, we have a joins comment. We can 556 00:24:00,359 --> 00:24:02,259 also say comment dot joins post, and then call 557 00:24:02,259 --> 00:24:05,830 this method called join sources. So, join sources is 558 00:24:05,830 --> 00:24:08,090 a way of saying, I have gotten this query 559 00:24:08,090 --> 00:24:10,179 from ActiveRecord. And this is, again, it's just using 560 00:24:10,179 --> 00:24:11,879 ActiveRecord. And I just want to grab the join 561 00:24:11,879 --> 00:24:13,340 sources. I don't care about the select or the 562 00:24:13,340 --> 00:24:15,119 where. Just give me the join sources and pass 563 00:24:15,119 --> 00:24:17,919 that onto whoever is wrapping. In this case, another 564 00:24:17,919 --> 00:24:18,629 query. 565 00:24:18,629 --> 00:24:22,469 You can do the same thing with first join. 566 00:24:22,469 --> 00:24:25,999 And then you can also specify the on conditions 567 00:24:25,999 --> 00:24:28,719 manually here. So, we have a, a join comment 568 00:24:28,719 --> 00:24:32,169 Arel table, and we're joining that on :comment_id is 569 00:24:32,169 --> 00:24:34,440 equal to author :comment_id, and then saying join sources. 570 00:24:34,440 --> 00:24:35,489 And we're doing this for both. 571 00:24:35,489 --> 00:24:36,999 Now, this is gonna get pretty verbose, but at 572 00:24:36,999 --> 00:24:39,159 this point, we can finally add our outer join. 573 00:24:39,159 --> 00:24:43,259 OK. So notice that the attribute, Arel attribute dot 574 00:24:43,259 --> 00:24:45,379 join takes a third argument, or a second argument, 575 00:24:45,379 --> 00:24:46,690 that is the join type. So you could say 576 00:24:46,690 --> 00:24:50,599 inner join here, or outer join. And that results 577 00:24:50,599 --> 00:24:54,309 in, essentially, the same query. But this time an 578 00:24:54,309 --> 00:24:55,049 outer join. 579 00:24:55,049 --> 00:24:58,210 You're thinking, that is a lot of code. Why 580 00:24:58,210 --> 00:25:00,769 would I ever type that much code? And you're 581 00:25:00,769 --> 00:25:03,059 right. That's way too much code. So, part of 582 00:25:03,059 --> 00:25:08,899 the Arel-helpers library is called JoinAssociation. And this will 583 00:25:08,899 --> 00:25:12,039 introspect your model, just like ActiveRecord would do. Hand 584 00:25:12,039 --> 00:25:14,849 those off to what's called a select manager, and 585 00:25:14,849 --> 00:25:17,799 then use the second argument Arel::OuterJoin to give you 586 00:25:17,799 --> 00:25:19,919 an outer join, but without all the tedium of 587 00:25:19,919 --> 00:25:21,609 specifying all these conditions. 588 00:25:21,609 --> 00:25:24,249 So I can slide this in here. JoinAssociation, and 589 00:25:24,249 --> 00:25:27,469 I supply the original model and then the association 590 00:25:27,469 --> 00:25:30,379 name, and then Arel::OuterJoin. And I can do that 591 00:25:30,379 --> 00:25:33,919 for both of these guys. Pretty cool. 592 00:25:33,919 --> 00:25:38,519 But wait, there's more. All right. So you've got 593 00:25:38,519 --> 00:25:40,049 this guy, and you're like, you know, I really 594 00:25:40,049 --> 00:25:46,609 want to supply some extra custom on conditions. Fortunately, 595 00:25:46,609 --> 00:25:48,950 join dependency or join association, rather, will yield a 596 00:25:48,950 --> 00:25:52,259 block to you that has both the association name, 597 00:25:52,259 --> 00:25:55,719 which is a string or symbol, and also the, 598 00:25:55,719 --> 00:25:59,690 a join condition's intermediate query object. So you can 599 00:25:59,690 --> 00:26:02,379 say join conditions and, and then pass in anything 600 00:26:02,379 --> 00:26:04,929 else you would like to construct. In this case, 601 00:26:04,929 --> 00:26:06,499 we're making sure that the created_at date is less 602 00:26:06,499 --> 00:26:10,429 than or equal to yesterday. 603 00:26:10,429 --> 00:26:12,599 So this will, again, in the same query, but 604 00:26:12,599 --> 00:26:14,279 this time. And it would have been an outer 605 00:26:14,279 --> 00:26:16,489 join except that I, I actually took out, for 606 00:26:16,489 --> 00:26:18,999 space reason, I took out the extra OuterJoin call 607 00:26:18,999 --> 00:26:21,049 there. So this, this would return inner join and 608 00:26:21,049 --> 00:26:22,749 then an outer join. 609 00:26:22,749 --> 00:26:25,989 All right, so let's talk about join tables. As 610 00:26:25,989 --> 00:26:29,229 for, for, so far we've been talking about tables 611 00:26:29,229 --> 00:26:33,019 that have defined associations between them, and those associations 612 00:26:33,019 --> 00:26:35,629 are not has_and_belongs_to_many. So I'm trying to kind of 613 00:26:35,629 --> 00:26:37,549 freshen us a little bit and say, let's talk 614 00:26:37,549 --> 00:26:40,259 a table that, or two tables that are associated 615 00:26:40,259 --> 00:26:43,379 but via a join table. So, you might have 616 00:26:43,379 --> 00:26:45,499 a series of courses that are taught by a 617 00:26:45,499 --> 00:26:48,690 series of teachers. Multiple teachers can teach one course, 618 00:26:48,690 --> 00:26:50,739 and courses can have multiple teachers. 619 00:26:50,739 --> 00:26:54,590 So, in this case, you've got three tables. Courses, 620 00:26:54,590 --> 00:26:57,379 teachers, and courses underscore teachers. This is pretty common 621 00:26:57,379 --> 00:27:00,559 in Rails, I think. So, it means you have, 622 00:27:00,559 --> 00:27:02,629 again, the course table. You can refer to that 623 00:27:02,629 --> 00:27:06,499 with course dot arel_table like we saw before. Teacher 624 00:27:06,499 --> 00:27:09,320 table, again, reference that with Teacher dot arel_table. But 625 00:27:09,320 --> 00:27:12,109 then we've got some magic in here for courses_teachers, 626 00:27:12,109 --> 00:27:14,989 because there, there really is no model for this. 627 00:27:14,989 --> 00:27:17,330 There's no constant we can use to reference this 628 00:27:17,330 --> 00:27:19,940 table. So instead, we need to create an object 629 00:27:19,940 --> 00:27:20,879 for it. 630 00:27:20,879 --> 00:27:22,950 So we'll create an Arel::Table. This is doing this 631 00:27:22,950 --> 00:27:25,849 manually. We're giving it the name courses_teachers, and now 632 00:27:25,849 --> 00:27:28,090 we can use this variable ct anywhere we cant 633 00:27:28,090 --> 00:27:30,830 to refer to this table. So, for example, this 634 00:27:30,830 --> 00:27:33,149 query. I want to say joins, and we have, 635 00:27:33,149 --> 00:27:35,889 you know, courses join teachers, and then just for 636 00:27:35,889 --> 00:27:38,539 the purposes of illustration here I've shown, you know, 637 00:27:38,539 --> 00:27:40,529 what you would do in Arel to construct the 638 00:27:40,529 --> 00:27:42,919 join conditions here, so. You know, on this on 639 00:27:42,919 --> 00:27:44,899 this and then dot join sources. 640 00:27:44,899 --> 00:27:47,399 And I didn't show the SQL output of this 641 00:27:47,399 --> 00:27:49,119 but I think it's pretty, pretty clear from the 642 00:27:49,119 --> 00:27:50,109 previous examples. 643 00:27:50,109 --> 00:27:53,179 OK. So that wraps up join. So let's talk 644 00:27:53,179 --> 00:27:56,999 about order. Order is actually the simplest of the 645 00:27:56,999 --> 00:27:59,989 kind of SQL constructs I'll talk about today. You 646 00:27:59,989 --> 00:28:01,499 all know that you can say post dot order 647 00:28:01,499 --> 00:28:03,809 visitors and visitors in an integer in this case, 648 00:28:03,809 --> 00:28:07,269 then dot to_sql. And this will give you a, 649 00:28:07,269 --> 00:28:09,809 a return value of, you know, all of your, 650 00:28:09,809 --> 00:28:12,700 all of your posts ordered by the number of 651 00:28:12,700 --> 00:28:15,119 visitors. You can also, and some people don't know 652 00:28:15,119 --> 00:28:17,619 this, but this also just an ActiveRecord construction here. 653 00:28:17,619 --> 00:28:21,359 Post.order, that should be (:visitors), reverse order. This will 654 00:28:21,359 --> 00:28:25,570 add a descending clause or a descending keyword to 655 00:28:25,570 --> 00:28:26,479 the end of your query. 656 00:28:26,479 --> 00:28:29,320 Now, you can also do this in Arel. You 657 00:28:29,320 --> 00:28:31,659 can say post(:views) descending to SQL here, and this 658 00:28:31,659 --> 00:28:36,059 is essentially the same thing. All right. So let's 659 00:28:36,059 --> 00:28:37,899 talk about sub-queries with in. 660 00:28:37,899 --> 00:28:40,019 So we talked about sub-queries with from. You can 661 00:28:40,019 --> 00:28:41,519 also do sub-queries with in, and you can use 662 00:28:41,519 --> 00:28:43,539 that AST that we talked about before. SO here's 663 00:28:43,539 --> 00:28:45,609 an example of that. This is a very trivial 664 00:28:45,609 --> 00:28:47,899 example, but we say post dot where and then 665 00:28:47,899 --> 00:28:50,830 we say Post.arel_table in and we supply it another 666 00:28:50,830 --> 00:28:53,279 query. So we're adding a sub-query into this in 667 00:28:53,279 --> 00:28:54,149 call. 668 00:28:54,149 --> 00:28:56,960 So that's pretty cool. And it does the right 669 00:28:56,960 --> 00:28:59,809 thing. It adds the correct parenthesis and everything's great. 670 00:28:59,809 --> 00:29:02,609 All right. And then like queries with matches, you 671 00:29:02,609 --> 00:29:04,489 can also do this with Arel. So most of 672 00:29:04,489 --> 00:29:06,019 the time you'd probably have to say, well, I'd 673 00:29:06,019 --> 00:29:07,330 like to, if I wanted to do a like, 674 00:29:07,330 --> 00:29:09,409 in other words match, kind of fuzzy match against 675 00:29:09,409 --> 00:29:11,599 a column in your database, you would need to 676 00:29:11,599 --> 00:29:13,830 add a like or, I guess, what is it 677 00:29:13,830 --> 00:29:17,499 in PostGres? Like. What is it, double ilike or 678 00:29:17,499 --> 00:29:19,769 something. Anyway. So it's one of those things. 679 00:29:19,769 --> 00:29:21,409 So and then usually you have to add wild 680 00:29:21,409 --> 00:29:23,969 card characters to this, and this would normally be 681 00:29:23,969 --> 00:29:26,859 a big string in your query. But, you can 682 00:29:26,859 --> 00:29:28,989 fix that with Arel. So, Post dot where, and 683 00:29:28,989 --> 00:29:30,820 we have a matches Arel and then I just 684 00:29:30,820 --> 00:29:31,539 put my - so you do have to add 685 00:29:31,539 --> 00:29:34,339 these. Unfortunately you have to add these, these wild 686 00:29:34,339 --> 00:29:37,859 card characters, but it's just dot matches. Super easy. 687 00:29:37,859 --> 00:29:40,539 And that gets serialized out into a like. 688 00:29:40,539 --> 00:29:42,450 And, interestingly enough, I didn't know this when I 689 00:29:42,450 --> 00:29:44,979 started out, but it looks like it's actually taking 690 00:29:44,979 --> 00:29:50,080 that string and encoding it into Base, Base16. Hexadecimal. 691 00:29:50,080 --> 00:29:54,029 All right. So we've learned all this awesome stuff 692 00:29:54,029 --> 00:29:56,159 about Arel. We know that it can do joins 693 00:29:56,159 --> 00:29:58,599 and wheres and likes and ins and all kinds 694 00:29:58,599 --> 00:30:01,219 of awesome stuff, all without the use of, well, 695 00:30:01,219 --> 00:30:04,349 mostly without the use of strings. We also learned 696 00:30:04,349 --> 00:30:07,409 that it's pretty chainable. So, when you think about 697 00:30:07,409 --> 00:30:09,359 something that's chainable, the thing that comes to mind, 698 00:30:09,359 --> 00:30:11,759 for me at least, almost immediately, is the builder 699 00:30:11,759 --> 00:30:12,839 pattern. 700 00:30:12,839 --> 00:30:14,409 And I think, well, why would I want to 701 00:30:14,409 --> 00:30:17,099 construct a huge query inside my model or controller? 702 00:30:17,099 --> 00:30:20,009 I'd much rather have an object do this for 703 00:30:20,009 --> 00:30:22,109 me. Now, is that always the right choice? No, 704 00:30:22,109 --> 00:30:23,649 of course not. But it's, it can be nice 705 00:30:23,649 --> 00:30:27,210 to encapsulate logic, like building a huge query. So 706 00:30:27,210 --> 00:30:28,940 that's why the next step, or the next point 707 00:30:28,940 --> 00:30:31,690 I'm gonna bring up, is how to construct a 708 00:30:31,690 --> 00:30:33,609 query builder. And this is also part of the 709 00:30:33,609 --> 00:30:36,159 ActiveRecord, or the arel-helpers gem that I mentioned before. 710 00:30:36,159 --> 00:30:39,009 So this is pretty much the entire implementation of 711 00:30:39,009 --> 00:30:42,830 query builder. You have something that can forward some 712 00:30:42,830 --> 00:30:45,469 methods for you. It'll query, it'll forward to_a, to_sql, 713 00:30:45,469 --> 00:30:48,339 and to_each, this variable called query inside your object. 714 00:30:48,339 --> 00:30:50,700 It accepts a query and sets that as an 715 00:30:50,700 --> 00:30:52,450 instance variable. Then it also has this kind of 716 00:30:52,450 --> 00:30:55,109 funky method name reflect, and reflect just instantiates a 717 00:30:55,109 --> 00:30:57,039 new instance of the class and adds the query. 718 00:30:57,039 --> 00:30:58,989 So it's essentially implementing that chaining idea. 719 00:30:58,989 --> 00:31:01,299 All right, so here's a lot of code, and 720 00:31:01,299 --> 00:31:03,029 we're gonna go over each of these pieces individually, 721 00:31:03,029 --> 00:31:05,429 but this is a, a post query builder. So 722 00:31:05,429 --> 00:31:07,369 I was trying to query some posts. Let's look 723 00:31:07,369 --> 00:31:10,289 at this first method. So, notice this first inherited 724 00:31:10,289 --> 00:31:12,969 from QueryBuilder. So let's say we have a method 725 00:31:12,969 --> 00:31:16,399 called with_title_matching and it accepts a title. Calls reflect 726 00:31:16,399 --> 00:31:18,759 and says dot where title matches and it adds, 727 00:31:18,759 --> 00:31:19,869 you know, you can see it adds the percent 728 00:31:19,869 --> 00:31:22,139 signs for the wild card characters. This is all 729 00:31:22,139 --> 00:31:23,659 encapsulated inside this guy. So you all you have 730 00:31:23,659 --> 00:31:26,599 to do is just call with_title_matching on your QueryBuilder. 731 00:31:26,599 --> 00:31:31,190 Let's go down to with_comments_by. With_comments_by(username), so we join 732 00:31:31,190 --> 00:31:35,709 comments and authors, and then where(Author[:username].in(usernames)). So we are 733 00:31:35,709 --> 00:31:39,399 expecting an array of usernames and this, again, reflects 734 00:31:39,399 --> 00:31:40,779 this query back to a new instance, returns a 735 00:31:40,779 --> 00:31:43,599 new instance to QueryBuilder, too, this time with. Cause, 736 00:31:43,599 --> 00:31:45,879 again, we're chaining all these things, right, so now 737 00:31:45,879 --> 00:31:47,700 we have both. If we call both these methods 738 00:31:47,700 --> 00:31:49,839 in a row, we would have find_by_username and with_title_matching, 739 00:31:49,839 --> 00:31:50,429 both together. 740 00:31:50,429 --> 00:31:56,669 So the last method I have here is since_yesterday. 741 00:31:56,669 --> 00:32:01,409 The post[:create_at] greater than equal to Date dot yesterday. 742 00:32:01,409 --> 00:32:04,119 Which means that now I can do something like 743 00:32:04,119 --> 00:32:06,019 this. There's not a lot of ugly syntax in 744 00:32:06,019 --> 00:32:09,749 here. It's all pretty clean. PostQueryBuilder.new with_comments_by pass in 745 00:32:09,749 --> 00:32:12,539 an array of usernames, dot to_sql. And notice it's 746 00:32:12,539 --> 00:32:14,200 doing all of the joining for me, like you'd 747 00:32:14,200 --> 00:32:16,629 expect. I can then just say dot with_title_matching("arel"), it 748 00:32:16,629 --> 00:32:20,059 adds that like, and then I can say since_yesterday 749 00:32:20,059 --> 00:32:23,469 and it will add that third condition. 750 00:32:23,469 --> 00:32:28,379 Pretty cool. Thanks. 751 00:32:28,379 --> 00:32:34,559 Cool. Appreciate that. But you might be sitting here 752 00:32:34,559 --> 00:32:37,379 thinking, god, this is super complicated. There's got to 753 00:32:37,379 --> 00:32:39,749 be something out there to help me write these 754 00:32:39,749 --> 00:32:41,859 queries better, because, you know, it's a lot of 755 00:32:41,859 --> 00:32:42,969 verbosity, it's a lot of code, it's a lot 756 00:32:42,969 --> 00:32:46,190 of extra stuff to remember. Well, fortunately, there is. 757 00:32:46,190 --> 00:32:47,489 So, I worked for a little while on a 758 00:32:47,489 --> 00:32:51,269 project called Scuttle. It was gonna be called Ariel, 759 00:32:51,269 --> 00:32:53,200 like Little Mermaid, Ariel, you know, but it was, 760 00:32:53,200 --> 00:32:56,969 unfortunately, too linguistically similar to Arel and so I 761 00:32:56,969 --> 00:32:58,169 thought I would have trouble saying it on stage. 762 00:32:58,169 --> 00:33:01,179 So I named it after Ariel in The Little 763 00:33:01,179 --> 00:33:03,190 Mermaid's best friend Scuttle. So if you go to 764 00:33:03,190 --> 00:33:05,809 scuttle dot io - thanks - if you go 765 00:33:05,809 --> 00:33:07,789 to scuttle dot io, you'll see a screen like 766 00:33:07,789 --> 00:33:10,859 this. It's an editor. You can enter in a 767 00:33:10,859 --> 00:33:12,609 SQL query, and at the bottom it will convert 768 00:33:12,609 --> 00:33:14,799 it to Arel for you. 769 00:33:14,799 --> 00:33:16,129 [applause] 770 00:33:16,129 --> 00:33:18,799 Thanks. Thanks. 771 00:33:18,799 --> 00:33:25,080 Again, it's gonna be pretty verbose, but you can 772 00:33:25,080 --> 00:33:26,759 tune your queries. There's a lot of other kind 773 00:33:26,759 --> 00:33:29,830 of text below explaining what it does. This is 774 00:33:29,830 --> 00:33:32,019 kind of a combination of three different projects, and 775 00:33:32,019 --> 00:33:33,209 you can check these out on GitHub if you're 776 00:33:33,209 --> 00:33:36,749 curious. So, I could not find a SQL parser 777 00:33:36,749 --> 00:33:39,029 in Ruby. I tried so hard to find one 778 00:33:39,029 --> 00:33:41,349 of these. And there just isn't one. The only, 779 00:33:41,349 --> 00:33:43,599 as far as I can tell, the only SQL 780 00:33:43,599 --> 00:33:45,149 parsers that I could find were written in Java, 781 00:33:45,149 --> 00:33:47,979 and using this thing called Antler, which is a 782 00:33:47,979 --> 00:33:49,059 parser generator. 783 00:33:49,059 --> 00:33:52,830 So, I finally found an Antler grammar for the 784 00:33:52,830 --> 00:33:57,099 Apache incubator tajo project, and grabbed that, generated these 785 00:33:57,099 --> 00:34:01,169 appropriate classes, and then wrote a JRuby wrapped around 786 00:34:01,169 --> 00:34:03,469 that that allows you to parse, it allows you 787 00:34:03,469 --> 00:34:07,469 to parse queries. So it only works currently with 788 00:34:07,469 --> 00:34:10,300 select and, I think, insert, because Apache incubator tajo 789 00:34:10,300 --> 00:34:13,540 apparently doesn't need to do anything else. So, and 790 00:34:13,540 --> 00:34:15,060 I wasn't ready to go write a bunch of 791 00:34:15,060 --> 00:34:16,619 other Antler stuff. So, if you guys, if somebody 792 00:34:16,619 --> 00:34:18,369 knows Antler around here, please, help me out. We 793 00:34:18,369 --> 00:34:18,859 can, we can make it better. 794 00:34:18,859 --> 00:34:21,520 And then the third part of this is something 795 00:34:21,520 --> 00:34:25,010 called scuttle-server, which is just a Sinatra app on 796 00:34:25,010 --> 00:34:27,909 top of JRuby that uses these two components. And, 797 00:34:27,909 --> 00:34:29,829 also I forgot to mention there's also this scuttle-Java. 798 00:34:29,829 --> 00:34:32,480 But anyway. Anyway, it uses these components to, to 799 00:34:32,480 --> 00:34:33,719 drive the website. 800 00:34:33,719 --> 00:34:37,099 OK. So that was a whirlwind. I have, like, 801 00:34:37,099 --> 00:34:39,579 five minutes left. Thank you guys all for coming 802 00:34:39,579 --> 00:34:40,208 and for listening.