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
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
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
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
authors that fulfill our specified conditions.