Edited at

WHERE を使わない絞り込み

More than 3 years have passed since last update.


概要

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


シチュエーション


  • たとえば「ちゆ12歳」と「もなみ9歳」だけを抜き出したいとする

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 日

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