鈴木商店の嶋崎です。こんにちは。

今回はSQLの話です。
ひとつのテーブルに対して条件がある場合、whereで絞り込むだけで良いので簡単です。
1対1の場合もinner joinをしてon条件で絞り込めば、望む結果が得られます。
また、1対多であっても、検索条件がひとつの場合、実質1対1になるので、そう問題ではありません。

問題は1対多で、かつ条件が複数ある場合です。

例えばBlogテーブルと、Tagテーブルがあるとします。
1つのエントリに対して、複数のtagが設定できるとき、
複数のタグでOR検索（条件のどれかのtagを含んでいる）、AND検索（条件で指定した全てのtagを含んでいる）をしたいと思います。

ググると、条件個分UNIONとか、条件個分JOINと言う方法が見つかりましたが、
今回はPHP等でプログラムにしやすい形を目指しての方法になります。

まずは準備、以下のテーブルがあるとします。

-- 型やサイズは適当に想像してください
create table blog
blog_id, title, body;

create table tag
tag_id, tag_name;

create table blog_tag
blog_id,tag_id

-- 型やサイズは適当に想像してください

create table blog

blog_id, title, body;

create table tag

tag_id, tag_name;

create table blog_tag

blog_id,tag_id

blog

blog_id	title	body
1	こんにちは	こんにちはこんにちは
2	こんばんは	こんばんははこんばんは

tag

tag_id	name
1	挨拶
2	朝
3	夜

blog_tag

blog_id	tag_id
1	1
1	2
2	1
2	3

パターン1

OR検索の場合

select blog_id, title, body
from blog
where 
exists
(
  select 1 from blog_tag 
  where blog_tag.blog_id = blog.blog_id
  and blog_tag.tag_id in (1, 2, 3)
)

select blog_id, title, body

from blog

where

exists

(

select 1 from blog_tag

where blog_tag.blog_id = blog.blog_id

and blog_tag.tag_id in (1, 2, 3)

)

AND検索の場合

select blog_id, title, body
from blog
where 
exists
(
  select 1 from blog_tag 
  where blog_tag.blog_id = blog.blog_id
  and blog_tag.tag_id in (1, 2)
  having count(*) = 2 -- ここの2は条件個数を指定 (1,2,3)だった場合は「3」
)

select blog_id, title, body

from blog

where

exists

(

select 1 from blog_tag

where blog_tag.blog_id = blog.blog_id

and blog_tag.tag_id in (1, 2)

having count(*) = 2 -- ここの2は条件個数を指定 (1,2,3)だった場合は「3」

)

existsです。条件のどれか一つでも当てはまればexistsはtrueなので、OR検索できます。
条件に全て当てはまった件数と条件個数が等しければtrueを返すようにすればAND検索になります。

パターン2

OR検索の場合

select blog_id, title, body
from blog
inner join  
(
  select blog_tag.blog_id, count(*) s 
  from blog_tag 
  where blog_tag.tag_id in (1,2,3)
  group by blog_tag.blog_id
  having s&gt; 0
) tag_summary
on blog.blog_id = tag_summary.blog_id

select blog_id, title, body

from blog

inner join

(

select blog_tag.blog_id, count(*) s

from blog_tag

where blog_tag.tag_id in (1,2,3)

group by blog_tag.blog_id

having s> 0

) tag_summary

on blog.blog_id = tag_summary.blog_id

AND検索の場合

select blog_id, title, body
from blog
inner join  
(
  select blog_tag.blog_id, count(*) s 
  from blog_tag 
  where blog_tag.tag_id in (1,2)
  group by blog_tag.blog_id
  having s = 2 -- ここの2は条件個数を指定 (1,2,3)だった場合は「3」
) tag_summary
on blog.blog_id = tag_summary.blog_id

select blog_id, title, body

from blog

inner join

(

select blog_tag.blog_id, count(*) s

from blog_tag

where blog_tag.tag_id in (1,2)

group by blog_tag.blog_id

having s = 2 -- ここの2は条件個数を指定 (1,2,3)だった場合は「3」

) tag_summary

on blog.blog_id = tag_summary.blog_id

EXISTSをinner joinに書き換えたパターンです。
MySQLのバージョンや、データ件数等によってはこっちの方が早いと思います。

ORにしてもANDにしても多少の構造の管理は必要とは言え、基本的には条件が増減するだけなので、シンプルです。

PHPで実装する場合は、条件がない場合はexists及びjoinをしないようにします。
あとは条件のプレースホルダの増減と、AND検索の場合にhavingのところにも条件数のプレースホルダを与えるだけです。

CakePHPであればconditionsに配列そのまま渡すだけですね。

Aurora（MySQL5.6）で検証していますが、どのデータベースでも使えると思います。

以上です。

MySQLで1対多の、多の方に複数条件がある場合の書き方

パターン1

パターン2

コメントを残すコメントをキャンセル

パターン1

パターン2

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル