Guide for Using ARel in Rails Where ActiveRecord Doesn't Cut It
ARel is a database agnostic library that ActiveRecord record uses under the covers. It can be used to generate advanced SQL queries.
ActiveRecord Public API
ActiveRecord handles majority of the use-cases pretty well. for example, you want to use ActiveRecord for:
Searching for equality
> Post.where title: 'hello'
# SELECT "posts".* FROM "posts" WHERE "posts"."title" = 'hello'
Searching for NULLs
> Post.where title: nil
# SELECT "posts".* FROM "posts" WHERE "posts"."title" IS NULL
Searching for either of given values
suppose I want to search where title is ‘hello’ OR ‘hi’ OR ‘howdy’. pass all options as an array works
> Post.where title: ['hello', 'hi', 'howdy']
# SELECT "posts".* FROM "posts" WHERE "posts"."title" IN ('hello', 'hi', 'howdy')
Searching for either given values or NULLs
Post.where title: ['hello', 'hi', 'howdy', nil]
# SELECT "posts".* FROM "posts" WHERE (("posts"."title" IN ('hello', 'hi', 'howdy') OR "posts"."title" IS NULL))
Searching for Range, where values are BETWEEN given starting & end values
suppose I want to search all posts published in last 2months
> Post.where publishing_date: 2.months.ago..Date.today
# SELECT "posts".* FROM "posts" WHERE ("posts"."publishing_date" BETWEEN '2014-04-09 04:25:23.704466' AND '2014-06-09')
Searching for NOT
Starting Rails 4, ActiveRecord has not
which will negate the query, so all of the above queries when appended with not
, will work like this:
> Post.where.not(title: 'hello')
# SELECT "posts".* FROM "posts" WHERE ("posts"."title" != 'hello')
> Post.where.not(title: nil)
# SELECT "posts".* FROM "posts" WHERE ("posts"."title" IS NOT NULL)
> Post.where.not title: ['hello', 'hi', 'howdy']
# SELECT "posts".* FROM "posts" WHERE ("posts"."title" NOT IN ('hello', 'hi', 'howdy'))
> Post.where.not title: ['hello', 'hi', 'howdy', nil]
# SELECT "posts".* FROM "posts" WHERE (NOT (("posts"."title" IN ('hello', 'hi', 'howdy') OR "posts"."title" IS NULL)))
> Post.where.not publishing_date: 2.months.ago..Date.today
# SELECT "posts".* FROM "posts" WHERE (NOT ("posts"."publishing_date" BETWEEN '2014-04-09 04:58:40.232850' AND '2014-06-09'))
What about queries with that require LIKE
, <
(less than), >
(greator than), <=
(less than equal to), >=
(greator than equal to) operators?
As you might have guessed ActiveRecord doesn’t support generating queries with above operators, so what you do?
More operators that you might need:
While working with SQL databases, you might feel the need to use the following operators.
- LIKE: used when to search a specific pattern
<
(less than): Less than>
(greator than): Greater than<=
(less than equal to): Less than or equal>=
(greator than equal to)`: Greater than or equal
But ActiveRecord doesn’t support them. you could use “strings”. But what if your queries need certain amount of conditional in ruby? Enter ARel - ActiverRecord’s private API
Arel methods/predicates that are available for above 5operators
- Like:
matches
- Less than:
lt
- Greater than:
gt
- Less than or equal:
lteq
- Greater than or equal:
gteq
To be correct, there are more but they are rarely used.
Before we see each of the methods in details, lets first look on how to install/use ARel gem.
How do I install ARel?
if you are using Rails/ActiveRecord, its already installed & configured! Every ActiveRecord model is backed by an Arel Table. try calling arel_table
method on any ActiveRecord model. Object retuned will be instance of Arel::Table
as shown in the example below.
> arel = Post.arel_table
=> #<Arel::Table:0x00000101740160 @name="posts", @engine=Post(id: integer, title: string, body: text, publishing_date: date, published: boolean, created_at: datetime, updated_at: datetime), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>
Examples
Match/Like operator
Use this when you want to match a part of the string, so instead of this:
Post.where('title like ?', '%welcome%')
# SELECT "posts".* FROM "posts" WHERE (title like '%welcome%')
you can do this
Post.where arel[:title].matches('%welcome%') #arel = Post.arel_table
Will skip string examples for brevity’s sake
Less Than Operator - lt
Post.where(arel[:created_].lt 1.day.ago).to_sql
"SELECT `posts`.* FROM `posts` WHERE (`posts`.`created_at` < '2015-09-26 16:19:31')"
Greater Than Operator - gt
Post.where(arel[:created_].gt 1.day.ago).to_sql
"SELECT `posts`.* FROM `posts` WHERE (`posts`.`created_at` > '2015-09-26 16:19:31')"
Less than or equal Operator - lteq
Post.where(arel[:created_].lteq 1.day.ago).to_sql
"SELECT `posts`.* FROM `posts` WHERE (`posts`.`created_at` <= '2015-09-26 16:19:31')"
Greater than or equal Operator - gteq
Post.where(arel[:created_].gteq 1.day.ago).to_sql
"SELECT `posts`.* FROM `posts` WHERE (`posts`.`created_at` >= '2015-09-26 16:19:31')"
Real world Use-Case
Consider the following AR scope
String version
scope : find_valid_phones_to_whom_we_can_text, -> { where(duplicate: false)
.where("notification_type = 'mobile' OR notification_type = 'phone' OR notification_type = 'office_phone'")} do
# fetches only those records whose contact number is valid
def whitelisted
# contact number should not start from 800, (800)
query = []
# an array of numbers where we don't want to send sms
BLACKLISTED_NUMBER_PREFIXES.each do |prefix|
if query.empty?
query << "(contact NOT LIKE '#{prefix}%' AND contact NOT LIKE '(#{prefix})%')"
else
query << "(contact NOT LIKE '#{prefix}%' AND contact NOT LIKE '(#{prefix})%')"
end
end
# contact number should not be more than 10 characters
query << "NOT length(contact) > 10"
where(query.join(" AND "))
end
end
Arel Version
scope : find_valid_phones_to_whom_we_can_text, -> { where(duplicate: false)
.where(notification_type: ['mobile', 'phone', 'office_phone']) } do
# fetches only those records whose contact number is valid
def whitelisted
contact = Notification.arel_table[:contact]
# contact number should not be more than 10 characters
has_valid_length = Arel::Nodes::NamedFunction.new('length', [contact]).lteq(10)
# contact number should not start from 800, 888 etc
where( contact.does_not_match_any(BLACKLISTED_NUMBER_PREFIXES)
.and( has_valid_length ))
end
end
As you can see above, Arel version reads better.
Wrapping Up
Arel full fledged query generator which generate any SQL query. So question is should you use it?
Like all things in computer science. the answer is it depends on the use-case. Here is how I like to think about it:
- SQL that ActiveRecord can generate: Use ActiveRecord.
- SQL that ActiveRecord can’t generate but doesn’t have dynamic conditional logic. Use Strings.
- SQL that ActiveRecord can’t generate & also have dynamic conditional logic based on paremeters: Use AREL as shown in the above real-world example
To conclude, I would say Arel is tool that every rails developer should have in their toolbox.
Lastly, This is a blog post written after an year’s gap. so any feedback/comments/questions are welcome by tweeting to me at @gaurish