特定の2つのタグが付けられたバグ検索

SQLAP

7章 マルチカラムアトリビュート(複数列属性) より

特定の2つのタグが付けられたバグ検索といった少し複雑な処理も、簡単に記述できます。

BugsテーブルとTagsテーブルが1対多の関係のとき、"printing"と"performance"という2つのタグが付いたバグの検索をどう行うか。いかにもありがちな話だが案外難しい。

2つのどちらかなら簡単だ。

SELECT DISTINCT b.* FROM Bugs AS b
  JOIN Tags AS t USING (bug_id)
WHERE t.tag = 'printing' OR t.tag = 'performance';

では2つ両方がついているものはどうするか。自分ならまずこんなのを考えるかもしれない。

-- A
SELECT * FROM Bugs AS b
 WHERE EXISTS
      (SELECT * FROM Tags AS t1
        WHERE b.bug_id = t1.bug_id AND t1.tag = 'printing')
   AND EXISTS
      (SELECT * FROM Tags AS t2
        WHERE b.bug_id = t2.bug_id AND t2.tag = 'performance');

ちょっとひねるとすれば集約関数を使ってこう。

-- B
SELECT bug_id FROM Bugs
  JOIN Tags AS t USING (bug_id)
 WHERE t.tag IN ('printing', 'performance')
 GROUP BY bug_id
HAVING count(DISTINCT tag) = 2;
-- C
SELECT bug_id FROM Bugs
  JOIN Tags AS t USING (bug_id)
 GROUP BY bug_id
HAVING 0 < sum(CASE t.tag WHEN 'printing' THEN 1 ELSE 0 END)
   AND 0 < sum(CASE t.tag WHEN 'performance' THEN 1 ELSE 0 END)

PostgreSQLであれば集約関数にbool_or()があるのでこうも書ける。

-- D
SELECT bug_id FROM Bugs
  JOIN Tags AS t USING (bug_id)
 GROUP BY bug_id
HAVING bool_or(t.tag = 'printing')
   AND bool_or(t.tag = 'performance')

ただし、B,C,Dは、bug_idしか取れないRDBMSもあるので、Bugsの他の列も取得したければ、GROUP BYに列挙する必要がある(MySQLとPostgreSQL9.1以降では必要無い)。

-- B'
SELECT bug_id, summary FROM Bugs
  JOIN Tags AS t USING (bug_id)
 WHERE t.tag IN ('printing', 'performance')
 GROUP BY bug_id, summary
HAVING count(DISTINCT tag) = 2;

で、本に書かれていたのはこう。

-- E
SELECT * FROM Bugs
  JOIN Tags AS t1 USING (bug_id)
  JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';

これはすごくお洒落なクエリーだなと思う。この目的でこのクエリーを最初に思いつく自信が無い。Aが思いつけばあとからJOINで書き換えられるなと考えるかもしれないけど。

こんな風に書いたほうが理解しやすいかもしれない。

-- E'
SELECT * FROM Bugs AS b
  JOIN Tags AS t1 ON (t1.bug_id = b.bug_id AND t1.tag = 'printing')
  JOIN Tags AS t2 ON (t2.bug_id = b.bug_id AND t2.tag = 'performance')

ただ、Tagsテーブルにt1、t2と2種類のaliasをつけてJOINしているあたりがいかにもO/Rマッパーと相性が悪そうだ。

Railsなどではどのように解決するのが普通なのだろう。

4/4 追記 @t_wadaさんがQA@ITで質問してくれました。 Rails で同じテーブルを何度も JOIN する上手い方法はありますか? - QA@IT

t1とt2の両方に含まれているもの、をあらわすINTERSECTがあるのを忘れていた。

SELECT b.* FROM Bugs AS b
  JOIN Tags AS t USING (bug_id)
WHERE t.tag = 'printing'
INTERSECT
SELECT b.* FROM Bugs AS b
  JOIN Tags AS t USING (bug_id)
WHERE t.tag = 'performance'

とか

SELECT * FROM Bugs
 WHERE bug_id IN (
       SELECT bug_id FROM Tags WHERE tag = 'printing'
       INTERSECT
       SELECT bug_id FROM Tags WHERE tag = 'performance')

でもよさそうです。