Converting SQL Queries to Arel

※

My big project at work this year is to upgrade our codebase from Rails 4 to Rails 5 (and hopefully Rails 6 if we have time). The biggest obstacle is that we rely heavily on the Squeel gem; a gem that was abandoned 5 years ago and isn’t compatible with Rails 5. There is a newer alternative called Baby Squeel that doesn’t tie into the internals of ActiveRecord, but it is also undermaintained. My team concluded that our best option is to rewrite everything using only ActiveRecord and the Arel that underlies it. Unfortunately, I’ve found that Arel is underdocumented, so I’m sharing what I’ve learned here.

First, I want to thank Cameron Dutro for the scuttle.io website and the Arel Helpers gem. We decided to just write our own helpers rather than add a gem dependency to our codebase, but the gem was still helpful. I’m using this method a lot.

application_record.rb
1
2
3
4
5
6
7
8
9
10

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  def self.[](column = nil)
    return arel_table if column.nil?

    arel_table[column]
  end
end

We have an extensive GraphQL API and these are the query parts I had to figure out.

EXISTS
1
2
3
4
5
6
7
8
9

  Foo.where(subquery.arel.exists)
  # example:
  Supplier.where(
    Product.select(:id).where(
      Product[:supplier_id].eq(Supplier[:id]).and(Product[:price].lt(20))
    ).arel.exists
  )
  # SELECT * FROM suppliers WHERE EXISTS(SELECT id FROM products WHERE products.supplier_id = suppliers.id AND products.price < 20)
NOT EXISTS
1
2
3
4
5
6
7
8
9

  Foo.where(subquery.arel.exists.not)
  # example:
  Supplier.where(
    Product.select(:id).where(
      Product[:supplier_id].eq(Supplier[:id]).and(Product[:price].lt(20))
    ).arel.exists.not
  )
  # SELECT * FROM suppliers WHERE NOT EXISTS(SELECT id FROM products WHERE products.supplier_id = suppliers.id AND products.price < 20)
IN
1
2
3
4
5

  Foo.where(bar: ['baz', 'qux'])
  # example:
  Client.where(zipcode: ['84720', '84113'])
  # SELECT * FROM clients WHERE zipcode IN ('84720', '84113')
NOT IN
1
2
3
4
5

  Foo.where.not(bar: ['baz', 'qux'])
  # example:
  Client.where.not(zipcode: ['84720', '84113'])
  # SELECT * FROM clients WHERE zipcode NOT IN ('84720', '84113')
LIKE
1
2
3
4
5
6

  Foo.where(Foo[:bar].matches(baz))
  # example:
  search_string = 'Jo'
  Client.where(Client[:first_name].matches(search_string + '%'))
  # SELECT * FROM clients WHERE first_name LIKE 'Jo%'
NOT LIKE
1
2
3
4
5

  Foo.where(Foo[:bar].does_not_match(pattern))
  # example:
  Client.where(Client[:email].does_not_match('%@example.com'))
  # SELECT * FROM clients WHERE email NOT LIKE '%@example.com'
ILIKE (case-insensitive)
1
2
3
4
5

  Foo.where(Foo[:bar].matches(pattern, nil, true))
  # example:
  Client.where(Client[:name].matches('%smith%', nil, true))
  # SELECT * FROM clients WHERE name ILIKE '%smith%' (PostgreSQL)
IS NULL
1
2
3
4
5

  Foo.where(Foo[:bar].eq(nil))
  # example:
  Client.where(Client[:address].eq(nil))
  # SELECT * FROM clients WHERE address IS NULL
IS NOT NULL
1
2
3
4
5

  Foo.where(Foo[:bar].not_eq(nil))
  # example:
  Client.where.not(Client[:address].eq(nil))
  # SELECT * FROM clients WHERE address IS NOT NULL
BETWEEN
1
2
3

  Client.where(created_at: Date.yesterday..Date.tomorrow)
  # SELECT * FROM clients WHERE created_at BETWEEN '2020-01-07' AND '2020-01-09'
OR
1
2
3
4
5

  Foo.where(Foo[:bar].eq('a').or(Foo[:baz].eq('b')))
  # example:
  Client.where(Client[:status].eq('active').or(Client[:vip].eq(true)))
  # SELECT * FROM clients WHERE status = 'active' OR vip = true
GREATER THAN / LESS THAN
1
2
3
4
5
6
7
8

  Foo.where(Foo[:bar].gt(value))   # >
  Foo.where(Foo[:bar].gteq(value)) # >=
  Foo.where(Foo[:bar].lt(value))   # <
  Foo.where(Foo[:bar].lteq(value)) # <=
  # example:
  Product.where(Product[:price].gteq(100).and(Product[:price].lteq(500)))
  # SELECT * FROM products WHERE price >= 100 AND price <= 500
ORDER BY
1
2
3
4
5
6

  Foo.order(Foo[:bar].asc)
  Foo.order(Foo[:bar].desc)
  # example:
  Client.order(Client[:created_at].desc, Client[:name].asc)
  # SELECT * FROM clients ORDER BY created_at DESC, name ASC
INNER JOIN
1
2
3
4
5
6
7
8
9
10
11

  Foo.joins(:bars) # simple association
  # custom join condition:
  Foo.joins(Foo.arel_table.join(Bar.arel_table).on(
    Foo[:bar_id].eq(Bar[:id])
  ).join_sources)
  # example:
  Client.joins(Client.arel_table.join(Order.arel_table).on(
    Client[:id].eq(Order[:client_id]).and(Order[:status].eq('active'))
  ).join_sources)
  # SELECT * FROM clients INNER JOIN orders ON clients.id = orders.client_id AND orders.status = 'active'
LEFT JOIN
1
2
3
4
5
6
7
8
9
10
11

  Foo.left_joins(:bars) # simple association
  # custom join condition:
  Foo.joins(Foo.arel_table.join(Bar.arel_table, Arel::Nodes::OuterJoin).on(
    Foo[:bar_id].eq(Bar[:id])
  ).join_sources)
  # example:
  Client.joins(Client.arel_table.join(Order.arel_table, Arel::Nodes::OuterJoin).on(
    Client[:id].eq(Order[:client_id])
  ).join_sources).where(Order[:id].eq(nil))
  # SELECT * FROM clients LEFT OUTER JOIN orders ON clients.id = orders.client_id WHERE orders.id IS NULL

Thanks for reading!