Arel, part II: Common table expressions - rails

This post is the second entry in our series on Arel. For Part I, see here.

Roughly a year ago, a requirement for one of our client projects led us to create a database view that collated data from almost every table in our database; naturally, generating this view required a relatively complex query. Our first draft of this view contained a lot of complex WHERE and JOIN conditions. While testing, we decided that we wanted to speed up our queries against this view. With the help of Andres, one of our resident SQL gurus, we managed to speed up our view by several orders of magnitude by moving some of the logic in our view into common table expressions (CTEs). For anyone relatively unfamiliar with CTEs, you can think of them as a sort of named, temporary table that exists within the current query. Read more about them here or here.

While we didn't use Arel to construct the aforementioned view, it provided a great example of the power behind CTEs. Even though ActiveRecord doesn't give us CTE support out of the box, we can craft our own CTEs with Arel (or you can use the postgres_ext gem if you're using Postgres).

For the purposes of this exercise, imagine that we have the following models:

class Recipe < ActiveRecord::Base
  belongs_to: :author
end
class Author < ActiveRecord::Base
  has_many :recipes
end

Both models have a primary key of id, as usual. The recipes table also has an integer column called rating that represents the quality of the recipe as well as the standard author_id foreign key. For this exercise, our CTE will represent recipes whose rating is between 60 and 80.

To start off, we need to instantiate a new Arel::Table. We'll pass in a symbol representing the name that we want our CTE to be referenced by in our final query as the only argument.

cte_table = Arel::Table.new(:recipes_rated_between_60_and_80)

Now, we have to create the definition of our CTE.

recipes = Recipe.arel_table
cte_definition = recipes
                   .project(recipes[:id], recipes[:author_id])
                   .where(recipes[:rating].between(60..80))

Lastly, we need to combine our cte_table and our cte_definition in an Arel::Nodes::As instance.

composed_cte = Arel::Nodes::As.new(cte_table, cte_definition)

We can now use our CTE in other queries. For example, if we wanted to grab all the authors who have written a recipe with a rating between 60 and 80, we could do the following:

authors = Author.arel_table
author_ids = authors
              .project(authors[:id])
              .join(cte_table).on(authors[:id].eq(cte_table[:author_id]))
              .with(composed_cte)
Author.where(authors[:id].in(author_ids))

This leads to Rails executing the following SQL query:

SELECT "AUTHORS".*
FROM "AUTHORS"
WHERE "AUTHORS"."ID" IN (
  WITH "RECIPES_RATED_BETWEEN_60_AND_80" AS (
    SELECT "RECIPES"."ID", "RECIPES"."AUTHOR_ID"
    FROM "RECIPES"
    WHERE "RECIPES"."RATING"
    BETWEEN 60 AND 80
  )
  SELECT "AUTHORS"."ID"
  FROM "AUTHORS"
  INNER JOIN "RECIPES_RATED_BETWEEN_60_AND_80"
          ON "RECIPES_RATED_BETWEEN_60_AND_80"."AUTHOR_ID" = "AUTHORS"."ID"
)

Thanks to ActiveRecord and Arel, we now have an ActiveRecord::Relation of authors that fulfill our specified conditions.