MySQL、join だけでリレーションに対して絞り込み検索したい

Question

例えば下記のようなデータがあるとして(本来はテーブルですが、便宜上jsonにしています)、

users = [
    {
        "user_name": "userA",
        "posts": [
            {
                "post_name": "postA",
                "comments": [
                    {
                        "body": "foo"
                    },
                    {
                        "body": "bar"
                    }
                ]
            }
        ]
    },
    {
        "user_name": "userB",
        "posts": [
            {
                "post_name": "postB",
                "comments": [
                    {
                        "body": "foo"
                    },
                    {
                        "body": "foo"
                    }
                ]
            }
        ]
    }
]

(上記の json データだと紛らわしいとの指摘を受けましたので、以下テーブルデータも併記しました。)
users テーブル:

id	name
1	userA
2	userB

posts テーブル

id	user_id
1	1
2	2

comments テーブル

id	post_id	body
1	1	foo
2	1	bar
3	2	foo
4	2	foo

このデータに対して、以下のように検索したいです。

1. postsを持つusers;
2. さらにそのpostsがcommentsを持つ;
3. さらにそのcommentsのすべては "foo" を含む.

例のデータの場合ですと、上記条件をクリアしたユーザはuserBとなります。

サブクエでできるのは分かりますが、パフォーマンスが良くなく、joinで実現できないでしょうか？
(上記はのサンプルのリレーションシップは3層だけですが、実際はもっとリレーションシップが深いです。)
それか、別のもっといいやり方はあるのでしょうか？

Comments

[yambejp]

jsonとmysqlの関係がいまいちどうしたいかわかりません。json型のカラムにご指示のデータを流し込んでいいのでしょうか？それとも配列の要素ごとを１データずつ保持するのでしょうか？

[xipx_osx]

jsonはあくまでも疑似データです、本当はテーブルです。こちらのほうがリレーションシップを分かりやすく表現できると思い、jsonにしました。

[yambejp]

mysql5.7以降にはjson型カラムがあるので、質問のデータ提示は紛らわしいですね。sqlのデータを提示するならcreate table ＋ insert intoもしくは所定のtableを利用して提示するようにしてください

[xipx_osx]

なるほどですね、、テーブルデータを付加しました。

Answer 1

commentsがあって、'foo'以外が無いものとすれば、commentsが全て'foo'という事になります。
※commentsの有る無しが関係ないなら、最後の条件は不要。

select *
from users
where not exists(
       select 1 from posts inner join comments on posts.id=comments.post_id
       where posts.user_id=users.id and comments.body<>'foo'
      )
  and exists(
       select 1 from posts inner join comments on posts.id=comments.post_id
       where posts.user_id=users.id
      ) 

追記

users の行を求めたいのだから、keyのレベルを合わせるのにはサブクエリーが必須になるんですよね。
一応別パターン

select *
from users inner join (
       select user_id from posts inner join comments on posts.id=comments.post_id
       group by user_id 
       having count(*)=sum(case when comments.body='foo' then 1 else 0 end)
     ) cond on cond.user_id=users.id

Comments

[xipx_osx]

ありがとうございます！
実は私のいう「サブクエリのやり方」がこれです。
いわゆる`correlated-subquery`を避けたくて…(パフォーマンスの悪化がひどいです)
やはり join だけでは実現難しいのでしょうか…？

[sazi]

サブクエリーといってもexistsは比較的高速だと思いますけど。
何に対してのパフォーマンス悪化なのかはは分かりませんが、適切なインデックスが設定されていないのでｈないでしょうか。

Answer 2

こんな感じでしょうか？（あれ？既存の回答と同じかな？）

• 元データ

create table users(id int primary key,name varchar(10),index(name));
insert into users values(1,'userA'),(2,'userB'),(3,'userC'),(4,'userD'),(5,'userE');  
create table posts(id int primary key,user_id int,index(user_id));
insert into posts values(1,1),(2,2),(3,3),(4,4); 
create table comments(id int primary key,post_id int,body varchar(30),index(post_id,body));
insert into comments values(1,1,'foo'),(2,1,'bar'),(3,2,'foo'),(4,2,'foo'),(5,3,'foo');

※userEはpostsがない
※userDはpostsがあるけどpostsがcommentsを持っていない
※userAはcommentsがfooを含んでいないものを持っている

• 検索

select * from users as t1
inner join posts as t2 on t1.id=t2.user_id
and exists(
select 1 from comments
group by post_id
having sum((body regexp 'foo'))=count(*)
and post_id=t2.id
);

※ロジックがおかしかったので修正しました

Comments

[yambejp]

つまりfooを含まないcommentsをもつid以外のpostsに対してusersを拾う

[xipx_osx]

ありがとうございます！なるほど！こういう書き方もできたんですね。
やはり`correlated-subquery`は避けられないのでしょうか。。？

[yambejp]

なんかおかしかったので修正しました。
インデックスを効かせましたがまだ遅そうですか？

Answer 3

######修正分

サブクエリで遅いというのは、こんな感じにした場合ことでしょうか？
SQLがあると、アドバイスがつきやすいかもしれません。

select ...
from users
join posts
  on ...
join (select ... // fooの数とコメントの数が一致していれば、postsとcommentsをjoinできるような要素を出力する
      from comments
      goup by postid) as filterdComments  
  on ...
join comments
  on ...

Comments

[xipx_osx]

味噌は **commentsのすべては "foo" を含む.** ところです。
お示しいただいた例ですと、おそらく ALL ではなく、SOME になるかと。

[退会済みユーザー]

commentが全てfooの場合のpostが欲しいということでしょうか？

[xipx_osx]

「すべての post が持つすべての comment がfooを含む」を持つユーザ、が欲しいということです。

[退会済みユーザー]

なるほど。ごめんなさい、質問に書かれているのを見落としてしまっていました。私も回答しましたが、saziさんの回答のほうがスマートに思えるので、そちらを支持します。