Ktouth Brand. on Web

け〜くんこと K.Ktouth のだらだらした日常と突発的に作るプログラムや読み物とかの雑多サイト



[2011年01月08日]

タグの絞り込みのSQLって、これで良いのか……?

2011年01月09日 03:38更新 筆者:K.Ktouth

今日も今日とてプログラム〜♪
ruby はともかく、SQL に関してはこれまで大していじった経験がないので、本当にこれが適切、というか効率的なものなのかがまだよくわかってないのが不安材料だったりする。もちろん大量のデータを一括処理するのには必須とはわかっているし、実際便利ではあるんだが、使いこなせている実感が全くないのが困りもの(笑)
今回気になっているのが「多対多で結ばれた要素によって絞り込んだ一覧の取得」を SQL で書くにはどうすればいいのか。一応考えついたのは以下のものなんだけど……

# Article モデル(articles テーブル)と Tag モデル(tags テーブル)は articles_tags テーブルで多対多(many_to_many、HABTM)で関連付けられている。
# @tags には絞り込む Tag モデルの配列が格納されている。

sub_query = DB[:articles_tags].filter(:tag_id => @tags.map {|x| x.id })\
               .group_by(:article_id).having('COUNT(*) = ?', @tags.size)\
               .select(:article_id)
  query = Article.filter(:id => sub_query).order_by(:id.desc)

# 出力される SQL は以下の通り。
# @tags = [Tag[8], Tag[52]]

SELECT * FROM `articles`
 WHERE (`id` IN (
  SELECT `article_id` FROM `articles_tags`
   WHERE (`tag_id` IN (8, 52))
   GROUP BY `article_id` HAVING (COUNT(*) = 2)
  ))
 )
 ORDER BY `id` DESC

ruby 側は sequel を利用。
意味合いとしてはまず関連テーブルを使って絞り込みたいタグのレコードだけに絞り、アーティクル毎にカウントして絞り込みたいタグ数と一致したアーティクルを取得……という流れ。
SQLを見ればわかるようにサブクエリ→集計関数→メインクエリで、絞り込みたいタグ数が増えてもクエリの深度は変わらないのでそこまで悪くないと思われます。もちろんタグ数が0もしくは1ならもっと単純化したものに場合分けした方が良いでしょうが。

結局この辺はアルゴリズム構築と一緒なので、経験がものを言うわけだからねぇ……悩む悩む。

本日のリンク元
アンテナ
その他のリンク元
検索