ActiveRecordでCTEを使ってクエリを分割して個別にテストする

Rails 7.1 以降では、任意の ActiveRecord Relation をCTEとして使用することができます。 共通テーブル式（CTE）を使うと、複雑なクエリ名前付きの一時結果セットに分割でき、クエリの可読性を高めるのに役立ちます。ActiveRecordでは .with クエリーメソッドに任意の ActiveRecord Relation を渡すことでCTEを定義できます。これは個々の結果セットをテスト可能になることを意味します。複雑なクエリを分割することで、テストしやすくなり、保守性を高めることにもつながります。ただし現時点では再帰的に利用することはできません。

https://gihyo.jp/article/2022/10/mysql-rcn0181

共通テーブル式の例

「レビューの平均スコアが4以上のユーザーを検索する」場合、次の様に書くことができます。

SQL

WITH
  average_scores AS (
    SELECT reviewee_id, AVG(score) average_score
    FROM user_reviews
    GROUP BY reviewee_id
  )
SELECT users.*
FROM users
JOIN average_scores ON users.id = average_scores.reviewee_id
WHERE average_score >= 4

ActiveRecord

ActiveRecord Relation を .with メソッドに名前を付けて渡し、それを .join で結合します。

average_scores =
  UserReview.group(:reviewee_id).select(:reviewee_id, Arel.sql("AVG(score) average_score"))
User
  .with(average_scores:)
  .joins("JOIN average_scores ON users.id = average_scores.reviewee_id")
  .where("average_score >= ?", 4)

https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-with

生のSQLから定義することもできる（推奨されない）

.with に文字列を渡すことで生のSQLを使うこともできますが、SQLインジェクションのリスクがあり危険です。どうしても使用したい場合は、サニタイズ済みの安全な文字列であることを確認したうえで Arel.sql を使用してください。

User
  .with(average_scores: Arel.sql(<<~SQL))
    SELECT reviewee_id, AVG(score) average_score
    FROM user_reviews
    GROUP BY reviewee_id
  SQL
  .joins("JOIN average_scores ON users.id = average_scores.reviewee_id")
  .where("average_score >= ?", 4)

テスト

各CTEをメソッドにすることで、それぞれについてテストすることができます。

spec/models/user_review_spec.rb

require 'rails_helper'

RSpec.describe UserReview, type: :model do
  describe ".average_scores" do
    it do
      user = create(:user)
      create(:user_review, reviewee: user, score: 5)
      create(:user_review, reviewee: user, score: 4)
      create(:user_review) # 対象外
      expect(UserReview.average_scores.map { |r| [ r.reviewee_id, r.average_score ] }).to include([ user.id, 4.5 ])
    end
  end

  describe ".reviews_counts" do
    it do
      user = create(:user)
      create_list(:user_review, 3, reviewee: user)
      create(:user_review) # 対象外
      expect(UserReview.reviews_counts.map { |r| [ r.reviewee_id, r.reviews_count ] }).to include([ user.id, 3 ])
    end
  end

  describe ".user_reviews_counts" do
    it do
      user = create(:user)
      create_list(:user_review, 3, reviewer: user)
      create(:user_review) # 対象外
      expect(UserReview.user_reviews_counts.map { |r| [ r.reviewer_id, r.user_reviews_count ] }).to include([ user.id, 3 ])
    end
  end
end

spec/models/user_spec.rb

average_scores = UserReview.average_scores
reviews_count = UserReview.reviews_counts
user_reviews_count = UserReview.user_reviews_counts

users_with_average_score = User.with(average_scores:, reviews_count:, user_reviews_count:)
  .joins("LEFT OUTER JOIN average_scores ON users.id = average_scores.reviewee_id")
  .joins("LEFT OUTER JOIN reviews_count ON users.id = reviews_count.reviewee_id")
  .joins("LEFT OUTER JOIN user_reviews_count ON users.id = user_reviews_count.reviewer_id")

expect(users_with_average_score).to match_array(User.all)

# 平均スコアが4以上のユーザーを探す
expect(users_with_average_score.where("average_score >= ?", 4)).to match_array([...])
# レビューが3件以上あるユーザーを探す
expect(users_with_average_score.where("reviews_count >= ?", 3)).to match_array([...])
# レビューを3件以上書いたユーザーを探す
expect(users_with_average_score.where("user_reviews_count >= ?", 3)).to match_array([...])

再帰的な利用について

CTEは再帰呼び出しをサポートしていますが、Rails 7.1のCTEサポートでは再帰呼び出しは未実装です。再帰呼び出しを活用することで、たとえば隣接リストモデルの検索クエリを書けるようになることが有名です。残念ながら現在のところは利用することができません。

https://github.com/rails/rails/pull/51601