How to execute Arel queries
Sometimes Active Record hits a limit. Plenty of resources (such as this article or this talk) will advise you to use Arel, but what struck me most when I tried to use arel was that I could not find an explicit guide on how to use the queries I could build with Arel. They all just provided the Arel query and printed to_sql to prove that was the right query. This small article is about two different ways to execute your Arel query. It assumes you have already read about Arel and how to make complex queries, but are stuck at how to execute them.
Under Active Record
Active Record internally relies on Arel and the most common way to use Arel is actually to use it with Active Record. For simple queries that can be directly translated to a list of model (such as Active Record usually returns), you will directly get results as a usual active record query.
For example, if we have a Cat model, we can do:
cat_arel = Cat.arel_table
soft_fur = Cat.where(cat_arel[fur_quality].gt(5))This is mostly used when your Arel statement translate in a where condition
Directly as SQL query
Sometimes, you want to optimize your queries in such a way that it will not return an ApplicationRecord model. In these case, you have to execute raw sql. To do that, simply translate your query to sql with the to_sql method and execute it.
ActiveRecord::Base.connection.exec_query(my_query.to_sql)
You can then go through the entries (list of hashes) and check for the result. Given this particular format, I advise you to use aliases in the query for easier manipulation.
Let’s try with a case I encountered. I want to get both the minimum and maximum value of a column, and I want to send only one query.
cat_arel = Cat.arel_tablequery = cat_arel.project(cat_arel[:price].minimum.as(‘min’), cat_arel[:price].maximum.as(‘max’)).to_sqlrecords = ActiveRecord::Base.connection.exec_query queryputs records.first>>> {“min”=>0.0, “max”=>72.0}
Note that if I did not aliased min and max, the keys of the record would have been {“MIN(`cats`.`price`)”=>0.0, “MAX(`cats`.`price`)”=>72.0}, which is not very nice to handle. If your query is expected to return multiple rows, simply iterate over the result of exec_query.
Wrapping up
That’s it. Arel allows you to make complexe queries that ActiveRecord cannot do. If the result is still something that ActiveRecord can treat and parse, you are in luck and can simply insert your arel bits into a classical ActiveRecord query. Or if you are doing something more complex, that will not return a model, simply execute sql prepared by arel and do whatever you need to with the result.
We are recruiting, apply if you like the way we work
