MySQLで「対象スレッドの投稿者でない者」が投稿したコメントの有無を確認したい

Question

###前提
掲示板を作っていて、「対象スレッドの投稿者でない者」が投稿したコメントの有無を確認したいという目的がございます。

例えばスレッドID=1に、その投稿者以外がコメントしていた場合、「スレッド管理者以外のコメントがあります」といった表示をしたいイメージです。

###実現したいこと
現状は「①スレッドのIDが与えられるパターン」なのですが、
これを「②コメントのIDが与えられるパターン」のコードに変更したいです。

つまり
「スレッドIDで対象スレッドを決める」のではなく、
「コメントIDからそのコメントが投稿されたスレッドIDを得て、そのスレッドIDで対象スレッドを決める」という変更です。

###テーブル
テーブルにはすべての投稿が入るp_mattersがあり、そのサブタイプとしてp_threadsやp_commentsがあります。

-- 投稿 (スーパータイプ)
CREATE TABLE p_matters (
  `ID` int
  ,`user_ID` int
  ,`kind` int
  ,`sentence` varchar(1000)
  ,`created_at` DATETIME(6)
  ,PRIMARY KEY (`ID`)
  ,INDEX idx_p_matters_1 (`kind`)
  ,CONSTRAINT fk_p_matters_1 FOREIGN KEY (user_ID) REFERENCES users (ID) # 2020/11/07 03:33 追記
);    
INSERT INTO p_matters
    (`ID`, `user_ID`, `kind`, `sentence`, `created_at`)
VALUES
    # thread は kind=1 で comment は kind=2
    (1,  3,  1, '猫について語りましょう', '2020-01-01 12:00:00.123456'),
    (2,  3,  1, '犬について語りましょう', '2020-01-02 12:00:00.123456'),
    (3,  4,  2, '猫はいいです',          '2020-01-03 12:00:00.123456'),
    (4,  1,  2, 'たしかに猫は最高です',   '2020-01-04 12:00:00.123456'),
    (5,  2,  1, '鰐について語りましょう', '2020-01-05 12:00:00.123456'),
    (6,  3,  2, '鰐は怖いです',          '2020-01-06 12:00:00.123456'),
    (7,  1,  2, '犬はやわらかいです',     '2020-01-07 12:00:00.123456'),
    (8,  3,  2, '猫は液体です',          '2020-01-08 12:00:00.123456'),
    (9,  2,  2, '猫は液体ではないです',  '2020-01-09 12:00:00.123456'),
    (10, 4,  2, '鰐は怖くないです',      '2020-01-10 12:00:00.123456')
;

-- 投稿 (サブタイプ/スレッド)
CREATE TABLE p_threads (
  `matter_ID` int
  ,`title` varchar(100)
  ,PRIMARY KEY (`matter_ID`)
  ,CONSTRAINT fk_p_threads_1 FOREIGN KEY (`matter_ID`) REFERENCES p_matters(`ID`) # 2020/11/06 23:18 追記
);   
INSERT INTO p_threads
    (`matter_ID`, `title`)
VALUES
    (1, '猫スレ'), (2, '犬スレ'), (5, '鰐スレ')
;

-- 投稿 (サブタイプ/コメント)
CREATE TABLE p_comments (
  `matter_ID` int
  ,`thread_ID` int
  ,PRIMARY KEY (`matter_ID`)
  ,CONSTRAINT fk_p_comments_1 FOREIGN KEY (`matter_ID`) REFERENCES p_matters(`ID`)
  ,CONSTRAINT fk_p_comments_2 FOREIGN KEY (`thread_ID`) REFERENCES p_threads(`matter_ID`)
);   
INSERT INTO p_comments
    (`matter_ID`, `thread_ID`)
VALUES
    (3, 1),(4, 1),(6, 5),(7, 2),(8, 1),(9, 1),(10, 5)
;

###該当のソースコード
現状の「①スレッドのIDが与えられるパターン」がこちらです。

実行 → http://sqlfiddle.com/#!9/ec5910/2

/*
- 与えられる値
スレッド ID=1 が与えられます

- 実行したいSQL文
その投稿者である user_ID=3 以外が投稿したコメントを取得します

- 求める結果
結果は ID=3,4,9 となります
*/

# スレッドへのコメントを取得
SELECT  *
FROM	p_matters matters1
        INNER JOIN p_comments comments
        ON comments.matter_ID = matters1.ID
        AND comments.thread_ID = 1

# ただしそのコメントの投稿者は次を含まない
WHERE   matters1.user_ID NOT IN (
	
        # 同スレッドの投稿者
        SELECT  matters2.user_ID
        FROM    p_matters matters2
                LEFT JOIN p_threads threads
                ON threads.matter_ID = matters2.ID
        WHERE   threads.matter_ID = 1
)

###試したこと
上記を「②コメントのIDが与えられるパターン」のコードに変更したいわけで次のように試しました。

上記はスレッドIDが与えられますが、こちらはコメントIDしか与えられないため、対象スレッドを判断するためにSELECTを増やさないといけないと思います。

一応できはしたのですが…

SELECTをこうして増やしますと# ここでスレッド ID=1 を取得というSELECTが2か所になってしまい、この重複に冗長さを覚えています。

実行 → http://sqlfiddle.com/#!9/ec5910/4

/*
- 与えられる値
コメント ID=3 が与えられます

- 実行したいSQL文
そのスレッドである ID=1 を取得し
その投稿者である user_ID=3 以外が投稿したコメントを取得します

- 求める結果
結果は ID=3,4,9 となります
*/

# スレッドへのコメントを取得
SELECT  *
FROM	p_matters matters1
        INNER JOIN p_comments comments
        ON comments.matter_ID = matters1.ID
        AND comments.thread_ID = (
                # ここでスレッド ID=1 を取得
                SELECT	thread_ID
                FROM	p_comments
                WHERE	matter_ID = 3
        )

# ただしそのコメントの投稿者は次を含まない
WHERE   matters1.user_ID NOT IN (
	
        # 同スレッドの投稿者
        SELECT  matters2.user_ID
        FROM    p_matters matters2
                LEFT JOIN p_threads threads
                ON threads.matter_ID = matters2.ID
        WHERE	threads.matter_ID = (   
                # ここでスレッド ID=1 を取得
                SELECT	thread_ID
                FROM	p_comments
                WHERE	matter_ID = 3
        )
)

改めまして、上記「②コメントのIDが与えられるパターン」のコードにつきまして、この冗長さを回避するとしたらどんな書き方が良いでしょうか。

アドバイスよろしくお願い致します。

Comments

[sazi]

なんで、p_threads の主キーが`matter_ID`なんですか。
ドメイン管理的にはIDですけどね。
SQL考える上で、邪魔でしょうがないです。という訳で挫折しました。

[ikatako]

初心者設計ですみません。よろしければ教えてください。

もしそちらをmatter_IDでなくIDとした場合、p_commentsのカラムは
「matter_ID,thread_ID」でなく
「ID,thread_ID」とする感じでしょうか？

もしそうであれば、その「p_commentsのID」が、「p_comments自身のIDなのか、それともp_mattersのIDなのか」がわかりにくくなりませんでしょうか？それをわかりやすいと思って「matter_ID,thread_ID」とした。というのが初心者なりに考えた設計の理由になります。

もしかしてこのわかりにくさは、外部キーでカバーするのが一般の設計ですか？

[ikatako]

つまり
「matter_ID,thread_ID」でなく
「ID,thread_ID」としておいて、

「p_comments自身のIDなのか、それともp_mattersのIDなのか」がわからない

と一瞬なりますが

「p_commentsのIDに、p_mattersへの外部キーがあるから、ここはp_mattersのIDだ」ということがわかる

という考え方になりますか？

[sazi]

>もしそちらをmatter_IDでなくIDとした場合、p_commentsのカラムは
>「matter_ID,thread_ID」でなく
>「ID,thread_ID」とする感じでしょうか？
p_commentsは双方への外部キーだからそのままでおかしくないですよ。

それよりも、SQL中に
> p_matters matters2
> LEFT JOIN p_threads threads
> ON threads.matter_ID = matters2.ID
とあり、ON threads.matter_ID = matters2.ID
という事は、threads.matter_ID はmatters.IDへの外部キーという事ですか？

[ikatako]

> という事は、threads.matter_ID はmatters.IDへの外部キーという事ですか？
イエスです。もしかして、それも何かおかしいでしょうか。
p_matters は、 p_threads 並びに p_comments のスーパータイプとして作りましたのでそのような設計と致しました。

[sazi]

>イエスです
では、以下のようした方が良いと思います。
-- 投稿 (サブタイプ/スレッド)
CREATE TABLE p_threads (
`ID` int
,`matter_ID` int
,`title` varchar(100)
,PRIMARY KEY (`ID`)
,CONSTRAINT fk_p_comments_1 FOREIGN KEY (`matter_ID`) REFERENCES p_matters(`ID`)
);
-- 投稿 (サブタイプ/コメント)
CREATE TABLE p_comments (
`ID` int
,`matter_ID` int
,`thread_ID` int
,PRIMARY KEY (`ID`)
,CONSTRAINT fk_p_comments_1 FOREIGN KEY (`matter_ID`) REFERENCES p_matters(`ID`)
,CONSTRAINT fk_p_comments_2 FOREIGN KEY (`thread_ID`) REFERENCES p_threads(`ID`)
);

[sazi]

一意性の担保は、別途ユニーク制約で。

[ikatako]

確かにp_tereadsの外部キーを失念していました。ありがとうございます。（質問文に「# 2020/11/06 23:18 追記」としておきました。）

しかし、別途「ID」を新たに足すのでしょうか？その「ID」にはんな値が想定されているのですか？

[sazi]

オートナンバーで良いかと思います。
ただ、敢えてとは言いません。
そのままナチュラルキーでも良いかと思います。

[sazi]

threadじゃなくて、p_matters がスーパータイプなのが非常に違和感を感じます。

[ikatako]

オートインクリメントのIDですが、そちら何のためのものなのでしょうか？
当初の「matter_IDの代わりにIDにした方がいい」というのは合点がいきますが、「matter_IDとは別途IDを追加した方がいい」というのは意図が掴めなくて…

[ikatako]

>threadじゃなくて、p_matters がスーパータイプなのが非常に違和感を感じます

他に返信テーブルとしてp_replyなどもありまして、「それらすべての投稿タイプが共通で持つカラム」の意味で「p_matters」というスーパータイプを設けました。

例えばスレッドテーブルにしかないカラム（例えばそのスレッドの先頭に固定するコメントのID）などのカラムがあり、このカラムはコメントテーブルや返信テーブルに不要です。

スレッドテーブルをスーパータイプにしてしまうと、コメントや返信のレコードでこのカラムにNULLが入ってしまいますので、NULLを避けるために、すべての投稿タイプが共通で持つカラムを抽出して、p_mattersというスーパータイプになっています。

[ikatako]

…おかしいでしょうか。テーブル設計から見直す覚悟もあるので、どうぞ遠慮なく仰ってください。

[sazi]

詳細については理解していなので、単に名称から違和感を感じているだけです。

[ikatako]

たしかにmatterという名称が上位に置かれるのはおかしいですよね！笑
ご指摘ありがとうございます。

Answer 1

SELECTをこうして増やしますと# ここでスレッド ID=1 を取得というSELECTが2か所になってしまい、この重複に冗長さを覚えています。

条件はよくわからないので、単に記述を変えただけのものです。

select  *
from  p_matters matters
      inner join p_comments comments
      on  comments.matter_id = matters.id
      inner join p_comments comments2
      on  comments.thread_id=comments2.thread_id
where not exists(
        select  1
        from  p_matters matters2
              left join p_threads threads
              on  threads.matter_id = matters2.id
              left join p_comments comments
              on  threads.matter_id = comments.thread_id 
        where comments.matter_id = comments2.matter_id
          and matters2.user_id=matters.user_id
      )
  and comments2.matter_id = 3 

Comments

[ikatako]

ありがとうございます。さすが、想定通りの取得となりました。
EXPLAINしますとmatters2でフルインデックススキャン（typeがindex）が見つかりましてここだけ改善したいのですが、これはサブクエリ内ですから避けられないでしょうか？

[sazi]

p_mattersに(id,user_id)若しくは(user_id)のインデックスを追加してみて下さい

[ikatako]

はい、インデックスを追加しての「typeがindex」でして、追加前は「typeがall」でした。

下図の左がご回答の「typeがall」で、右がそのあとインデックスを追加しての「typeがindex」です。
https://imgur.com/a/mQnI0gi

[sazi]

質問にない、is_trushが条件にあるようですが？
追加したインデックスの内容は？
使用されているfk_p_matters_1 はusersへの外部参照であり、SQL上usersが出てこないので参照先がNullなのです。

[ikatako]

> 質問にない、is_trushが条件にあるようですが？

is_trashはレコードが削除されたことを 0|1 で示すp_mattersのカラムですが、SQLにあまり影響ないと判断し質問から割愛してしまいました。失礼いたしました。

> 追加したインデックスの内容は？

追加したインデックスは「# 2020/11/07 03:33 追記」とある行のfk_p_matters_1になります。

> 使用されているfk_p_matters_1 はusersへの外部参照であり、SQL上usersが出てこないので参照先がNullなのです。

SQL上usersを出すというと、次の「#以下を追加」でusersをJOINするという意味かと思いましたが、やはり画像右と同様の結果でした。この意味とは違うでしょうか。

from p_matters matters2
left join p_threads threads
on threads.matter_id = matters2.id
left join p_comments comments
on threads.matter_id = comments.thread_id
# 以下を追加
left join users u
on u.ID = matters2.user_ID

[ikatako]

というか実行計画の改善は質問内容ではありませんでしたね。あれこれ質問してしまい申し訳ございません。
自分でいろいろ調べてみますので、どうぞお気になさらず。