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 my findings 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.

active_record_extensions.rb
1
2
3
4
5
6
7
8

class ActiveRecord::Base
  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.exists)
  # example:
  Supplier.where(
    Product.select(:id).where(
      Product[:supplier_id].eq(Supplier[:id]).and(Product[:price].lt(20))
    ).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.exists.not)
  # example:
  Supplier.where(
    Product.select(:id).where(
      Product[:supplier_id].eq(Supplier[:id]).and(Product[:price].lt(20))
    ).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%'
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].eq(nil))
  # example:
  Client.where.not(Client[:address].eq(nil))
  # SELECT * FROM clients WHERE address IS NOT NULL
BETWEEN
1
2
3
4
5
6
7

  from = Date.yesterday
  to = Date.tomorrow
  Client.where(Arel::Nodes::Between.new(
    Client[:created_at], Arel::Nodes.build_quoted(from).and(Arel::Nodes.build_quoted(to))
  ))
  # SELECT * FROM clients WHERE created_at BETWEEN '2020-01-07' AND '2020-01-09'

Thanks for reading!