MySQLで「タグを持つ記事」を取得したい

Question

###前提・実現したいこと
wp_posts、wp_tags、wp_tag_holders、という３つのテーブルがあります。（wp_という接頭辞は気にしないでください。）

tags.tag_name='red' and tags.tag_name='purple'のように指定して「redとpurpleのタグを持つ記事」という取得をしたいのですが…

###発生している問題
…ですが、whereの指定がよくわからず、取得件数が0件になってしまいます。

###欲しい結果
「redとpurpleのタグを持つ記事」とは「tag_id=1とtag_id=5を持つ記事」になります。
つまり「post_id=1とpost_id=5の記事」であり以下の結果が欲しいです。

ID	content
1	赤くて緑で紫
5	赤くて紫

###テーブル設計
CREATE、INSERTです。

CREATE TABLE wp_posts (
  `ID` int,
  `content` varchar(100),
  PRIMARY KEY (`ID`)
);
INSERT INTO wp_posts
    (`ID`, `content`)
VALUES
    (1, '赤くて緑で紫'),(2, '緑で紫'),(3, '赤'),(4, '青'),(5, '赤くて紫')
;

CREATE TABLE wp_tags (
  `ID` int,
  `tag_name` varchar(10),
  PRIMARY KEY (`ID`),
  INDEX index_wp_tags_1 (`tag_name`) 
);
INSERT INTO wp_tags
    (`ID`, `tag_name`)
VALUES
    (1, 'red'),(2, 'green'),(3, 'blue'),(4, 'yellow'),(5, 'purple')
;

CREATE TABLE wp_tag_holders (
  `tag_ID` int,
  `post_ID` int,
  CONSTRAINT foreign_wp_tag_holders_01 FOREIGN KEY (`tag_ID`) REFERENCES wp_tags(`ID`),
  INDEX index_wp_tag_holders_1 (`post_ID`) 
);
INSERT INTO wp_tag_holders
    (`tag_ID`,`post_ID`)
VALUES
    (1, 1),(2, 1),(5, 1),(2, 2),(5, 2),(1, 3),(3, 4),(1, 5),(5, 5)
;

###取得のソースコード
以下で取得件数は0件でした。

select
post.ID,
post.content

from
wp_posts post
left join
wp_tag_holders tag_holders ON tag_holders.post_ID=post.ID
left join
wp_tags tags ON tags.ID=tag_holders.tag_ID

where
tags.tag_name='red' and tags.tag_name='purple'

###試したこと
上のjoinしたものをwhereで指定できないのだと思い、joinの方向をやめて、次のようにINで指定するように試みましたが、こちらもエラーなどはないのですが、取得件数は0件でした。

select
post.ID,
post.content

from
wp_posts post

where post.ID IN (
  select
  tag_holders.post_ID
  from
  wp_tag_holders tag_holders
  left join
  wp_tags tags ON tags.ID=tag_holders.tag_ID
  where
  tags.tag_name='red' and tags.tag_name='purple'
)

###バージョン
MySQLのバージョンは少し古めで5.6になります。
宜しくお願い致します。

Comments

[m.ts10806]

WordPress前提でしたら「WordPress」はタグにあったほうが良いと思います。
「wp_という接頭辞は気にしないでください。」と仰いますが、構成がそれであれば前提条件として必要に思います。

Answer 1

「redとpurpleのタグを持つ記事」というのは、「redのタグを持つ記事」と「purpleのタグを持つ記事」が共に存在しているという独立した事象である事を条件にします。

select ID, content
from  wp_posts post
where exists (
        select 1 
        from  wp_tag_holders tag_holders 
              left join wp_tags tags 
              ON  tags.ID=tag_holders.tag_ID
        where tag_holders.post_ID=post.ID
          and tags.tag_name='red'
      )
  and exists(
        select 1 
        from  wp_tag_holders tag_holders 
              left join wp_tags tags 
              ON  tags.ID=tag_holders.tag_ID
        where tag_holders.post_ID=post.ID
          and tags.tag_name='purple'
      )

Comments

[hotaruika]

仰る書き方で考えていた通りにできました。タグの数だけselectが発生せざるを得ないのですか。タグが増えるほどかなり遅くなりそうですが、仕方ないですね。ありがとうございます。

[sazi]

条件用のテーブルなどがあれば別ですが、条件が動的である以上可変部分を少なくすることは出来ても、無くす事はできません。

Answer 2

レコード単位でredかつpurpleのデータは存在しないですから
どういう結果をほしいかわかりませんがとりあえずandをorに変えるところから

Comments

[yambejp]

ざっと条件を見直してみるとこんな感じでしょうか？

select * from wp_posts as t3
where id in(
select
t2.tag_id
from
wp_tags as t1
inner join wp_tag_holders as t2
on t1.id=t2.post_id
and t1.tag_name in('red','purple')
group by tag_id
having count(*)=2
)

[sazi]

それだと、redやpurple片方だけで２件あってもHITしちゃいます。

[yambejp]

＞ redやpurple片方だけで２件あっても

たしかにテーブル側で制限してないですが、普通は中間テーブルは同じデータを
もつことを想定していないんですけどね、一応ダブりを想定するならこう？

select * from wp_posts as t3
where id in(
select
t2.tag_id
from
wp_tags as t1
inner join wp_tag_holders as t2
on t1.id=t2.post_id
and t1.tag_name in('red','purple')
group by tag_id
having count(distinct post_id)=2
)

[hotaruika]

ありがとうございます。yambejp様のコードでも実現できました。select回数が少ない分だけsazi様のコードより早く済みそうに思えましたが、質問がございます。

[hotaruika]

「普通は中間テーブルは同じデータを
もつことを想定していない」というのは、どういう意味でしょうか？
質問のCREATEには何か問題というか、「普通でない」ような状態になっているのでしょうか？

[yambejp]

wp_tag_holdersはwp_postsとwp_tagsをリレーションするテーブルですよね？
仮に2,1で連携されているデータにもう一つ2,1を連携させる意味がありません。
したがってtag_ID,post_IDをつかって複合unique属性を付けておくのが妥当です。
しかしそのように設定されていないのであえてdistinctをかましたcountをつかって
処理をしています

[hotaruika]

はい、リレーションするテーブルになります。
たびたび理解が至らず誠に申し訳ございませんが、以下の引用はどの部分のことでしょうか？

>仮に2,1で連携されているデータにもう一つ2,1を連携させる意味がありません

wp_tag_holdersのレコードは
(1, 1),(2, 1),(5, 1),(2, 2),(5, 2),(1, 3),(3, 4),(1, 5),(5, 5)
となっており、「2,1」は一つしかないと思うのですが、「もう一つ2,1」とは？

私としても重複は望んではおりませんので、どの部分のことを仰っているのか理解したいのですが…

[yambejp]

> 「もう一つ2,1」とは？

もう一つ2,1を追加する「可能性がある」ということです
saziさんの指摘もそこに帰結します。
かりに2,1のレコードを追加してみると、私の最初のSQLがNGで新しいSQLがOKなのが
確認できると思います

[hotaruika]

なるほど。yambe様は質問にドンピシャでお答え下さったのに対し、sazi様は質問で複合ユニークがないことから「可能性がある」を想定し先見に富んだお答えを下さったわけですね。

今回は複合ユニークとすべきでしたので、yambe様の13:26が適当かもしれません。

高レベルなお二方のご回答は大変刺激になりました。どうもありがとういございました。