Arel Primer
One of the easiest things about Rails is writing out SQL queries without writing out SQL queries.
So instead of writing something like this,
We can write something like this,
This is much more readable and effectively abstracts us from writing any SQL.
What’s happening under the hood is that Rails is using the Arel library to build SQL queries. Arel is a powerful SQL AST manager that lets us appropriately combine selection statements for simple to very complicated queries.
However, reader be cautioned – Arel is still a private API provided by Rails. Meaning that future versions of Rails could be subject to changes in Arel. Fortunately, the changes have been limited to a minimum with the latest versions.
Let’s take the same query and write it using Arel,
This gives us the Arel query which can be converted to SQL.
This only gives us only a partial query, we now need to chain this to the table in SQL.
Now calling .to_sql
let’s see what happens,
Ta da!
Advantages of Arel
-
Reusability
It’s much easier to reuse Arel queries as they are made up of interlinking nodes – a safer alternative than string interpolation.
-
Readability
Since SQL queries are long strings, the more complex queries are the more unreadable it becomes. It also becomes harder to edit small portions of the query as the entire query has to be understood first.
-
Reliability
If we join another table, our query will immediately break due to the ambiguity of the
id
column. Even if we qualify the columns with the table name, this will break as well if Rails decides to alias the table name. -
Repetition
Often we end up rewriting code that we already have as a scope on multiple classes.
Arel 101
Arel is quite intuitive to use. Once we pick up on the basic structure, it gets easier to pile things on, called nodes. These nodes can be chained together to create complex queries.
Let’s have a first look into how we can get started.
-
Arel Table
Before we get started creating queries, we need to decide the base table to perform the queries on.
users = User.arel_table
-
Arel Fields
We can extract columns to work on as well.
users[:role]
-
Where Queries
This is where things get interesting! Many things can be done and all of them are listed in the arel/predications.rb file. Let’s go through a couple of them.
users[:id].in([1,2,3]).to_sql => "`users`.`id` IN (1, 2, 3)" users[:id].gt(2).to_sql => "`users`.`id` > 2" users[:id].eq(3).to_sql => "`users`.`id` = 3"
In order to combine it with the ActiveRecord
where
query, simply,User.where(users[:id].gt(2)) => #<ActiveRecord::Relation [#<User id: 3, email: ...]>
-
Projection Queries
These are nothing but
select
queries. This would need to be executed directly as SQL and can not easily be combined with ActiveRecord queries.users.where(users[:role].eq(:admin)).project(:email).to_sql => "SELECT email FROM `users` WHERE `users`.`role` = 2"
To execute it,
ActiveRecord::Base.connection.exec_query users.where(users[:role].eq(:admin)).project(:email).to_sql # SQL (0.8ms) SELECT email FROM `users` WHERE `users`.`role` = 2 => <ActiveRecord::Result:0x00007fd7d5888178 @columns=["email"], @rows=[["[email protected]"]], @hash_rows=nil, @column_types={}>
-
Aggregates
The standard aggregates (sum, average, minimum, maximum, and count) are also available.
users.project(users[:age].sum) users.project(users[:age].average) users.project(users[:id].count)
-
Order
users.order(users[:name], users[:age].desc)
-
Limit & Offset
users.take(5) => 'SELECT * FROM users LIMIT 5' users.skip(4) => 'SELECT * FROM users OFFSET 4'
-
Joins
This is where Arel becomes really useful as it is easy to build complex SQL queries without resorting to writing them out.
photos = Photo.arel_table users.join(photos)
We can also specify the relationship,
users.join(photos, Arel::Nodes::OuterJoin).on(users[:id].eq(photos[:user_id]))
We can also use conditions,
users.joins(:photos).merge(Photo.where(published: true))
This covers most scenarios faced in real life. However, for a deep dive, I suggest reading the documentation.
Arel Compositions
Where Arel becomes really useful is when refactoring code to become more extensible. Let’s take an example of a user belonging to multiple organizations in an application. In order to find out what organizations he belongs to we would do,
Now, if we need to find out all the organizations that the user has commented on, we would need to repeat the same,
This is repeated code! Let’s see how we can refactor it.
Let’s create a class called Memberships
And here’s how we would use it,
Now if we want to find the organizations that the user has commented on,