質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.91%

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 792

score 13

例えば下記のようなデータがあるとして(本来はテーブルですが、便宜上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. さらにそのpostscommentsを持つ;
  3. さらにそのcommentsすべては "foo" を含む.

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yambejp

    2018/10/24 18:15

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

    キャンセル

  • xipx_osx

    2018/10/24 19:00 編集

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

    キャンセル

  • yambejp

    2018/10/24 19:10

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

    キャンセル

  • xipx_osx

    2018/10/24 20:36

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

    キャンセル

回答 3

+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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/10/24 19:12 編集

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

    キャンセル

  • 2018/10/25 00:53

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

    キャンセル

checkベストアンサー

0

こんな感じでしょうか?(あれ?既存の回答と同じかな?)

  • 元データ
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
);


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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/10/24 21:15

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

    キャンセル

  • 2018/10/24 21:31

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

    キャンセル

  • 2018/10/25 10:05

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

    キャンセル

0

修正分

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/10/24 17:06 編集

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

    キャンセル

  • 2018/10/24 17:11

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

    キャンセル

  • 2018/10/24 18:58 編集

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

    キャンセル

  • 2018/10/24 19:02

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.91%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る