Those in the know use Arel as a clever SQL AST manager for Ruby. It makes SQL queries easy to read, reliable, and secure - and it’s database agnostic. Learn the basics of Arel right here in our mini-tutorial.
In this blog post we will show you what Arel is and how it can be used to create database queries. If you are interested in writing complex, database engine agnostic queries directly from Ruby, then Arel may be a clever solution to your problem.
Arel (A Relational Algebra) is an SQL AST (Abstract Syntax Tree) manager for Ruby, which allows you to generate SQL queries for various database engines and is used by ActiveRecord under the hood. It also allows you to construct more complex queries without writing plain SQL or string interpolation - making them more readable, reliable (i.e. fixing the ambiguity problem - which we will show later) and secure.
Before diving into more complex statements in Arel, let’s start with the simplest ones.
Probably the most common Rails query is `find(id)` which allows us to find a record based on its primary key. The Arel equivalent will look like this:
users = User.arel_table
user = User.where(users[:id].eq(id)).take(1).firstNote: Previously, instead of using `User.arel_table` you could also use `Arel::Table.new(:users``).` This is no longer recommended since automatic type casting is deprecated (and will be removed in the newest version) in favor of an external type caster (which needs to be passed explicitly).
For more info on this deprecation, you can check this commit or compare the result of `User.arel_table` and `Arel::Table.new(:users)`:
> User.arel_table
=> #<Arel::Table:0x007fdca7c06300 @columns=nil, @name="users", @table_alias=nil, @type_caster= #<ActiveRecord::TypeCaster::Map:0x007fdca7c06350 ... >>
> Arel::Table.new(:users)
=> #<Arel::Table:0x007fdcad56ea28 @columns=nil, @name="users", @table_alias=nil, @type_caster=nil>As you can see, the `type_caster` instance variable for `User.arel_table` is set automatically, but not so with `Arel::Table.new(:users)`.
Reliability
Now let’s talk about that ambiguity problem that we mentioned earlier. Let’s imagine that you would like to fetch users who have registered in 2017 with their published posts.
You might try `eager_load` to load users with posts:
User.eager_load(:posts).where("created_at >= ? AND published = ?", Date.new(2017), true)However, if you try to run this query, you will get an error: `ActiveRecord::StatementInvalid: PG::AmbiguousColumn: ERROR: column reference "created_at" is ambiguous`. This happens because both the `users` and `posts` tables contain a `created_at` column. This problem can be easily fixed by adding the table’s name (`users`) to the column’s name in the condition:
User.eager_load(:posts).where("users.created_at >= ? AND published = ?", Date.new(2017), true)Let’s turn to Arel to see how a similar query would look like:
recent_users = User.arel_table[:created_at].gteq(Date.new(2017))
published = Post.arel_table[:published].eq(true)
User.eager_load(:posts).where(recent_users.and(published))When we work with Arel, we always have to explicitly provide the column and table names that we are matching - a query will never depend on implicit table names or aliases, meaning that we can’t make this particular mistake in our code. We also have additional predicate operators (here is the full list) at our disposal so we don’t need to interpolate the strings.
Readability
Imagine that you need to find a user who either has a first name or last name but not the one who has both or none.
Again we will start with an ActiveRecord example:
User.where('(first_name IS NULL AND last_name IS NOT NULL) OR (first_name IS NOT NULL AND last_name IS NULL)')You can try to use intermediate variables for storing each condition:
first_name_clause = 'first_name IS NULL AND last_name IS NOT NULL'
last_name_clause = 'first_name IS NOT NULL AND last_name IS NULL'
User.where("#{first_name_clause} OR #{last_name_clause}")
# alternativly you can use `or` method from ActiveRecord
User.where(first_name_clause).or(User.where(last_name_clause))But this is not the best way to split them - we either need to operates on strings directly or duplicate ActiveRecord relations.
Let’s instead try using Arel again:
table = User.arel_table
first_name_nil_clause = table[:first_name].eq(nil).and(table[:last_name].not_eq(nil))
last_name_nil_clause = table[:first_name].not_eq(nil).and(table[:last_name].eq(nil))
User.where(first_name_nil_clause.or(last_name_nil_clause))With Arel, we are able to split the query conditions using objects and then nicely combine them when we need to run the query. Using these Arel building blocks makes query composition a whole lot easier and more traceable than writing a complete, complex SQL query.
A few more examples
Finding a record containing a substring
`ILIKE` is a Postgres specific function, so if you use it directly in your model’s scope or query object, it means you won’t be able to use it with different RDBMS.
With Arel, you can use an alternative approach, one that will use a function that is available in the database engine of your choice:
table = User.arel_table
where_clause = table[:first_name].lower.matches('%ronin')
User.where(where_clause)Subquery join
Let’s try one more example, to see how Arel can help with creating more complex queries.
Imagine that you would like to select the top most popular authors, based on the number of comments their posts get. To do that, you’ll need to join `comments` with `posts` and then group them by `user_id` and count. Those results can be used to fetch `users` and sort them.
Here is how the SQL query might look like (there are more solutions to this problem but we just wanted to show how we can use Arel for one of them):
SELECT users.*, popularity.comments_no
FROM USERS
INNER JOIN (
SELECT post.user_id, COUNT(*) AS comments_no
FROM comments
INNER JOIN posts
ON posts.id = comments.post_id
GROUP BY post.user_id
) as popularity
ON popular_authors.user_id = user.id
ORDER BY popularity.comments_no DESCWe will create a simple Query object for running this query:
class PopularAuthors < Struct.new(:limit)
def fetch
User.joins(join_clause)
.select('users.*, popularity.comments_no')
.order('popularity.comments_no DESC')
.take(limit)
end
private
def join_clause
@join_clause ||= begin
users = User.arel_table
users.join(popularity).on(users[:id].eq(popularity[:user_id]))
end
end
def popularity
@popularity ||= begin
comments = Comment.arel_table
posts = Post.arel_table
comments.join(posts).on(posts[:id].eq(comments[:post_id]))
.project(posts[:user_id], comments[:id].count.as('comments_no'))
.group(posts[:user_id])
.as('popularity')
end
end
endAs you see, we are creating a `popularity` table which is joined with the `users` table and provides `comments_no` to then order the results.
A case that even Arel cannot solve
There is an interesting case which even Arel is not able to solve (unfortunately!). Imagine once again that you have both `users` and `posts` models:
class User < ActiveRecord::Base
has_many :posts, inverse_of: :user, dependent: :destroy
end
class Post < ActiveRecord::Base
belongs_to :user, inverse_of: :posts
endand you would like to load all `users` - and then preload only a subset of each user’s `posts` (based on some runtime conditional):
User.includes(:posts).where('posts.created_at > ?', Date.new(2017, 11))Unfortunately the code above won’t work, because it will use an `INNER JOIN` for fetching data - so it will only load those users who created a post in November. That isn’t what we want. We want to include all users - even those not active in November - but only load posts for each user if they were created in November.
Switching back to `joins` also won’t work, because it will fetch *all* `posts` when we call the method on the `User` instance (`joins` does not assign results of the join to associations).
> users = User.joins("LEFT OUTER JOIN posts ON posts.user_id = users.id AND posts.created_at > '2017-11-01'")
> user = users.first
> user.posts.loaded?
=> false
> user.first.posts # it fires a query to get all time sessionsWe can try to break the nasty manual join with Arel (which can be useful if you are dealing with complex joins and multiple conditions):
users = User.arel_table
posts = Post.arel_table
on_clause = Arel::Nodes::Equality.new(users[:id], posts[:user_id])
and_clause = time_sessions[:created_at].gt(Date.new(2017, 11))
join_clause = users.join(time_sessions, Arel::Nodes::OuterJoin).on(on_clause.and(and_clause)).join_sources
User.joins(join_clause)but it won’t solve the original problem, since we still leave managing association to Rails.
The only solution we are aware of relies on combining associations manually (ick, but works):
posts = Post.where('created_at > ?', Date.new(2017, 11))
users = User.all.each do |user|
records = posts.select { |p| p.user_id == user.id }
association = user.association(:posts)
association.loaded!
association.target.concat(records)
records.each { |record| association.set_inverse_instance(record) }
endKeep in mind this solution is not efficient if you are working on a large dataset, as it loads all objects into memory - so it’s good to perform some initial filtering if you don’t want to be waiting around for a lifetime to get the results.
This kind of solution is used by the active_loaders gem (with the `loaded` method). You can read more about this problem in the post from active_loaders author.
We know all about creating complex SQL queries and making different queries across different databases - that’s why we know Arel! If you have any complex database operations that you need solving or implementing, would like help in migrating to a different database, or making your own queries database agnostic then reach out to the experts at iRonin. We would love to help out!
