概要

ActiveRecordを利用したコーディングにおいて、NOT EXISTSとINNER JOINを組み合わせた、少し複雑なクエリが必要となった。

そこで、Arelを使用した、生のSQLを書かない方法で実装してみた。

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のコード中にあったミスを修正していただきました。
ありがとうございます。

ActiveRecordでサブクエリ+NOT EXISTSを発行

概要