Using Common Table Expression (CTE) with Rails ActiveRecord

Question

Common Table Expression is a fairly common practice in different RDBMS (PostgreSQL, MySQL, Oracle, SQLite3 etc.) to perform the same calculation multiple times over across multiple query components or for some other purposes

I found old gem postgres_ext with such functionality. But it is not maintained. And it is Postgres specific

There are some old questions about it, but they are about specific rails version or specific RDBMS or about Arel

Is it possible to use WITH clause in Rails using AR some common way?

You really mean "using the ActiveRecord query interface" which is the spiffy abstractions that it provides to generate SQL. Even when you write queries using Arel or SQL strings they are still going through AR. — max, Feb 09 '23 at 11:30
@max I think you understand what I'm talking about, I just didn't know how to phrase it better — mechnicov, Feb 09 '23 at 11:41

score 4 · Accepted Answer · answered Feb 09 '23 at 01:16

Rails 7.1 introduce with method

It returns ActiveRecord::Relation

Post.with(posts_with_tags: Post.where("tags_count > ?", 0))
# WITH posts_with_tags AS (
#   SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts

Once you define Common Table Expression you can use custom FROM value or JOIN to reference it

Post.with(posts_with_tags: Post.where("tags_count > ?", 0)).from("posts_with_tags AS posts")
# WITH posts_with_tags AS (
#  SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts_with_tags AS posts

Post.with(posts_with_tags: Post.where("tags_count > ?", 0)).joins("JOIN posts_with_tags ON posts_with_tags.id = posts.id")
# WITH posts_with_tags AS (
#   SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts JOIN posts_with_tags ON posts_with_tags.id = posts.id

It's possible to pass not only AR but also SQL literal using Arel.

NB: Great caution should be taken to avoid SQL injection vulnerabilities. This method should not be used with unsafe values that include unsanitized input

Post.with(popular_posts: Arel.sql("... complex sql to calculate posts popularity ..."))

To add multiple CTEs just pass multiple key-value pairs

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

or chain multiple .with calls

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

Using Common Table Expression (CTE) with Rails ActiveRecord

1 Answers1