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

[ソニックガーデン プログラマ アドベントカレンダー](https://qiita.com/advent-calendar/2024/sonicgarden)、7日目の記事です。

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

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

Rails 7.1 から`with`メソッドが追加され、`Arel`を使わずにCTEを実現できるようになりました。
[ActiveRecord::QueryMethods - with](https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-with)

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

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


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

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

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

```ruby: 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))
```

```ruby: 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`メソッドを使うとスマートに書けそうです。

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

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

```ruby
# 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`メソッドを使ってコード上で紐づける方法でも良いと思います。

```ruby: 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](https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-with_recursive)

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

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

[^1]: https://github.com/amerine/acts_as_tree
      使ったことない。
[^2]: https://github.com/stefankroes/ancestry
      使ったことある。


# まとめ

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

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