概要
ActiveRecordを利用したコーディングにおいて、NOT EXISTSとINNER JOINを組み合わせた、少し複雑なクエリが必要となった。
そこで、Arelを使用した、生のSQLを書かない方法で実装してみた。
Arelとは
第43回 Rails 3を支える名脇役たち その1 - Arel -
Arelとは，「Relational Algebra」または「Active Relation」の略で，その名前から想像がつくとおり，ActiveRecordから派生した，「関係代数」をRubyのオブジェクトで取り扱うためのライブラリです。
ドキュメントによれば，Arelは，
- 面倒なSQLの生成を簡単にしてくれて，
- さまざまなデータベースシステムに対応している，
「フレームワークのフレームワーク」を目指して作られています。
つまり，Arelを使えば，DBの互換性やSQL文字列の生成などに惑わされることなく，大事な設計やモデリングに注力してO/Rマッピング処理を実装することができるようになっています。
とても簡単に要約すると、
- SQLを簡単に生成してくれる
- 様々なRDBMSに対応してくれる
ということだと思います。
テーブル概要
超簡易化したテーブル概要は以下のとおり。
| テーブル名 | 概要 |
|---|---|
| members | メンバーー覧。IDと名前を持つ。 |
| events | イベント一覧。IDと名前を持つ。 |
| groups | グループ一覧。イベントごとのグループを保持。IDと名前、加えてイベントIDを持つ。 |
| groups_members | グループとメンバー間の関連。グループIDとメンバーIDを持つ。 |
目的
とあるイベント(id: x)に参加していないメンバーを抽出したい。
そのためには、
-
groupsをイベントIDで絞り、イベントに関連するグループを抽出する -
groupsとgroups_membersをJOINし、イベント参加者一覧を抽出する - イベント参加者一覧に存在しないメンバーを抽出する
というクエリを作成すれば良い。
SQLによる記述
具体的には、以下のようなクエリを生成したい。
SELECT
*
FROM
members a
WHERE
NOT EXISTS (
SELECT
*
FROM
groups b JOIN groups_members c
ON b.id = c.group_id
AND b.event_id = x -- 任意のイベントID
WHERE
a.id = c.member_id
)
;
Arelによる記述
Arel::Tableのproject，join，on，whereを組み合わせて、上記のSQLを再現してみた。
members = Member.arel_table
groups = Group.arel_table
groups_members = GroupsMember.arel_table
condition =
groups
.project(Arel.star)
.join(groups_members)
.on(groups[:id].eq(groups_members[:group_id]), groups[:event_id].eq(x))
.where(members[:id].eq(groups_members[:member_id]))
Member.where(condition.exists.not).all
ここで出てきたprojectという馴染みのないワードは、projectionの略だそうです。
Weblio - projectionによると、「射影」という意味があります。射影は属性の抽出をする演算で、SQLでいうとSELECTにあたります。つまり、projcet(Arel.star)はSELECT *のことです。
詳細はWikipedia大先生に任せます。
生成されるSQL
上記のコードから生成されたSQLは以下の通りとなりました。
SELECT
"members" . *
FROM
"members"
WHERE
(
NOT (
EXISTS (
SELECT
*
FROM
"groups" INNER JOIN "groups_members"
ON "groups" . "id" = "groups_members" . "group_id"
AND "groups" . "event_id" = x
WHERE
"members" . "id" = "groups_members" . "member_id"
)
)
)
ORDER BY
id
;
無事、目的のSQLとなっていました。
条件の追加
メンバーがアクティブのものだけを抽出する必要があることに後から気がついたので、以下のようにwhereを追加してみました。
Member.where(condition.exists.not).where(active: true).all
Tips
condition.to_sqlのようにすると、具体的なSQLが生成されます。Rails consoleを使って適宜to_sqlをし、生成されるSQLと睨めっこしながら作業を進めました。
参考文献
以下の記事を参考にしました。詳細な解説は以下の記事の方が充実しています。
謝辞
@jnchito さんに、Arelのコード中にあったミスを修正していただきました。
ありがとうございます。