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.