After seeing @camertron's talk on Advance Arel from Rails Conf 2014 (slides) I've wanted a simple reference for the most common Arel comparators that I forget to use instead of string interpolation.
This post was motivated by a recent bug I found in one of my applications where using string interpolation with joined relations caused an ambiguous column SQL error. While this post is intended to serve as a personal reference, it may be useful to others as well.
Also check out scuttle.io as a further resource to translate SQL to Arel.
Note Arel is considered an internal API for ActiveRecord and can change between major Rails versions.
Setup
My examples assume a Rails 4.2 application and a single Post model with 2 attributes title and published_date, gist.
Note that pulling in the arel-helpers gem can eliminate the need to keep calling arel_table all over the place and adds some potentially useful join helpers.
Equality
Greater than
Post.where(
Post.arel_table[:published_date].gt(Date.new(2015, 8, 11))
)
# instead of
Post.where('published_date > ?', Date.new(2015, 8, 11))
Less than
Post.where(
Post.arel_table[:published_date].lt(Date.new(2015, 8, 11))
)
# instead of
Post.where('published_date < ?', Date.new(2015, 8, 11))
Greater than or equal
Post.where(
Post.arel_table[:published_date].gteq(Date.new(2015, 8, 11))
)
# instead of
Post.where('published_date >= ?', Date.new(2015, 8, 11))
Less than or equal
Post.where(
Post.arel_table[:published_date].lteq(Date.new(2015, 8, 11))
)
# instead of
Post.where('published_date <= ?', Date.new(2015, 8, 11))
Not equal
Post.where(Post.arel_table[:title].not_eq('Sample Post'))
# instead of
Post.where('title != ?', 'Sample Post')
Matching / (I)LIKE
Post.where(Post.arel_table[:title].matches('%sample%'))
# instead of
Post.where('title ILIKE ?', '%sample%')
Ordering
Post.order(Post.arel_table[:publish_date].desc)
# instead of
Post.order('publish_date DESC')
If you are looking for even more flexibility and control than what ActiveRecord provides, I would highly recommend the sequel gem.
Have other tricks you use with ActiveRecord? Tweet at me @calebwoods.