「どのwhereで取得されたレコードか？」を、取得結果に示したい（MySQL）

Question

###前提・実現したいこと
MySQLのwhereで「タグ1とタグ2を持つ記事」などの条件で取得しています

下記のソースコードで取得結果となるIDは 2,4,8,9,10 という5件です

この取得結果に、「このレコードはタグ1を持つ記事として取得されました」ということを示すことはできますか？

###最終的な目的
最終的な目的としてはフロントへの出力を次のように、タグごとにわけることです

－－－－－－－－－－－－
【タグ1を持つ記事一覧】
2,4,8,10

【タグ2を持つ記事一覧】
9
－－－－－－－－－－－－

###ソースコード
現状はこのようなSQLを書いていて、

select p.ID, p.contents
from posts p
left join tag_relations tr ON tr.contents_ID = p.ID
left join tags t ON t.ID = tr.tags_ID
where t.tag_name IN ( 'タグ1', 'タグ2' ) # 指定タグを持っている
AND p.ID NOT IN ( 1 ) # 指定記事を除外
group by p.ID

###ソースコードの結果
で、次のレコードが取得される状況ですが、

ID	contents
2	#タグ1 を持っている記事2の本文です
4	#タグ1 と #タグ3 と #タグ4 を持っている記事4の本文です
8	#タグ1 を持っている記事8の本文です
9	#タグ2 と #タグ4 を持っている記事9の本文です
10	#タグ1 と #タグ2 と #タグ6 を持っている記事10の本文です

###欲しい結果
ここに次のように「このレコードはタグ1を持つ記事として取得されました」などの「取得経緯のtag_ID」を示したいのです

ID	contents	取得経緯のtag_ID
2	#タグ1 を持っている記事2の本文です	1
4	#タグ1 と #タグ3 と #タグ4 を持っている記事4の本文です	1
8	#タグ1 を持っている記事8の本文です	1
9	#タグ2 と #タグ4 を持っている記事9の本文です	2
10	#タグ1 と #タグ2 と #タグ6 を持っている記事10の本文です	1

【※】post_ID=10はタグ1もタグ2も持っていますが、タグ1側を優先したいです

###テーブル
そしてテーブルはこうです

-- 記事
CREATE TABLE posts
    (`ID` int, `user_ID` int, `contents` varchar(100))
;    
INSERT INTO posts
    (`ID`, `user_ID`, `contents`)
VALUES
    (1,  10, '#タグ1 と #タグ2 を持っている記事1の本文です'),
    (2,  20, '#タグ1 を持っている記事2の本文です'),
    (3,  30, '記事3の本文です'),
    (4,  10, '#タグ1 と #タグ3 と #タグ4 を持っている記事4の本文です'),
    (5,  30, '#タグ5 を持っている記事5の本文です'),
    (6,  40, '記事6の本文です'),
    (7,  10, '記事7の本文です'),
    (8,  50, '#タグ1 を持っている記事8の本文です'),
    (9,  10, '#タグ2 と #タグ4 を持っている記事9の本文です'),
    (10, 20, '#タグ1 と #タグ2 と #タグ6 を持っている記事10の本文です')
;

-- タグ
CREATE TABLE tags
    (`ID` int, `tag_name` varchar(100))
;    
INSERT INTO tags
    (`ID`, `tag_name`)
VALUES
    (1, 'タグ1'),
    (2, 'タグ2'),
    (3, 'タグ3'),
    (3, 'タグ4'),
    (3, 'タグ5'),
    (3, 'タグ6')
;

-- タグリレーション
CREATE TABLE tag_relations
    (`tags_ID` int, `contents_ID` int )
;
INSERT INTO tag_relations
    (`tags_ID`, `contents_ID` )
VALUES
    # contents_ID=1が、tag_ID=1とtag_ID=2を持っている
    (1, 1),  (2, 1),
    # contents_ID=2が、tag_ID=1を持っている
    (1, 2),
    # contents_ID=4が、tag_ID=1とtag_ID=3とtag_ID=4を持っている
    (1, 4),  (3, 4),  (4, 4),
    # contents_ID=5が、tag_ID=5を持っている
    (5, 5),
    # contents_ID=8が、tag_ID=1を持っている
    (1, 8),
    # contents_ID=9が、tag_ID=2とtag_ID=4を持っている
    (2, 9),  (4, 9),
    # contents_ID=10が、tag_ID=1とtag_ID=2とtag_ID=6を持っている
    (1, 10), (2, 10), (6, 10)
;

###試したこと
最終的な目的のためには、クエリを一つにまとめずアプリケーション側で二つ実行すればいいのですが、まずそれはなしで、一つのクエリで実現したいと考えています

ならばと、二つをそのまま一つにまとめて、つまりunionで次のようにまとめてみれば実現できるか？と思ったのですが…

惜しくも【※】の部分が欲しい結果とは微妙に異なりまして、ID=10について、「取得経緯のtag_ID」が1でなく2になってしまう結果でした（そもそもunionはあまり推奨されないようですし、他の方法を探しています）

select p.ID, p.contents, '1' AS 取得経緯のtag_ID
from posts p
left join tag_relations tr ON tr.contents_ID = p.ID
left join tags t ON t.ID = tr.tags_ID
where t.tag_name IN ( 'タグ1' ) # 指定タグを持っている
AND p.ID NOT IN ( 1 ) # 指定記事を除外
group by p.ID

union

select p.ID, p.contents, '2' AS 取得経緯のtag_ID
from posts p
left join tag_relations tr ON tr.contents_ID = p.ID
left join tags t ON t.ID = tr.tags_ID
where t.tag_name IN ( 'タグ2' ) # 指定タグを持っている
AND p.ID NOT IN ( 1 ) # 指定記事を除外
group by p.ID

Comments

[maisumakun]

> ID=10について、「取得経緯のtag_ID」が1でなく2になってしまう結果でした

このような場合はどちらを優先させるか、決まっていますか？

[premiummalts]

失礼致しました
小さいものを優先したいと考えています

Answer 1

group_concat()を使用してtag_name を列挙します。
質問のSQLでは、group by でcontentsが指定されていませんので明示した上でのSQLは以下です

select p.ID, p.contents, group_concat(t.tag_name) grp_tag
from posts p
left join tag_relations tr ON tr.contents_ID = p.ID
left join tags t ON t.ID = tr.tags_ID
where t.tag_name IN ( 'タグ1', 'タグ2' ) # 指定タグを持っている
AND p.ID NOT IN ( 1 ) # 指定記事を除外
group by p.ID, p.contents

Comments

[premiummalts]

ありがとうございます！group_concatでやらせていただきます

[premiummalts]

すみません、「group by でcontents」ということですが、これはなぜ必要なのですか？あってもなくても同じ結果のようですが、ない場合にどのような不具合がありえるのでしょうか？

[sazi]

group byする時、select項目を指定する場合にはそのselect項目はgroup byに指定しなければなりません。
mysqlの場合group byの指定が無いselect 項目は主に先頭のみが表示されます。（モードによる）

意図が分かりませんでしたので、不要なら除外して下さい。

[premiummalts]

質問のselect項目にはcontentsがありますから、それを表示するためには本来はgroup byに指定しなければならないということですよね？
しかしmysqlの場合は先頭のみが表示されるためその本来の指定は不要となり、group byに指定せずとも普通にcontentsが表示できている…という感じでしょうか？
なんとなくｗわかりました！ありがとうございました
今後は念のため指定しておくようにします

Answer 2

t.tag_nameを表示すればよいかと。

Comments

[premiummalts]

ありがとうございます
そうするとtag_nameは表示されますが、最終的な目的はどのようにしますか？

[退会済みユーザー]

SQL を組みなおしてください。

[premiummalts]

ありがとうございます
他の方のご回答を待たせて頂きます