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

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

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

# Arelとは
[第43回　Rails 3を支える名脇役たち その1 - Arel -](http://gihyo.jp/dev/serial/01/ruby/0043#sec4)

> Arelとは，「Relational Algebra」または「Active Relation」の略で，その名前から想像がつくとおり，ActiveRecordから派生した，「関係代数」をRubyのオブジェクトで取り扱うためのライブラリです。

> ドキュメントによれば，Arelは，

> 1. 面倒なSQLの生成を簡単にしてくれて，
> 2. さまざまなデータベースシステムに対応している，

>「フレームワークのフレームワーク」を目指して作られています。

>つまり，Arelを使えば，DBの互換性やSQL文字列の生成などに惑わされることなく，大事な設計やモデリングに注力してO/Rマッピング処理を実装することができるようになっています。

とても簡単に要約すると、

- SQLを簡単に生成してくれる
- 様々なRDBMSに対応してくれる

ということだと思います。

# テーブル概要
超簡易化したテーブル概要は以下のとおり。

|テーブル名|概要|
|---|---|
|members|メンバーー覧。IDと名前を持つ。|
|events|イベント一覧。IDと名前を持つ。|
|groups|グループ一覧。イベントごとのグループを保持。IDと名前、加えてイベントIDを持つ。|
|groups_members|グループとメンバー間の関連。グループIDとメンバーIDを持つ。|

# 目的
とあるイベント``(id: x)``に参加していないメンバーを抽出したい。

そのためには、

1. ``groups``をイベントIDで絞り、イベントに関連するグループを抽出する
2. ``groups``と``groups_members``を``JOIN``し、イベント参加者一覧を抽出する
3. イベント参加者一覧に存在しないメンバーを抽出する

というクエリを作成すれば良い。

## SQLによる記述
具体的には、以下のようなクエリを生成したい。

```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``を再現してみた。

```ruby
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](http://ejje.weblio.jp/content/projection)によると、「射影」という意味があります。射影は属性の抽出をする演算で、``SQL``でいうと``SELECT``にあたります。つまり、``projcet(Arel.star)``は``SELECT *``のことです。

詳細は[Wikipedia大先生](http://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_%28%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB%29#.E5.B0.84.E5.BD.B1)に任せます。

## 生成されるSQL
上記のコードから生成された``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``を追加してみました。

```ruby
Member.where(condition.exists.not).where(active: true).all
```

## Tips
``condition.to_sql``のようにすると、具体的な``SQL``が生成されます。``Rails console``を使って適宜``to_sql``をし、生成される``SQL``と睨めっこしながら作業を進めました。

# 参考文献
以下の記事を参考にしました。詳細な解説は以下の記事の方が充実しています。

- [Arel でサブクエリ](http://qiita.com/tkawa/items/e65c8847e57a5329f336)
- [ActiveRecordでNOT EXISTSを発行する](http://qiita.com/abey1192/items/198f3c15d14392023a63)

# 謝辞
@jnchito さんに、Arelのコード中にあったミスを修正していただきました。
ありがとうございます。