WHERE を使わない絞り込み

# 概要

SQL で、行を絞り込むのに使えるのは WHERE だけではなく、INNER JOIN を使う事もできるという性質を利用して、モデルの絞り込みをしてみようという話です

# 背景 

先日[SQL 脳から見た Ruby](http://www.slideshare.net/yancyajp/rubyseenfromsqlishbrain-54870167)という発表をして、オチの部分に Rails のサンプルコードを出したんですが、なにぶん時間がギリギリだったので、駆け足になってしまい、見ている人が「？？？」という感じになっていたので、記事にしたいとおもいます

# 実装

## 登場するモデル

### User

下記のようなスキーマ

- name: text
- birthday: date

サンプルデータ

| name |birthday|
|---|---|
|ちゆ|1999-02-14| 
|ちゆ|2003-02-14| 
|もなみ|2002-02-09| 
|もなみ|2006-02-09| 
|メモリ|2003-11-02| 
|メモリ|2004-11-02| 

## シチュエーション

- たとえば「ちゆ１２歳」と「もなみ９歳」だけを抜き出したいとする

```sql
SELECT *
  FROM users
 WHERE (name = 'ちゆ' AND date_part('year', age(birthday)) = 12)
    OR (name = 'もなみ' AND date_part('year', age(birthday)) = 9)
```

- SQL で書くと、こんなかんじ
- 対象が増える程 OR が増える
- Repeat myself 感（Not DRY）
- Arel を駆使してゴリゴリ書くアレだ...
- 今回は、こういうのじゃない SQL を生成する実装にしてみよう

## モデルを追加

### Shapeshifter
  
```rb
class CreateShapeshifters < ActiveRecord::Migration
  def change
    create_table :shapeshifters do |t|
    end

    # INSERT forbidden
    execute <<~SQL
         ALTER TABLE shapeshifters
      ADD CONSTRAINT forbid_to_insert_shapeshifters
               CHECK (false)
    SQL
  end
end
```

```rb
class Shapeshifter < ActiveRecord::Base
  def self.metamorphose(schema:, tuples:)
    with(table_name => sanitize_sql_array(<<~SQL.chomp, tuples.flatten))
      SELECT *
        FROM #{to_values(tuples)}
          AS t(#{primary_key}, #{schema.join(', ')})
    SQL
  end
 
  def self.to_values(tuples)
    tuples.
      map.with_index(1) { |tuple, i| "(#{i}, #{tuple.map { '?' }.join(', ')})" }.
      join(", ").
      tap { |values| break "(VALUES #{values})" }
  end
 
  def self.sanitize_sql_array(sql, values)
    ActiveRecord::Base.send(:sanitize_sql_array, [sql, *values])
  end
end
```
### Shapeshifter の性質

```rb
Shapeshifter.all
#=> #<ActiveRecord::Relation []>
# 中身は常に空

Shapeshifter.create
#=> ActiveRecord::StatementInvalid:
#=> PG::CheckViolation: ERROR:  new row for relation "shapeshifters"
#=> violates check constraint "forbid_to_insert_shapeshifters"
# 新しいレコードは作成出来ない
```

- 中身も無い、INSERT も出来ない、属性が id のみのモデル
- これをこうじゃ

```rb
ar = Shapeshifter.
  metamorphose(
    schema: %w{code message},
    tuples: [[404, 'Not Found'], [200, 'OK']]
  )
#=> #<ActiveRecord::Relation [#<Shapeshifter id: 1>, #<Shapeshifter id: 2>]>

ar.map(&:attributes)
#=> [{"id"=>1, "code"=>404, "message"=>"Not Found"},
#=>  {"id"=>2, "code"=>200, "message"=>"OK"}]

ar.where(code: 200)
#=> #<ActiveRecord::Relation [#<Shapeshifter id: 2>]>
```

- アプリケーションから注入したタプルが SQL 界を旅してから AR として戻ってくる

```sql  
-- 発行される SQL
  WITH "shapeshifters" AS (
SELECT *
  FROM (VALUES (1, 404, 'Not Found'), (2, 200, 'OK'))
    AS t(id, code, message) )
SELECT "shapeshifters".*
  FROM "shapeshifters"
--  id | code |  message  
-- ----+------+-----------
--   1 |  404 | Not Found
--   2 |  200 | OK
```

- 一時的に共通表式(CTE)を参照することで、これを実現しています

## User モデル

```rb
class User < ActiveRecord::Base
  scope :on_name_and_ages, -> (tuples) {
    with(
      target_users: Shapeshifter.
        metamorphose(schema: %w{name age}, tuples: tuples)
    ).
    joins(<<~SQL)
      JOIN target_users
        ON users.name = target_users.name
       AND age = date_part('year', age(birthday))::integer
    SQL
  }
end
```

- scope に Shapeshifter を仕込むと、scope を呼び出す度に、引数に渡したタプルの内容のテーブルがあるかのような状態でクエリーが実行出来るので、それを JOIN することで絞り込みの目的を達成する

```rb
User.on_name_and_ages(
  [['ちゆ', 12], ['もなみ', 9]]
).map(&:attributes)
#=> [{"id"=>2, "name"=>"ちゆ", "birthday"=>Fri, 14 Feb 2003},
#=>  {"id"=>4, "name"=>"もなみ", "birthday"=>Thu, 09 Feb 2006}]
```

- タプルを使った絞り込みをする scope になっていることが分かる

```sql
-- 発行される SQL
  WITH "target_users" AS (
      WITH "shapeshifters" AS (
    SELECT *
      FROM (VALUES (1, 'ちゆ', 12), (2, 'もなみ', 9))
        AS t(id, name, age))
SELECT "shapeshifters".*
  FROM "shapeshifters")
SELECT "users".*
  FROM "users"
  JOIN target_users
    ON users.name = target_users.name
   AND age = date_part('year', age(birthday))::integer
```

# まとめ

- 普段は WHERE とか IN を使えば済むけれども、`(... AND ...) OR (... AND ...) ...` みたいな絞り込み方をしないといけないケースで、そういう SQL を生成するコードを書くのもしんどいし、生成された SQL を読むのもしんどい
- WITH を使って生成したリレーションを JOIN する、今回の方式を使うと、入り組んだ WHERE の方式と比べて、絞り込みに使う「データ」と絞り込み方の「ロジック」が分離されて、コードも、生成された SQL も分かりやすくなるように思う
- いつか使いこなしたい

# 12 月 18 日

今日はわしの誕生日なんですが、毎年「[ウィッシュリスト](http://yancya.jp/hoshi_wish)に入れてるからには読めよ」などと言いながら難しい本を送りつけて下さる方が続出しております。ほんと勘弁して欲しいですね