概要

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

背景

先日SQL 脳から見た Rubyという発表をして、オチの部分に 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

シチュエーション

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

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

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

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 の性質

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 のみのモデル
これをこうじゃ

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
  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 モデル

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 することで絞り込みの目的を達成する

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
  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 日

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