Home >

Advanced Arel Cheat Sheet

11 Aug 2015

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.