ActiveRecord::Relation の CTE, いつ使う？

ソニックガーデン プログラマ アドベントカレンダー、7日目の記事です。

Rails 7.1 で追加された ActiveRecord::Relation の CTE の使いどころについて、書いてみます。

概要

SQLを直接書くときによく使う CTE (Common Table Expression / 共通テーブル式)。

Rails 7.1 からwithメソッドが追加され、Arelを使わずにCTEを実現できるようになりました。
ActiveRecord::QueryMethods - with

当初は「待望の！」とは思ったものの、実際のところは使うタイミングがあまりなかったので、「どういう時なら使えそうかな？」を考えてみました。

なお、CTE自体の説明や、パフォーマンスに関しては触れません。

なんで使うタイミングがないの？

SQLでは主に可読性・メンテナンス性の向上のために使われるCTEですが、Railsにおいてはassociationやscopeメソッド、一時変数などを使えばよいだけです。

得られる結果が同じならば、無理にCTE(withメソッド)を使ってもコードが読みにくくなるだけ。特にメリットはないと思います。

withメソッドを頑張って使う例

User
  .with(comments_on_today: Comment.where(created_at: Date.current.all_day))
  .where('users.id IN (SELECT user_id FROM comments_on_today)')

# => SQL
# WITH
#   "comments_on_today" AS (
#     SELECT "comments".*
#     FROM   "comments"
#     WHERE  "comments"."created_at" BETWEEN $1 AND $2
#   )
# SELECT "users".*
# FROM   "users"
# WHERE  (users.id IN (SELECT user_id FROM comments_on_today))

withメソッドを使わない例

comments_on_today = Comment.where(created_at: Date.current.all_day)
User.where(id: comments_on_today.select(:user_id))

# => SQL
# SELECT "users".*
# FROM   "users"
# WHERE
#   "users"."id" IN (
#     SELECT "comments"."user_id"
#     FROM   "comments"
#     WHERE  "comments"."created_at" BETWEEN $1 AND $2
#   )

使いどころ: 関連テーブルの条件付き集計結果と外部結合するケース

例えば、下記の要件の画面を作る場合、withメソッドを使うとスマートに書けそうです。

• テナント名と、そのテナントに「指定月に登録されたユーザー数」を一覧表示する
• 「指定月に登録されたユーザー数」でソートする
• ページネーションが必要

状況としては限られていますが、似たような要件はそれほど珍しくもないはず。

# app/models/user.rb
class User < ApplicationRecord
  scope :registered_count_per_tenant, ->(count_alias: 'registered_count') do
    group(:tenant_id).select(:tenant_id, arel_table[:id].count.as(count_alias))
  end
end

# controller
year_month = params[:year_month].to_date
registered_user_count_per_tenant =
  User
    .where(created_at: year_month.all_month)
    .registered_count_per_tenant(count_alias: 'registered_user_count')
@tenants =
  Tenant
    .with(registered_user_count_per_tenant:)
    .left_joins(:registered_user_count_per_tenant)
    .select(
      Tenant.arel_table[Arel.star],
      :'registered_user_count_per_tenant.registered_user_count'
    )
    .order('registered_user_count_per_tenant.registered_user_count': :desc)
    .page(params[:page]) # kaminari gem の利用を想定

# view
@tenants.each do |tenant|
  p tenant.name
  p tenant.registered_user_count
end

# => SQL
# WITH
#   "registered_user_count_per_tenant" AS (
#     SELECT
#       "users"."tenant_id"
#       , COUNT("users"."id") AS registered_user_count
#     FROM
#       "users"
#     WHERE
#       "users"."created_at" BETWEEN $1 AND $2
#     GROUP BY
#       "users"."tenant_id"
#   )
# SELECT
#   "tenants".*
#   , "registered_user_count_per_tenant"."registered_user_count"
# FROM
#   "tenants"
#   LEFT OUTER JOIN "registered_user_count_per_tenant"
#     ON "registered_user_count_per_tenant"."tenant_id" = "tenants"."id"
# ORDER
#   "registered_user_count_per_tenant"."registered_user_count" DESC
# LIMIT $3 OFFSET $4

N+1は起きませんし、JOINの結合条件を書かずに済んでいるあたり、スマートに書けたほうでは。
(私の好みとしてarel_tableを使っていますが、直接 COUNT(users.id) や tenant.* にしても問題はありません。)

なお、「並び替えもページネーションも不要」であれば、下記のようにcountメソッドを使ってコード上で紐づける方法でも良いと思います。

withメソッドを使わない場合

target_month = '2024-12-01'.to_date
registered_user_count_by_tenant_id =
  User
    .where(created_at: target_month.all_month)
    .group(:tenant_id).count
tenants = Tenant.all

tenants.each do |tenant|
  p tenant.name
  p registered_user_count_by_tenant_id[tenant.id]
end

使いどころ: ツリー構造のテーブルが必要なとき

この記事を書きはじめるまで、 Rails 7.2 から再帰クエリを実現するwith_recursiveメソッドが追加されていたことを知りませんでした・・・。
ActiveRecord::QueryMethods - with_recursive

Railsでツリー構造のテーブルを扱う場合はacts_as_tree¹やancestry²などのgemを利用することが多いと思いますが、with_recursiveメソッドを追加されたことで、Railsの標準機能で無理なく実現できるようになりました。

といっても、ancestryなどのgemに頼るほうが楽なのは変わらないかと。

まとめ

限られた状況で、withメソッドを使うとスマートに書けることもある、と言えそうです。

私が思いつかなかっただけで、「こういう場面でも有用だよ！」というものがあれば、記事を書いて教えてくれると嬉しいです！

1. https://github.com/amerine/acts_as_tree
   使ったことない。 ↩

2. https://github.com/stefankroes/ancestry
   使ったことある。 ↩