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

• How do you use the postgresql WITH in activerecord?

• Rails 5.2.2 (active record) WITH statement

• Postgres Common Table Expression query with Ruby on Rails

• Arel and CTE to Wrap Query

• Multiple CTEs with Arel

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

Answer 1

After this pull request Rails 7.1 introduce with method that can take few arguments

Let's assume we have books table with integer reviews_count column. To define and use CTE you can apply ActiveRecord::QueryMethods#with such way:

Book.with(books_with_reviews: Book.where("reviews_count > ?", 0))

# WITH books_with_reviews AS (
#   SELECT * FROM books WHERE (reviews_count > 0)
# )
# SELECT * FROM books

It returns ActiveRecord::Relation object, which makes its use very convenient and flexible

For example, after defining a Common Table Expression, it's possible to use name of auxiliary statement with specified FROM clause or JOIN statement:

Book
  .with(books_with_reviews: Book.where("reviews_count > ?", 0))
  .from("books_with_reviews AS books")

# WITH books_with_reviews AS (
#  SELECT * FROM books WHERE (reviews_count > 0)
# )
# SELECT * FROM books_with_reviews AS books

Book
  .with(books_with_reviews: Book.where("reviews_count > ?", 0))
  .joins("JOIN books_with_reviews ON books_with_reviews.id = books.id")

# WITH books_with_reviews AS (
#   SELECT * FROM books WHERE (reviews_count > 0)
# )
# SELECT * FROM books JOIN books_with_reviews ON books_with_reviews.id = books.id

It's also possible to pass SQL query using Arel.sql method:

Book.with(popular_books: Arel.sql("some SQL literals here"))

Important note: double check such arguments to prevent SQL injection vulnerabilities, this approach must not be used with unsafe values, especially those containing unsanitized input

To define multiple CTEs just pass few hashes as arguments:

Book.with(
  books_with_reviews: Book.where("reviews_count > ?", 0),
  books_with_ratings: Book.where("ratings_count > ?", 0)
)

# WITH books_with_reviews AS (
#   SELECT * FROM books WHERE (reviews_count > 0)
# ), books_with_ratings AS (
#   SELECT * FROM books WHERE (ratings_count > 0)
# )
# SELECT * FROM books

Since with returns relation, you can simply chain it multiple times:

Book
  .with(books_with_reviews: Book.where("reviews_count > ?", 0))
  .with(books_with_ratings: Book.where("ratings_count > ?", 0))

# WITH books_with_reviews AS (
#   SELECT * FROM books WHERE (reviews_count > 0)
# ), books_with_ratings AS (
#   SELECT * FROM books WHERE (ratings_count > 0)
# )
# SELECT * FROM books

This pull request introduced WITH RECURSIVE feature

It is also possible to pass subquery array (will be merged using UNION ALL)

Book.with_recursive(
  books_with_inspirings: [
    Book.where(state: :sold),
    Book.joins('JOIN books_with_inspirings ON books.inspired_by_book_id = books_with_inspirings.id'),
  ]
)

# WITH RECURSIVE books_with_inspirings AS (
#   (SELECT * FROM books WHERE books.state = 'sold')
#   UNION ALL
#   (SELECT * FROM books JOIN books_with_inspirings ON books.inspired_by_book_id = books_with_inspiring.id)
# )
# SELECT * FROM books