has_many :throughな関連テーブルでの検索について
関連テーブルでカテゴリみたいな多対多の関連を実装したとき、一番安易なのはとのかくJOINで全部連結しちゃう方法だけど、これだと重そうだ。なるべく連結の規模が小さくて拡張しやすそうなSQLを考えてる。SQL素人の癖に。
いろいろ試してみたけどこんな感じで落ち着いた。
もっと効率よい方法、教えてください
# has_many :through な関連 genres と conditions による検索サンプル # 「いずれかのジャンル(Genre)」かつ「すべての条件(Condition)を満たす」 # conditions => { # :genre_ids => [1,2,3] # GenreのidでOR検索 # :condition_ids => [1,2,3] # ConditionのidでAND検索 # } def Shop.search(conditions = {}) parameters = { } # パラメータ プレースホルダ用 set_queries = [] # idの集合を得るためのサブクエリを格納する配列 genre_ids = (conditions[:genre_ids] || []).collect{ |id| id.to_i }.compact.uniq unless genre_ids.empty? # 「ジャンル」の指定 # IN演算子でOR条件をまとめてやる # 「いずれかのジャンル」を満たす中間テーブルのshop_idの集合を返すサブクエリをつくる set_queries << "SELECT a.shop_id FROM genre_assigns a WHERE a.genre_id IN (:genre_ids)" parameters[:genre_ids] = genre_ids end condition_ids = (conditions[:condition_ids] || []).collect{ |id| id.to_i }.compact.uniq unless condition_ids.empty? # 「条件」の指定 # サブクエリで必要な列のみ内部結合するようにし、 # GROUP BYとCOUNTで条件を全て満たすものを抽出。 # 「すべての条件」を満たす中間テーブルのshop_idの集合を返すサブクエリをつくる set_queries << <<-SQL SELECT s.id FROM shops s INNER JOIN (SELECT ca.shop_id AS shop_id, ca.sign_id AS sign_id FROM condition_assigns ca INNER JOIN (SELECT conditions.id AS id FROM conditions WHERE conditions.id IN (:condition_ids)) AS c ON ca.sign_id = c.id) a ON s.id = a.shop_id GROUP BY s.id HAVING COUNT(a.sign_id) >= #{condition_ids.size} SQL parameters[:condition_ids] = condition_ids end # 積集合を使って、マッチするidの集合を求めてIN演算子で抽出。 # ソートやらshops内の検索やら必要に応じて付け加えてやればよし。 Shop.find_by_sql([<<-SQL, parameters]) SELECT shops.* FROM shops #{"WHERE shops.id IN (#{set_queries.join(" INTERSECT ")})" unless set_queries.empty?} SQL end