Why do you need Arel?

In my opinion Arel is one of the greatest and most underestimated gems. Of course today Arel is also one of the most used gems because it’s shipped in Rails, but how many people use it actively? And why would they?

Since Rails 3 Arel is the base block of ActiveRecord. Every time you pass a hash to where, it goes through Arel eventually. Rails exposes this with a public API that we can use when we have to write complex queries.

So, if without Arel we would have the following code:

def visible_posts
  Post.where 'owner_id = ? OR public = ?', id, true
end

With Arel we can write:

def visible_posts
  Post.where owned_posts.or public_posts
end

private

def table
  Post.arel_table
end

def owned_posts
  table[:owner_id].eq id
end

def public_posts
  table[:public].eq true
end

So yeah, code improved… in a certain way.

But say we have a much more complex scenario:

And say we want to know how many products colored red have been bought at least 3 times in UK during this month.

With plain ActiveRecord you would do something like:

start_of_month = Time.now.beginning_of_month
product_ids = OrderItem.
  joins(:color, order: :address).
  group(:product_id).where('created_at > ?',  start_of_month).
  having('count(order_items.id) >= 3').
  where(addresses: { country: 'UK' }, colors: { code: 'red' }).
  select('product_id')
Product.where(id: product_ids)

That generates the following SQL query:

SELECT "products".*
FROM "products" 
WHERE "products"."id" IN (
  SELECT "order_items"."product_id" 
  FROM "order_items" 
  INNER JOIN "colors" 
    ON "colors"."id" = "order_items"."color_id" 
  INNER JOIN "orders" 
    ON "orders"."id" = "order_items"."order_id" 
  INNER JOIN "addresses" 
    ON "addresses"."id" = "orders"."address_id" 
  WHERE 
    (created_at > '2016-01-31 23:00:00.000000') AND 
    "colors"."code" = "red" AND 
    "addresses"."country" = 'UK' 
  GROUP BY "order_items"."product_id" 
  HAVING count(order_items.id) >= 3)

It’s ugly and not so performant as you may think (due to WHERE IN).

With Arel you can instead write something like:

def export
  Product.find_by_sql subset.
    join(products).on(products[:id].eq order_items[:product_id]).
    project(products[Arel.star])
end

def subset
  order_items.
    join(colors).on(colors[:id].eq order_items[:color_id]).
    join(orders).on(orders[:id].eq order_items[:order_id]).
    join(addresses).on(addresses[:id].eq orders[:address_id]).
    group(order_items[:product_id]).
    having(order_items[:id].count.gteq 3).
    where(red_colored.and during_this_month)
end

def products
  Product.arel_table
end

def order_items
  OrderItem.arel_table
end

def orders
  Order.arel_table
end

def colors
  Color.arel_table
end

def addresses
  Address.arel_table
end

def red_colored
  colors[:code].eq 'red'
end

def during_this_month
  orders[:created_at].gteq Time.now.beginning_of_month
end

That generates:

SELECT "products".* 
FROM "order_items" 
INNER JOIN "colors" 
  ON "colors"."id" = "order_items"."color_id" 
INNER JOIN "orders" 
  ON "orders"."id" = "order_items"."order_id" 
INNER JOIN "addresses" 
  ON "addresses"."id" = "orders"."address_id" 
INNER JOIN "products" 
  ON "products"."id" = "order_items"."product_id" 
WHERE 
  "colors"."code" = 'red' AND 
  "orders"."created_at" >= '2016-01-31 23:00:00.000000' 
GROUP BY "order_items"."product_id" 
HAVING COUNT("order_items"."id") >= 3

Much better, even from the performances point of view.

Now, think about how many medium sized applications with a backend full of reports and exports are out there, and think about how many times you saw Arel in action. It’s easy to realize that most developers are underestimating Arel and they shouldn’t.