RailsConf 2014 -Advanced aRel: When ActiveRecord Just Isn't Enough

CAMERON DUTRO: OK. Sweet. Welcome, everybody.

Thank you all for coming to this talk.

This is the, the second talk that I've given
at a RailsConf,

and first one that I gave was on

internationalization, and there were like,
maybe,

fifteen people in the audience.

And now there are a lot more.

I guess this is a more popular topic,

I don't know. So, cool.

So, again, thanks all for coming. I am Cameron

Dutro. I work for the U.S. Government. No,
no,

not really. I work for Twitter. I'm on International

Engineering, which explains the topic of my
previous talk,

two years ago. You can follow me at camertron

on Twitter. Look at me on GitHub as well.

A little bit of personal background, this
is my

cat. He's pretty cute. His name is Chester.
You

can follow him at @catwithtail. All right,
so, we're

gonna do a little bit of an interactive exercise

before I dive into the deep technical portions
of

this. How many, raise your hand if you've
ever

written Rails 2 code? You guys, wow, look
at

that. OK. Nice. How many of you, in your

Rails 2 code, saw maybe a query that looked

like this? Yeah.

So, a ton of strings in here. There's not

really an object-oriented way to construct
queries in Rails

2. So, thank heavens for Rails 3 and 4,

because they introduced a much more chainable
object-oriented pattern

for doing query construction. So, how many
of you

wrote Rails 3 or 4 code that looks like

this?

That's pretty much the same thing as before.
And,

I don't want to say, you know, you're doing

it wrong, but, you're doing it wrong. So,
let's

go into this code and maybe clean it up

a little bit, right.

So I just moved one of those strings that

defined a join association, or a join, on,
on

this table, and I moved it up into a

symbol, so I'm joining the comments table
on Post.

So this is a lot cleaner looking, and it's

allowing ActiveRecord to introspect the associations
between these two

models, derive the columns to use, and then
automatically

construct that query for you to do the right

thing.

So that's awesome. But, we still have a couple

other problems with this code, right. We still
have

these question marks in the where clause,
and we

still have that second join. And the reason
that

join is not yet a symbol is because it's

an association that doesn't pertain immediately
to Post, right.

It's joining authors in relation to comments.

And, by the way, in this system, authors are

a part of comments, not part of posts. I

know it's a little confusing. So, you could
simplify

this even further and you could use this cool

hash rocket syntax that says, even though
comments and

author are not, well comments is, but author
is

not directly related to posts, I want to get

there through comments. And so I can add this

hash rocket style syntax also.

But we still have these question marks. So,
fortunately

ActiveRecord has given us a lot over the past

couple of years that have passed between 2
and

3 and 4. But this is still a little

bit, you know, a little bit dirty, and, especially

if you wanted to do something like an outer

join in this join clause, or if you wanted

to do something a little bit more than say

I want to say something is equal to something

else, right. A column is equal to a value.

So I kind of look at this style as

better but, you know, you're still handed
a great

weapon and not quite using it appropriately,
right. So

let's look at parts of this query. I'm gonna

reuse the previous string example to identify
what's wrong

with these queries, but let's look at the
previous

queries. And we'll extract little bits of
this and

talk about why they're less than ideal.

So you've got this join, and the first thing

that's wrong with this is you have to write

the words join and the words on. And that's

not difficult for somebody to do. It's not
difficult

to type J-O-I-N on your keyboard. But it does

mean that you have to know MySQL or PostGreSQL

or SQLite syntax to get this done. You also

have, essentially, no syntax checking. So,
you don't have

any idea, having written this string, you
don't have

any idea that authors is an actual table,
or

that authors dot id is a column. All you

know is that you have this string and that's

getting handed to the, to ActiveRecord, and
then it's

executing it for you.

Let's look at the where clause here as well,

and I kind of alluded to these problems before,

but we'll go over these in detail. So, again,

you have to know MySQL syntax, you have to

know that there's an and between these two
things.

And, which of course, and all these syntactical
issues,

you know, these may not persist across different
flavors

of databases, so maybe MySQL has this particular
construction

for and, but it could be that PostGres has

a different one. And it probably doesn't,
but you

know, you don't know that.

It's confusing also to match arguments with
question marks.

I mean, how many of you have looked at

a piece of code and thought to yourself, man,

there are so many join conditions, or so many

where conditions here? And I don't know which
argument

matches up to which question mark. Yeah, I
mean

I've done that a bunch of times.

It's also not very object-oriented, right.
You have this

where string and then comma, comma, comma.
It's almost

functional in its style. And, again, we have
that

no syntax-checking problem.

So, you might think to yourself, boy, I wish

there was a better way to do this, and

maybe I'll do a search, and you might come

across a StackOverflow article that tells
you to use

strings in your queries. And you might come
across

RailsCast 202, which tells you to use strings
in

your queries. You might find blogs. You might
ask

coworkers. You might ask friends, family members,
or even

your cat, and they'll all tell you that you

should use strings in your queries.

But I'm here to tell you that no, you

can avoid doing that. You can keep calm, you

can avoid literal strings in your queries.
Let's look

at the previous query, but completely, I like
to

use the word, arelized. This is the query
arelized.

So, notice, there's a bunch of cool things
going

on here.

First, you don't have to know SQL syntax.
All

you have to know is Ruby syntax. And, you

know, of course, this is a little bit of

a DSL, so you might have to go about

learning Arel as well, learning what it can
do.

But that's what this talk is for. That's why

you're here.

Ruby syntax-checking, you get that for free.
So if

you have an unbalanced parenthesis or you're
referencing a

model that doesn't exist, it will throw an
exception

and you can go and it'll fail fast, in

other words, you can go fix that bug before

you deploy to production.

So, because ActiveRecord 3 and 4 are chainable,
it

means Arel is chainable as well. It allows
you

to compose queries in kind of step-by-step
pattern, a

step-by-step fashion. So it's also chainable.
And you have

none of those dang question marks.

It's also a lot easier to read, at least

in my opinion, because what I'm reading is
Ruby,

not SQL embedded in Ruby, which to me is

weird. So, here's what we're gonna cover today.
I've

kind of already gone over the, the initial,
you

know, diagram of, of how, kind of the state

of the world now, and so when I cover

what ActiveRecord is, what Arel is, there's
kind of

a, an indistinct line separating those two,
and I'll

try to separate that.

Talk about how to reference tables and columns.
What

terminal methods are and why they're confusing
and when

they happen. We'll talk about, of course,
the meat

of SQL. So select, where, join, and this special

thing called join association, and order.
Group is, is

way easy so I'm not gonna cover that. And,

or, greater than, less than - all these great

things you can do with Arel that you can't

necessarily find online. And then we'll also
talk about

the match, which is executed SQL-like, and
in, which

allows for sub-queries.

OK. So what is ActiveRecord exactly? Well,
you all

know this, but it bears repeating here. ActiveRecord
is

a database abstraction. So there's no need
to speak

a dialect of SQL. It connects to queries,
it

returns data back to you. And it also functions

as a persistence layer, you know. Database
rows are

Ruby objects. You can pull them out, you can

put them back in. And they also encapsulate,
in

many cases, your domain logic. And I know
people

say no fat models, no fat controllers, but
the

truth is that a lot of domain logic gets

shoved into those models.

They, they contain validations, they contain
all that good

stuff. And they also define associations between
your models.

So, Arel is distinct in that all that it's

really used for is building queries. So it's
described,

on its GitHub page, as a relational algebra
for

Ruby. And that didn't make any sense to me

because, it didn't until I started reading
more, because

relational algebra, those two words really
don't, I don't

know how those work put together, but that's
how

they describe it.

Essentially what that means is that it just
builds

SQL queries in an object-oriented way. It
generates abstract

syntax trees, or ASTs for your queries, and
then

uses the visitor pattern to put those out
to

SQL strings and then hands those off to ActiveRecord

to execute. It also enables chaining, as we
talked

about, because ASTs are kind of nicely composable,
because

all you have to do is reference a subtree

in order to pull in, you know, some other

branch of query logic.

So, one blogger also described Arel as vexingly
undocumented.

So you can look at find pieces of Arel

all over the place, but joining that all together

is a, something that took me a long time.

In fact, this talk came out of a project

that I was working on at Twitter. I work

on the Twitter translation center. We needed
to implement

an access control system. And to do that,
we

had permissions and we had groups and all
that

kind of thing, and in order to join all

that logic together and decide whether a user
was

able to see a particular phrase, it turned
out

that that was a difficult thing to do. We

would have had to do a lot of, a

lot of, you know, strings in our SQL queries.

And we wanted to avoid that. We wanted to

be able to be composable and use scoping and

all that great stuff.

So, having done that project, I'm trying to,
and

I'm taking that knowledge and kind of imparting
it

to you. So that project was a, a direct

reason why this talk was created. I find it

very difficult to find the information I was
looking

for.

So, as a, as a recap here, you know,

Arel knows nothing about your models. It knows
very

little about your database. All that it knows
is

that you have these queries you're trying
to construct.

It doesn't care if those tables exist. It
doesn't

care if those columns exist. ActiveRecord
cares. But Arel

really doesn't care. And it does not store
or

retrieve data. All of these are ActiveRecord's
responsibilities. So,

in summary here, Arel constructs queries and
ActiveRecord does

everything else.

If you were to look at this as a

hierarchy, and kind of a diagram I guess,
you've

got ActiveRecord, which you would interact
with as a

developer. ActiveRecord calls out to Arel
to, to process

a query, form a query, and then sends that

query to the database, and the data comes
back

and ActiveRecord will package that into nice
Ruby objects

for you.

So, something also that bears some introduction
here is

what an AST is, because as DHH so eloquently

put it in his keynote this morning, not everybody

comes from a computer science background.
And ASTs are

very computer science-y. So, an AST essentially
is, or

really what a tree, in this case, this is

a basic tree, this is actually a binary tree,

and what this means is that, so, it's made

up of nodes. Each node has left and right

children, unless it's the bottom. It's called
a leaf,

or it's called a, yeah, a leaf node.

So we have left and right children. In this

case, it's a binary tree because you have
two

children. You could have an n tree which could

have multiple children. So, why are trees
important? Why

are they cool? Well, a tree is cool because

you can represent things like equations with
them or,

or expressions. So five times parenthesis
six plus three,

you can represent this in a tree by defining

a node at the root here, which is multiplication.

That's an operator. And then each left and
right

child represents the two different operands
of that expressions,

of that, of that operator. So five times six

plus three.

So the six plus three would be evaluated first,

because it's in parenthesis. So, notice also
the parenthesis

are grouping this expression together. And
then five times

that. You can kind of expand this concept
into

how this would look for a SQL expression,
by

looking at this example. So, you might have
a

query root and then a select from, and select

has two columns, id and text, and from has

just one child post, posts. And this would
be

an entry.

Now, I don't actually know if this is how

Arel does this in the background. This is
a

representation that you could imagine is going
on behind

the scenes.

OK. So let's get to some code. And, and

the first thing I want to talk about is

a little gem that I put together to kind

of help with this talk and do some Arel

things that just take kind of some of the

verbosity out of Arel. So it's called arel-helpers.
Go

ahead and install this thing. It's got three
kind

of helpers. Very small.

And let's also, so, let's jump on from there

and we'll talk about how to reference tables
and

columns in Arel. So, let's pretend you have
this

table. It's called post. It has many comments.
And

let's say you wanted to reference the id in

this table. Well, post dot arel_table(:id).
So any class

that inherits from ActiveRecord::Base has
this method called Arel

table. And it returns to you an instance of

Arel colon colon table that was a, is a

way of referencing your table in code.

Now brackets :id of course gives me what's
called

an Arel attribute, and I can look at, I

can do the same thing for text, and I

can even do this for columns that don't exist.

So if I wanted to derive a column, I

could also use this to reference that column.
Now,

that's not always true. But that's almost
always true.

So this returns an Arel::Attributes::Attribute.

Now you don't really care what it returns.
You

just want, you care that you can use it

in your code. So, the arel-helpers gem kind
of

makes this a little easier. If you include
ArelHelpers::ArelTable

in your model, you can now reference columns
off

your tables with just square brackets and
eliminate arel_table,

it just delegates down to arel_table. And
it still

returns an attribute to you.

All right. So kind of going off of that,

what does this statement return? It's a little
pop

quiz. Does it return "Rails is Cool." Just
an

array of strings. Does it return an array
of

instantiated objects that are records, ActiveRecords?
Or does it

return an ActiveRecord::Relation? You guys
might know the answer

to this. Who thinks it's A? Who thinks it's

B? OK. And who thinks it's C? Nice. OK.

It is, in fact, C.

One thing that's very cool about Rails 3 and

Rails 4, ActiveRecord 3 and ActiveRecord 4,
is that

any of these methods, these database methods,
like select,

join, where, they actually return an ActiveRecord::Relation.
They don't

actually execute right away. So, that means
that they

are also chainable. So if I said post dot

select title, and I assigned that the query,
that's

not actually executing anything. That's storing
this intermediate AST

in the variable called query. Which means
that I

can also say query equals query dot select
another

column name, and then if I were to say

query dot to_sql, I would get out the string

the ActiveRecord will send to the database
to grab

data from.

So, it's also important to notice here, I've
kind

of introduced this to_sql method. Any ActiveRecord
relation that

you have, you can call to_sql on it and

it will give you the string that it would

execute against the database. So that's very
handy. And

I'll use that throughout the whole presentation.

Yeah. So relationships can be changed.

All right. So let's get into some of the

specifics here. So let's talk about select.
We talked,

we looked at select a little bit. You can

actually select multiple columns using array
syntax, so this

is actually just ActiveRecord at this point,
we're not

even diving into Arel right now. And you might

think, OK, so we're using this to_sql again.
We

get :id, :text from Post. Everything's great.

Let's say that we wanted to get the SQL,

though, for count. So we just say give me

all of the Posts, the counts here, and you,

you probably wouldn't say select(:id) and
then count, but,

you know, it's an example, so. And instead
of

getting back a SQL string, you instead get
back

a NoMethodError.

So why did that happen? Well, it turns out

that count is a terminal method. So, let's
dive

into why that happened. So. Being a terminal
method,

it would, it's executing immediately and returning
a result

to you instead of giving you an ActiveRecord
relation,

so that's why, it's good to distinguish between,
and

know which ones are going to do this to

you, right.

The way to actually get a count, and get

a SQL string back out for it, would be

to kind of dive into Arel here. Use your

Arel table knowledge from before and call
count on

an Arel attribute. And then, of course, you
can

pass that to select as an array. Pass text

as a symbol. And out comes this count, like

you're expecting.

So let's look at what terminal methods are.
So,

terminal methods execute the query immediately.
They do not

return a relation. They're mostly count, first,
and last,

so first and last are kind of like array

accessors, right. To_a, pluck, and any of
the enumerable

methods will all execute your queries immediately.
So, for

example, this, each over these objects, and
you would

expect this to happen, because this is not
a

lazy enumerator. This is a regular enumerator
and you're

iterating over all of these things and you're
putting

out the text. And that's obviously going to
execute

right away.

But, more surprising is this one. So, if I

say each slice here and it's a lazy enumerator,

in other words, I don't pass a block to

it, this will also execute immediately. So
just beware

of that.

All right. So let's go actually for real into

select here now. So we've already seen how
we

can say dot count on an attribute, on an

Arel attribute. We can also say dot sum, and

that will give us this sum method. This is,

these are all, by the way, MySQL examples.
Notice,

also, that Arel and ActiveRecord have nicely
added this

AS sum_id for you. So, any table, any, any

column like this will automatically get a
derived column

name as well.

You can change that column name by adding
an

as here. So just chain as right on the

end of sum. Post visitors sum as visitor_total,
and

that will give it kind of a custom derived

column name for you. You can also say dot

maximum and dot minimum, and these will all
use

kind of the built-in SQL methods that you're
expecting.

Let's say, though, that you're trying to,
you know,

run a function that isn't part of Arel's DSL

or Arel's knowledge, I guess. It's kind of
outside

of Arel's abilities. You can actually define
a function

call that, of any arbitrary function. This
covers things

like length or, like, any of the other methods

that MySQL provides, or PostGres, that may
differ from

database to database. So, in this case, I
am

saying a named function dot new, passing in
the

name of the function and then giving it an

array of arguments that function takes.

And, then again, I'm also aliasing the result
of

that as length, in this case. So giving it

a derived column name.

So, we can shorten this a little bit by,

cause it's a little bit verbose, by including
Arel::Nodes.

And then this just becomes NamedFunction dot
new. So

that's kind of a little hack you can add

to your queries.

Something kind of also pretty cool about Arel,
this

is kind of one of the other little cool

select things, you can also say Arel dot star

here, if you wanted to select every single
record,

right, so, or every single column from the
record.

So select start from post. Arel dot star exists,

you can use it in a bunch of different

places.

OK. So, many people also don't know that select

and Arel and ActiveRecord, I should say, have
the

ability to also just specify a from. So you

want to specify a, a sub-query inside a from.

This is pretty common. You can do this instead

of a join or instead of a, a more

complicated query here. So I'm saying select
ID, and

then I'm passing a sub-query into the from.

So one thing that's really important to notice
here

is you'll notice that the end of the sele,

the second select, I say dot AST. So also

at any point in your construction, you can
call

AST on your relation, and it will give you

back this massive tree that it's constructed.
And in

this case, from requires you to pass it an

AST otherwise it gets confused. It doesn't
know what

to do with a relation. It only knows what

to do with it if it's an AST or

the column or things like that. So. Or a

table, in this case.

All right. So that pretty much covers select.
There's

a lot of things you can do with select.

I was kind of breezing past a lot of

it. If you have any questions, you can always

talk to me or, or you know, I guess,

go online. But there's not that many resources.
Anyway.

OK. So, the wonder of where.

So, where is also gotten a lot better since

Rails 2. I can pass in a title to

my where here, where post dot where title
is

"Arel is Cool," and this will do what you

expect. It will say, it'll construct the SQL
query,

user dot title equals this value. So no question

marks here, which is very nice.

But what happens if you want to, you know,

do something a little more. Oh, here's the,
here's

the Arel version of this. So, you can do

this with pure Arel. It's a little more verbose.

You wouldn't really do this. But let's say
you

wanted to say not equal to or less than,

things like that. You can do the same thing.

So you can say not equal in this case,

and notice that it'll put the band equals
in

MySQL. You can also say, kind of something
cool

here, not equal to nil, and it will serialize

that as is not nil, which is what your,

what you should be expecting.

Some of the cool methods. Greater than, GT.
Less

than. LT. All makes sense. And all of these

are just chained right off of this, this,
this

column attribute here. Here's greater than
equal to -

GTEQ, and also LTEQ.

So, we also, oftentimes, in our queries, need
to

be able to and and or our way to

a kind of a multiple where. We can do

the same thing here. So, if you were to

say where multiple times just with ActiveRecord,
it would

automatically give you an and. But if you
wanted

to specify an or, you would need to do

something like this. So we have our title
and

attribute from post, we're saying equal "Arel
is Cool",

and the post id is equal to twenty-two or

twenty-three. And notice that I have actually
injected, so

I have put, inside the and, I put another

attribute and a dot or, and this will, Arel

will just know, it will, the visitor pattern
will

know how to add the right parenthesis to this

query.

So, notice the output query has an and and

then parenthesis and the or, so that no operator

precedence messes us up here. Now, notice
that I

said equals twenty-two or twenty-three. I
could also have

put an in here. So in also accepts arguments

here that could, essentially an array of values
here.

So it's the same thing, this will have the

same effect.

You can also compose your wheres. You can
add

named functions in here. So you can also do

dot count, dot sum, all that stuff works.
Those

are aggregate functions. Here's length. Oh,
I'm aggregating this

stuff together and it all, it all just kind

of works. It's pretty cool.

All right. So let's get to my favorite part

of Arel, and that's joins. So ActiveRecord
makes joins

pretty easy, except for when you're trying
to do

an outer join, and we'll just, we'll just
see

how that looks in a second here.

So let's pretend we have this data model.
It's

a basic blog, essentially, so you've got a
post.

Post has_many comments. And each comment has
an author.

So let's, let's see what happens when we dive

into some joins here. So we say join. This

is without any help from Arel. Joins comments,
or

comment in this case, where id is forty-two.

So, very cool. ActiveRecord inspects this
model. It inspects

the association between author and comment.
And automatically generates

the right join conditions for you. Pretty
cool. You

can also specify, as I mentioned, kind of
a,

a through table in this case. So I want

to join comments, and then I also want, you

know, using author, I've got comments, and
I also

want to find the posts of the comment reference

or that, that comment is part of.

So, this will also do what you expect. It

will grab all the correct attributes for the
models.

Hook them all together. A lot of cool introspection.

But, again, what about outer joins? So, you
notice

back here, it's inter-joined. And there's
really no way

to specify an outer join without diving in
to

Arel. At least that I'm aware of.

OK. So let's look at this guy again. So

let's say I wanted to add an outjoin again.

Well, first, it kind of bears mentioning here
that

you can turn this stuff into, also, pure Arel

stuff. So, we have a joins comment. We can

also say comment dot joins post, and then
call

this method called join sources. So, join
sources is

a way of saying, I have gotten this query

from ActiveRecord. And this is, again, it's
just using

ActiveRecord. And I just want to grab the
join

sources. I don't care about the select or
the

where. Just give me the join sources and pass

that onto whoever is wrapping. In this case,
another

query.

You can do the same thing with first join.

And then you can also specify the on conditions

manually here. So, we have a, a join comment

Arel table, and we're joining that on :comment_id
is

equal to author :comment_id, and then saying
join sources.

And we're doing this for both.

Now, this is gonna get pretty verbose, but
at

this point, we can finally add our outer join.

OK. So notice that the attribute, Arel attribute
dot

join takes a third argument, or a second argument,

that is the join type. So you could say

inner join here, or outer join. And that results

in, essentially, the same query. But this
time an

outer join.

You're thinking, that is a lot of code. Why

would I ever type that much code? And you're

right. That's way too much code. So, part
of

the Arel-helpers library is called JoinAssociation.
And this will

introspect your model, just like ActiveRecord
would do. Hand

those off to what's called a select manager,
and

then use the second argument Arel::OuterJoin
to give you

an outer join, but without all the tedium
of

specifying all these conditions.

So I can slide this in here. JoinAssociation,
and

I supply the original model and then the association

name, and then Arel::OuterJoin. And I can
do that

for both of these guys. Pretty cool.

But wait, there's more. All right. So you've
got

this guy, and you're like, you know, I really

want to supply some extra custom on conditions.
Fortunately,

join dependency or join association, rather,
will yield a

block to you that has both the association
name,

which is a string or symbol, and also the,

a join condition's intermediate query object.
So you can

say join conditions and, and then pass in
anything

else you would like to construct. In this
case,

we're making sure that the created_at date
is less

than or equal to yesterday.

So this will, again, in the same query, but

this time. And it would have been an outer

join except that I, I actually took out, for

space reason, I took out the extra OuterJoin
call

there. So this, this would return inner join
and

then an outer join.

All right, so let's talk about join tables.
As

for, for, so far we've been talking about
tables

that have defined associations between them,
and those associations

are not has_and_belongs_to_many. So I'm trying
to kind of

freshen us a little bit and say, let's talk

a table that, or two tables that are associated

but via a join table. So, you might have

a series of courses that are taught by a

series of teachers. Multiple teachers can
teach one course,

and courses can have multiple teachers.

So, in this case, you've got three tables.
Courses,

teachers, and courses underscore teachers.
This is pretty common

in Rails, I think. So, it means you have,

again, the course table. You can refer to
that

with course dot arel_table like we saw before.
Teacher

table, again, reference that with Teacher
dot arel_table. But

then we've got some magic in here for courses_teachers,

because there, there really is no model for
this.

There's no constant we can use to reference
this

table. So instead, we need to create an object

for it.

So we'll create an Arel::Table. This is doing
this

manually. We're giving it the name courses_teachers,
and now

we can use this variable ct anywhere we cant

to refer to this table. So, for example, this

query. I want to say joins, and we have,

you know, courses join teachers, and then
just for

the purposes of illustration here I've shown,
you know,

what you would do in Arel to construct the

join conditions here, so. You know, on this
on

this and then dot join sources.

And I didn't show the SQL output of this

but I think it's pretty, pretty clear from
the

previous examples.

OK. So that wraps up join. So let's talk

about order. Order is actually the simplest
of the

kind of SQL constructs I'll talk about today.
You

all know that you can say post dot order

visitors and visitors in an integer in this
case,

then dot to_sql. And this will give you a,

a return value of, you know, all of your,

all of your posts ordered by the number of

visitors. You can also, and some people don't
know

this, but this also just an ActiveRecord construction
here.

Post.order, that should be (:visitors), reverse
order. This will

add a descending clause or a descending keyword
to

the end of your query.

Now, you can also do this in Arel. You

can say post(:views) descending to SQL here,
and this

is essentially the same thing. All right.
So let's

talk about sub-queries with in.

So we talked about sub-queries with from.
You can

also do sub-queries with in, and you can use

that AST that we talked about before. SO here's

an example of that. This is a very trivial

example, but we say post dot where and then

we say Post.arel_table in and we supply it
another

query. So we're adding a sub-query into this
in

call.

So that's pretty cool. And it does the right

thing. It adds the correct parenthesis and
everything's great.

All right. And then like queries with matches,
you

can also do this with Arel. So most of

the time you'd probably have to say, well,
I'd

like to, if I wanted to do a like,

in other words match, kind of fuzzy match
against

a column in your database, you would need
to

add a like or, I guess, what is it

in PostGres? Like. What is it, double ilike
or

something. Anyway. So it's one of those things.

So and then usually you have to add wild

card characters to this, and this would normally
be

a big string in your query. But, you can

fix that with Arel. So, Post dot where, and

we have a matches Arel and then I just

put my - so you do have to add

these. Unfortunately you have to add these,
these wild

card characters, but it's just dot matches.
Super easy.

And that gets serialized out into a like.

And, interestingly enough, I didn't know this
when I

started out, but it looks like it's actually
taking

that string and encoding it into Base, Base16.
Hexadecimal.

All right. So we've learned all this awesome
stuff

about Arel. We know that it can do joins

and wheres and likes and ins and all kinds

of awesome stuff, all without the use of,
well,

mostly without the use of strings. We also
learned

that it's pretty chainable. So, when you think
about

something that's chainable, the thing that
comes to mind,

for me at least, almost immediately, is the
builder

pattern.

And I think, well, why would I want to

construct a huge query inside my model or
controller?

I'd much rather have an object do this for

me. Now, is that always the right choice?
No,

of course not. But it's, it can be nice

to encapsulate logic, like building a huge
query. So

that's why the next step, or the next point

I'm gonna bring up, is how to construct a

query builder. And this is also part of the

ActiveRecord, or the arel-helpers gem that
I mentioned before.

So this is pretty much the entire implementation
of

query builder. You have something that can
forward some

methods for you. It'll query, it'll forward
to_a, to_sql,

and to_each, this variable called query inside
your object.

It accepts a query and sets that as an

instance variable. Then it also has this kind
of

funky method name reflect, and reflect just
instantiates a

new instance of the class and adds the query.

So it's essentially implementing that chaining
idea.

All right, so here's a lot of code, and

we're gonna go over each of these pieces individually,

but this is a, a post query builder. So

I was trying to query some posts. Let's look

at this first method. So, notice this first
inherited

from QueryBuilder. So let's say we have a
method

called with_title_matching and it accepts
a title. Calls reflect

and says dot where title matches and it adds,

you know, you can see it adds the percent

signs for the wild card characters. This is
all

encapsulated inside this guy. So you all you
have

to do is just call with_title_matching on
your QueryBuilder.

Let's go down to with_comments_by. With_comments_by(username),
so we join

comments and authors, and then where(Author[:username].in(usernames)).
So we are

expecting an array of usernames and this,
again, reflects

this query back to a new instance, returns
a

new instance to QueryBuilder, too, this time
with. Cause,

again, we're chaining all these things, right,
so now

we have both. If we call both these methods

in a row, we would have find_by_username and
with_title_matching,

both together.

So the last method I have here is since_yesterday.

The post[:create_at] greater than equal to
Date dot yesterday.

Which means that now I can do something like

this. There's not a lot of ugly syntax in

here. It's all pretty clean. PostQueryBuilder.new
with_comments_by pass in

an array of usernames, dot to_sql. And notice
it's

doing all of the joining for me, like you'd

expect. I can then just say dot with_title_matching("arel"),
it

adds that like, and then I can say since_yesterday

and it will add that third condition.

Pretty cool. Thanks.

Cool. Appreciate that. But you might be sitting
here

thinking, god, this is super complicated.
There's got to

be something out there to help me write these

queries better, because, you know, it's a
lot of

verbosity, it's a lot of code, it's a lot

of extra stuff to remember. Well, fortunately,
there is.

So, I worked for a little while on a

project called Scuttle. It was gonna be called
Ariel,

like Little Mermaid, Ariel, you know, but
it was,

unfortunately, too linguistically similar
to Arel and so I

thought I would have trouble saying it on
stage.

So I named it after Ariel in The Little

Mermaid's best friend Scuttle. So if you go
to

scuttle dot io - thanks - if you go

to scuttle dot io, you'll see a screen like

this. It's an editor. You can enter in a

SQL query, and at the bottom it will convert

it to Arel for you.

[applause]

Thanks. Thanks.

Again, it's gonna be pretty verbose, but you
can

tune your queries. There's a lot of other
kind

of text below explaining what it does. This
is

kind of a combination of three different projects,
and

you can check these out on GitHub if you're

curious. So, I could not find a SQL parser

in Ruby. I tried so hard to find one

of these. And there just isn't one. The only,

as far as I can tell, the only SQL

parsers that I could find were written in
Java,

and using this thing called Antler, which
is a

parser generator.

So, I finally found an Antler grammar for
the

Apache incubator tajo project, and grabbed
that, generated these

appropriate classes, and then wrote a JRuby
wrapped around

that that allows you to parse, it allows you

to parse queries. So it only works currently
with

select and, I think, insert, because Apache
incubator tajo

apparently doesn't need to do anything else.
So, and

I wasn't ready to go write a bunch of

other Antler stuff. So, if you guys, if somebody

knows Antler around here, please, help me
out. We

can, we can make it better.

And then the third part of this is something

called scuttle-server, which is just a Sinatra
app on

top of JRuby that uses these two components.
And,

also I forgot to mention there's also this
scuttle-Java.

But anyway. Anyway, it uses these components
to, to

drive the website.

OK. So that was a whirlwind. I have, like,

five minutes left. Thank you guys all for
coming

and for listening.