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