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.

User.where(name: "Bob")
# SELECT "users".* FROM "users" WHERE "users"."name" = 'Bob'
User.where.not(job_title: nil)
# SELECT "users".* FROM "users" WHERE ("users"."job_title" IS NOT NULL)

However, when you have to do a greater-than or less-than query, most developers tend to do this:

User.where("created_at > ?", 1.day.ago)
# SELECT "users".* FROM "users" WHERE (created_at > '2016-09-20 00:09:33.360669')
User.where("updated_at <= ?", 2.hours.ago)
# SELECT "users".* FROM "users" WHERE (updated_at <= '2016-09-20 22:09:56.173574')

User.where(User.arel_table[:created_at].gt(1.day.ago))
# SELECT "users".* FROM "users" WHERE ("users"."created_at" > '2016-09-20 00:09:33.360669')
User.where(User.arel_table[:created_at].lteq(2.hours.ago))
# SELECT "users".* FROM "users" WHERE ("users"."created_at" <= '2016-09-20 22:09:56.173574')

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:

User.joins(:devices).where("created_at > ?", 1.day.ago)
# ActiveRecord::StatementInvalid: PG::AmbiguousColumn: ERROR:  column reference "created_at" is ambiguous

You could avoid this by manually specifying the table name, eg.

User.joins(:devices).where("users.created_at > ?", 1.day.ago)

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.

OR queries

ActiveRecord doesn’t have great support for OR statements - in fact it doesn’t even get a mention in the query interface documentation.

User.where("name = :query OR surname = :query", query: "Bob")
# SELECT "users".* FROM "users" WHERE (name = 'Bob' OR surname = 'Bob')
User.where("(name = :query AND surname IS NULL) OR (name IS NULL AND surname = :query)", query: "Bob")
# SELECT "users".* FROM "users" WHERE ((name = 'Bob' AND surname IS NULL) OR (name IS NULL AND surname = 'Bob'))

arel = User.arel_table
User.where(arel[:name].eq("Bob").or(arel[:surname].eq("Bob")))
# SELECT "users".* FROM "users" WHERE ("users"."name" = 'Bob' OR "users"."surname" = 'Bob')

name_clause = arel[:name].eq("Bob").and(arel[:surname].eq(nil))
surname_clause = arel[:name].eq(nil).and(arel[:surname].eq("Bob"))
User.where(name_clause.or(surname_clause))
# SELECT "users".* FROM "users" WHERE ("users"."name" = 'Bob' AND "users"."surname" IS NULL OR "users"."name" IS NULL AND "users"."surname" = 'Bob')

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:

arel = User.arel_table
name_clause = arel.grouping(arel[:name].eq("Bob").and(arel[:surname].eq(nil)))
surname_clause = arel.grouping(arel[:name].eq(nil).and(arel[:surname].eq("Bob")))
User.where(name_clause.or(surname_clause))
# SELECT "users".* FROM "users" WHERE (("users"."name" = 'Bob' AND "users"."surname" IS NULL) OR ("users"."name" IS NULL AND "users"."surname" = 'Bob'))

LIKE queries

LIKE queries are another area where the common solution is to write manual SQL.

User.where("lower(name) LIKE ?", "Bob".downcase)
# SELECT "users".* FROM "users" WHERE (lower(name) LIKE 'bob')

User.where(User.arel_table[:name].lower.matches("Bob".downcase))
# SELECT "users".* FROM "users" WHERE (LOWER("users"."name") ILIKE 'bob')

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.

User.where(User.arel_table[:name].lower.matches("%Bob%".downcase))

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.

sanitized_query = query.gsub(/[%_]/, '\\\\\0')

Futher reading

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.

Share on: Twitter, Facebook, Google+ or use the Permalink