Most Rails developers are familiar with ActiveRecord queries, but not so much with Arel. Arel allows us to really customize our queries and gives us control over our queries without having to do manual SQL and string interpolation. While Arel is a very powerful and feature-rich technology, I’m going to focus and the features that you use most often.
Greater-than or Less-than queries
The syntax for performing a regular equals or not-equals query is well known.
However, when you have to do a greater-than or less-than query, most developers tend to do this:
Since the generated SQL similar, it might not seem like a big deal. However, it becomes problematic when we’re joining to other tables and the columns we’re filtering on are no longer unique. For example, assuming the
devices table has timestamps columns:
You could avoid this by manually specifying the table name, eg.
But this will still fail if you’re joining to the same table multiple times, and it’s also messy to specify the table name inside your custom scopes. It also makes it more error-prone to rename tables.
ActiveRecord doesn’t have great support for
OR statements - in fact it doesn’t even get a mention in the query interface documentation.
Note that in our last example Arel didn’t add any parenthesis around the 2
AND statements - this is technically still correct since
AND has a higher operator precedence than
OR. However, if you want to be able to specify the parenthesis you can still do this:
LIKE queries are another area where the common solution is to write manual SQL.
Notice that the generated SQL used an
ILIKE query, which is a PostgreSQL-specific extension.
Unfortunately Arel doesn’t have support for wildcard (eg.
%) characters - which is a pity. It seems like this used to be a feature and then got reverted, with a very long and fierce comment chain. So in the meantime you need to specify wildcard characters manually.
This also means you need to escape any SQL-specific characters in user-specified queries - which is really messy, but you need to do that regardless of using Arel or not.
Arel has an enormous amount of features, but unfortunately it’s not well-documented. For the most part Stackoverflow has you covered with the most common questions, but The Definitive Guide to Arel is also a good read. Lastly you can always look at the source code.
Overall Arel is a great tool for abstracting your queries and future-proofing your code. As long as you’re using Arel ActiveRecord will always end up doing the right thing. Happy coding.