ruby-on-railsrails-activerecordcommon-table-expression

Using Common Table Expression (CTE) with Rails ActiveRecord


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?


Solution

  • 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