A Common Table Expression (CTE) is a temporary named result set, derived from a simple query. CTEs help us write and maintain complex queries. Below are some of the use cases of CTEs.

  • When you need to reference a derived table multiple times in a single query.
  • When you need to organize long and complex queries.

How to create a CTE in SQL?

Initiate a CTE using WITH, then the name of the CTE followed by AS. Define the query within the bracket. Later reference the CTE in the subsequent query.

Let's take an example of a posts table that has many comments associated with it.

We will first create a CTE query that gives a list of posts with at least one comment. Then use this CTE to fetch the ID and NAME of the post. Since CTE is a temporary result set, we should always run the CTE query & the subsequent query together.

WITH posts_with_comments AS (
  SELECT * FROM posts WHERE comments_count > 0
)
SELECT id, name FROM posts_with_comments;

# Results

# | ID | NAME    |
# | 4  | apple   |
# | 3  | android |

How to create a CTE in Rails < 7.1?

In Rails < 7.1, we can use Arel to generate CTE. We will use the same example we used above. Let's first build the Arel table.

posts_table = Arel::Table.new(:posts)

Next, define the query which will act as a CTE. Note the usage of Arel.star, a convenient method for the * character. This CTE fetches all the posts having at least one comment.

posts_with_comments_expression = posts_table.where(posts_table[:comments_count].gt(0)).project(Arel.star)

Now build the subsequent query and attach the CTE to this subsequent query. Use Arel::Nodes::As to mimic the AS command and map CTE with Arel table posts.

query = Post.select(:id, :name).arel.with(Arel::Nodes::As.new(posts_table, posts_with_comments_expression)).to_sql
=> "WITH \"posts\" AS (SELECT * FROM \"posts\" WHERE \"posts\".\"comments_count\" > 0) SELECT \"posts\".\"id\", \"posts\".\"name\" FROM \"posts\""

Let's run this query from the rails console.

Loading development environment (Rails 7.0.3.1)
2.7.6 :001 > ActiveRecord::Base.connection.exec_query(query)
SQL (0.4ms)  WITH "posts" AS (SELECT * FROM "posts" WHERE "posts"."comments_count" > 0) SELECT "posts"."id", "posts"."name" FROM "posts"
=> #<ActiveRecord::Result:0x00007fa9840278c8 @columns=["id", "name"], @rows=[[4, "apple"], [3, "android"]], @hash_rows=nil, @column_types={}>

You can see how tedious it is to build a CTE using Arel.

How to create a CTE in Rails >= 7.1?

.with query method makes it super easy to build and chain complex CTE queries. Let's take the same example we used in the SQL segment. First, create a CTE query having a post with at least one comment.

Post.with(posts_with_comments: Post.where("comments_count > ?", 0))

.with is only one part of the equation. Once we have CTE results we also need to create a subsequent query that uses this CTE.

We can write a from query method that will alias the CTE table to the ActiveRecord table. Use the select method to create a subsequent query that fetches ID & NAME of the posts.

Loading development environment (Rails 7.1.0.alpha)
2.7.6 :001 > Post.with(posts_with_comments: Post.where("comments_count > ?", 0)).from("posts_with_comments AS posts").select("posts.id, posts.name")
 
# Results

=> #<ActiveRecord::Relation [#<Post id: 4, name: "apple">, #<Post id: 3, name: "android">]>

Rails 7.1 has made it easy for us to write complex queries. Currently, CTEs are only supported with the following database adapters:

  • MariaDB (version 10.2.1 and above)
  • MySQL (version 8.0.1 and above)
  • PostgreSQL (all supported versions)
  • SQLite (version 3.8.3 and above)

So please check your database adapter version before using CTE in Rails.

References