MySQLでNOT EXISTSが効かない

Question

###実現したいこと
post_id=5,6が取得されてしまいますが、これをpost_id=5だけにし、post_id=6は除外したいです。

###発生している問題
NOT EXISTSを書いているのにpost_id=6が除外できません。

###テーブル設計
長くて申し訳ございませんが、以下6つのテーブルがあります。

CREATE TABLE posts
    (`post_id` int, `author_id` int, `parent_thread_id` int, `parent_comment_id` int, `kind` varchar(10), `text` varchar(1000), `date` datetime)
;
    
INSERT INTO posts
    (`post_id`, `author_id`, `parent_thread_id`, `parent_comment_id`, `kind`, `text`, `date`)
VALUES
    (1, 2, 0, 0, 'thread',  '#青空 と #雲 が大好きなスレ', '2020-01-01 01:00:00'),
    (2, 2, 1, 0, 'comment', 'いい天気ですね', '2020-01-01 02:00:00'),
    (3, 1, 1, 2, 'reply',   'でも #明日 は #雨 です', '2020-01-01 03:00:00'),
    (4, 3, 0, 0, 'thread',  '青い海で泳ぎたいスレ', '2020-01-01 04:00:00'),
    (5, 1, 0, 0, 'thread',  '山の厳しさを教えたいスレ', '2020-01-01 05:00:00'),
    (6, 2, 5, 0, 'comment', '冬の山は #危険 ですよ', '2020-01-01 06:00:00'),
    (7, 3, 4, 0, 'comment', '日焼け止めが必要ですね', '2020-01-01 07:00:00'),
    (8, 3, 1, 0, 'comment', '私は太陽がすきです', '2020-01-01 08:00:00')
;


CREATE TABLE tags
    (`tag_id` int, `tag_name` varchar(100))
;
    
INSERT INTO tags
    (`tag_id`, `tag_name`)
VALUES
    (1, '明日'),
    (2, '青空'),
    (3, '危険'),
    (4, '雲'),
    (5, '雨')
;


CREATE TABLE tag_relations
    (`relation_id` int, `post_id` int, `tag_id` int)
;
    
INSERT INTO tag_relations
    (`relation_id`, `post_id`, `tag_id`)
VALUES
    #post1がtag2とtag4を持っている
    (1, 1, 2), 
    (2, 1, 4),
    #post3がtag5とtag1を持っている
    (3, 3, 5),
    (4, 3, 1),
    #post6がtag3を持っている
    (5, 6, 3)
;

CREATE TABLE users
    (`user_id` int, `user_name` varchar(50))
;
    
INSERT INTO users
    (`user_id`, `user_name`)
VALUES
    (1, 'Aさん'),
    (2, 'Bさん'),
    (3, 'Cさん')
;


CREATE TABLE actions
    (`action_id` int, `actor_id` int, `target_kind` varchar(20), `target_id` int, `action_name` varchar(10))
;
    
INSERT INTO actions
    (`action_id`, `actor_id`, `target_kind`, `target_id`, `action_name`)
VALUES
    (1, 1, 'user',    2, 'follow'),
    (2, 2, 'user',    1, 'block'),
    (3, 1, 'thread',  4, 'follow'),
    (4, 1, 'tag',     2, 'follow'),
    (5, 3, 'user',    2, 'keep'),
    (6, 2, 'comment', 2, 'keep'),
    (7, 1, 'tag',     3, 'follow')
;


CREATE TABLE limiteds
    (`limited_id` int, `thread_id` int, `target_id` int)
;
    
INSERT INTO limiteds
    (`limited_id`, `thread_id`, `target_id`)
VALUES
    #thread1がuser3に公開されてる
    (1, 1, 3),
    #thread5がuser2とuser3に公開されてる
    (2, 5, 2),
    (3, 5, 3)
;

###該当のソースコード
下記ソースコードで、post_id=5だけにし、post_id=6は除外したいのです。

なぜなら、

post_id=6を投稿したのはauthor_id=2（user2）であり、

user2はuser1をblockしており（actionsテーブルのaction_id=2のレコードがその情報です）、

よってpost_id=6は

➉「user1をblockしてるuser」が投稿したthreadとcommentを除外

に該当するため、除外したいという理由です。

しかし、除外するために➉に書いているNOT EXISTSが効かず困っています。

/*
user1のタイムラインに流すthreadとcommentを【A、B、C、D】に基づき取得する

【A】「投稿」と関係するものを取得
#➀「user1が投稿したthreadとcomment」を取得
#➁「user1が投稿したthread」へのcommentを取得

【B】「follow情報」を元に取得
#➂「user1がfollowしてるuser」が投稿したthreadとcommentを取得
#➃「user1がfollowしているthread」に投稿されたcommentを取得
#➄「user1がfollowしてるtag」を持つthreadとcommentを取得
#➅「user1がfollowしているtag」を持つthreadに投稿されたcommentを取得

【C】「公開されてるもの」だけ取得
#➆「user1に公開されてるthread」を取得
#➇「user1に公開されてるthread」へのcommentを取得

【D】「block関係」は除外
#➈「user1がblockしてるuser」が投稿したthreadとcommentを除外
#➉「user1をblockしてるuser」が投稿したthreadとcommentを除外
*/

SELECT p.post_id, p.author_id, p.parent_thread_id, p.kind, p.text, p.date, t.tag_id, t.tag_name
FROM posts p
LEFT JOIN tag_relations r ON r.post_id=p.post_id
LEFT JOIN tags t ON t.tag_id=r.tag_id

#【A】「投稿」と関係するものを取得
WHERE (
  
  #➀「user1が投稿したthreadとcomment」を取得
  p.author_id=1 AND ( p.kind='thread' OR p.kind='comment' )
  
  #➁「user1が投稿したthread」へのcommentを取得
  OR p.parent_thread_id = (
    SELECT p.post_id
    FROM posts p
    WHERE p.author_id=1 AND p.kind='thread'
  )
)

#【B】「follow情報」を元に取得
OR (
  
  #➂「user1がfollowしてるuser」が投稿したthreadとcommentを取得
  p.author_id = ( 
    SELECT a.target_id
    FROM actions a  
    WHERE a.actor_id=1 AND a.action_name='follow' AND a.target_kind='user'
  )

  #➃「user1がfollowしているthread」に投稿されたcomment
  OR p.parent_thread_id IN (
    SELECT a.target_id
    FROM actions a
    WHERE a.actor_id=1 AND a.action_name='follow' AND a.target_kind='thread'
  )

  #➄「user1がfollowしてるtag」を持つthreadとcommentを取得
  OR t.tag_id IN ( 
    SELECT a.target_id
    FROM actions a
    WHERE a.actor_id=1 AND a.action_name='follow' AND a.target_kind='tag'
  )

  #➅「user1がfollowしているtag」を持つthreadに投稿されたcomment
  OR p.parent_thread_id IN ( 
    SELECT post_id
    FROM tag_relations r
    WHERE tag_id IN (
      SELECT target_id
      FROM actions
      WHERE actor_id=1 AND action_name='follow' AND target_kind='tag'
    )
  )
  
)

#【C】「公開されてるもの」だけ取得
AND(

  #➆「user1に公開されてるthread」を取得
  p.post_id IN (
    SELECT l.thread_id
    FROM limiteds l
    WHERE l.target_id=1
  ) 
  #➇「user1に公開されてるthread」へのcommentを取得
  OR p.parent_thread_id IN (
    SELECT l.thread_id
    FROM limiteds l
    WHERE l.target_id=1
  )

)

#【D】「block関係」は除外
AND(
  
  #➈「user1がblockしてるuser」が投稿したthreadとcommentを除外
  NOT EXISTS (
    SELECT post_id
    FROM posts
    WHERE author_id=(
      SELECT target_id
      FROM actions
      WHERE action_name='block' AND actor_id=1
    )    
  )
  #➉「user1をblockしてるuser」が投稿したthreadとcommentを除外
  OR NOT EXISTS (
    SELECT post_id
    FROM posts
    WHERE author_id=(
      SELECT actor_id
      FROM actions
      WHERE action_name='block' AND target_id=1
    )
  )
  
)

#新しい順に10件取得
ORDER BY date DESC
LIMIT 0, 10

###試したこと
NOT EXISTSが効かないので、以下のようにNOT INを使ってみましたが同じ結果で、他に除外系の方法がわかりません。

#【D】「block関係」は除外
AND(
  
  #➈「user1がblockしてるuser」が投稿したthreadとcommentを除外
  p.author_id NOT IN (
      SELECT target_id
      FROM actions
      WHERE action_name='block' AND actor_id=1
  )
  #➉「user1をblockしてるuser」が投稿したthreadとcommentを除外
  OR p.author_id NOT IN (
      SELECT actor_id
      FROM actions
      WHERE action_name='block' AND target_id=1
  )
  
)

長いうえに素人の手探りなソースコードで見にくいかとは思いますが、どうぞ宜しくお願い致します。

Comments

[Orlofsky]

質問を思った通りに動かないという現象を再現できる最小限の情報に絞っては？

[hoshi-takanori]

条件が複雑すぎてよく分かりませんが、reply は無視していいのでしょうか？ また、tags と join しているので同じ post id が複数返ることもあり得ますが、大丈夫でしょうか。（LIMIT 0, 10 との兼ね合いが…。）

[nyuuinkanja]

Orlofsky様
たしかに、読み取りにくい質問で申し訳ございません。

[nyuuinkanja]

hoshi-takanori様
はい、replyは無視して構いません。

複数の件は仰る通りかと思います。実は別質問（https://teratail.com/questions/249168）で複数でも問題ないとのご意見を頂戴したのですが、問題ありですよね。

するとやはりtag_idやtag_nameは別のSQLで取得し、質問のSQLではtagsやtag_relationsとjoinしないべきでしょうか。（group concatするとtag_nameにカンマがあった場合に困りますしね。）

Answer 1

とりあえず条件が複雑すぎる（かつ、頻繁に変更されることが予想される）ので、こんな感じで各条件を表示して考えてみてはいかがでしょうか。

SELECT post_id, A1, A2, B3, B4, B5, B6, C7, C8, D9, D10,
  (A1 OR A2) OR (B3 OR B4 OR B5 OR B6) AND (C7 OR C8) AND (D9 OR D10) AS "ALL"
FROM (

SELECT p.post_id,

#【A】「投稿」と関係するものを取得

  #➀「user1が投稿したthreadとcomment」を取得
  p.author_id=1 AND ( p.kind='thread' OR p.kind='comment' ) AS A1,

  #➁「user1が投稿したthread」へのcommentを取得
  p.parent_thread_id = (
    SELECT p.post_id
    FROM posts p
    WHERE p.author_id=1 AND p.kind='thread'
  ) AS A2,

#【B】「follow情報」を元に取得

  #➂「user1がfollowしてるuser」が投稿したthreadとcommentを取得
  p.author_id = (
    SELECT a.target_id
    FROM actions a
    WHERE a.actor_id=1 AND a.action_name='follow' AND a.target_kind='user'
  ) AS B3,

  #➃「user1がfollowしているthread」に投稿されたcomment
  p.parent_thread_id IN (
    SELECT a.target_id
    FROM actions a
    WHERE a.actor_id=1 AND a.action_name='follow' AND a.target_kind='thread'
  ) AS B4,

  #➄「user1がfollowしてるtag」を持つthreadとcommentを取得
  t.tag_id IN (
    SELECT a.target_id
    FROM actions a
    WHERE a.actor_id=1 AND a.action_name='follow' AND a.target_kind='tag'
  ) AS B5,

  #➅「user1がfollowしているtag」を持つthreadに投稿されたcomment
  p.parent_thread_id IN (
    SELECT post_id
    FROM tag_relations r
    WHERE tag_id IN (
      SELECT target_id
      FROM actions
      WHERE actor_id=1 AND action_name='follow' AND target_kind='tag'
    )
  ) AS B6,

#【C】「公開されてるもの」だけ取得

  #➆「user1に公開されてるthread」を取得
  p.post_id IN (
    SELECT l.thread_id
    FROM limiteds l
    WHERE l.target_id=1
  ) AS C7,

  #➇「user1に公開されてるthread」へのcommentを取得
  p.parent_thread_id IN (
    SELECT l.thread_id
    FROM limiteds l
    WHERE l.target_id=1
  ) AS C8,

#【D】「block関係」は除外

  #➈「user1がblockしてるuser」が投稿したthreadとcommentを除外
  NOT EXISTS (
    SELECT post_id
    FROM posts
    WHERE author_id=(
      SELECT target_id
      FROM actions
      WHERE action_name='block' AND actor_id=1
    )
  ) AS D9,

  #➉「user1をblockしてるuser」が投稿したthreadとcommentを除外
  NOT EXISTS (
    SELECT post_id
    FROM posts
    WHERE author_id=(
      SELECT actor_id
      FROM actions
      WHERE action_name='block' AND target_id=1
    )
  ) AS D10

FROM posts p
LEFT JOIN tag_relations r ON r.post_id=p.post_id
LEFT JOIN tags t ON t.tag_id=r.tag_id
ORDER BY p.post_id

) x

実行結果

+---------+-----+-----+-----+-----+------+-----+-----+-----+-----+-----+-----+
| post_id | A1  | A2  | B3  | B4  | B5   | B6  | C7  | C8  | D9  | D10 | ALL |
+---------+-----+-----+-----+-----+------+-----+-----+-----+-----+-----+-----+
|       1 |   0 |   0 |   1 |   0 |    1 |   0 |   0 |   0 |   1 |   0 |   0 |
|       1 |   0 |   0 |   1 |   0 |    0 |   0 |   0 |   0 |   1 |   0 |   0 |
|       2 |   0 |   0 |   1 |   0 | NULL |   1 |   0 |   0 |   1 |   0 |   0 |
|       3 |   0 |   0 |   0 |   0 |    0 |   1 |   0 |   0 |   1 |   0 |   0 |
|       3 |   0 |   0 |   0 |   0 |    0 |   1 |   0 |   0 |   1 |   0 |   0 |
|       4 |   0 |   0 |   0 |   0 | NULL |   0 |   0 |   0 |   1 |   0 |   0 |
|       5 |   1 |   0 |   0 |   0 | NULL |   0 |   0 |   0 |   1 |   0 |   1 |
|       6 |   0 |   1 |   1 |   0 |    1 |   0 |   0 |   0 |   1 |   0 |   1 |
|       7 |   0 |   0 |   0 |   1 | NULL |   0 |   0 |   0 |   1 |   0 |   0 |
|       8 |   0 |   0 |   0 |   0 | NULL |   1 |   0 |   0 |   1 |   0 |   0 |
+---------+-----+-----+-----+-----+------+-----+-----+-----+-----+-----+-----+

Comments

[nyuuinkanja]

こんな発想したことありませんでした！今後のSQLライフに大変有用そうなメソッドをどうもありがとうございます。

Answer 2

andとorには優先順位があります。
12.3.1 演算子の優先順位

質問のSQLは
【A】or【B】and【C】and【D】
となっていますので、優先順位を明示する為に()で括ると
【A】or ((【B】and【C】) and【D】)
となります。

質問の条件の結果を分かり易く表すと以下のようになります
where p.post_id in (5,6) or ((p.post_id in(1,2,3,6,7,8) and false) and true)

結果は**post_id in (5,6)**のみが真となります。
※trueとfalseのandは必ずfalseとなります。

not existsが効かないのではなく条件の組み立てが意図通りになっていないという事です。

そもそも条件内容からはnot existsではなくnot in だと思います。
ただそれだけ変更しても結果は変わりません。

希望の抽出とする為には、
【D】の条件についてnot exists からnot in に変更し、更にor を andに変更した上で
(【A】or (【B】and【C】)) and【D】
とすると一応抽出はされますが、それが仕様的に正しいのかどうかは判断はできません。

Comments

[nyuuinkanja]

なるほどです。どうもありがとうございます。
演算子の優先順位については理解できました。

ところで、NOT INの２つを、ORでなくANDでつなげるのはなぜでしょうか。
blockしているかされているか、のいずれかであれば除外したいので、”いずれか”といえば”OR”を使うのではないでしょうか？

実際にORではpost_id=6は除外されませんからANDが正解なのでしょうけど、なぜANDなのか腹落ちできません。

"AND"は、"いずれか"でなく"いずれも"なので、つまり”互いにblockしあっていた場合”になってしまうはずではないかという考えです。

[sazi]

何故そうするかというのを理解した上でやったわけではありません。
単に求める結果にする為に演算子のみを組み替えただけですので。

そもそもa,b,c,dの関係がどうあるべきかは分かりません。
Dを後から追加したのなら、
(【A】or (【B】and【C】)) and【D】
という関係が求めるものですか？
内容からすると
(【A】or【B】) and【C】 and【D】
のような気がしますけど、【C】はfalseなので何も抽出されなくなります

[sazi]

> "AND"は、"いずれか"でなく"いずれも"なので、つまり”互いにblockしあっていた場合”になってしまうはずではないかという考えです。

Notによって補集合を求めていますよね。
ドモルガンの法則によってNotを使用しないようにするとorを使用する事になります。
ですので、集合で考えないと正しい条件には辿り着けません。

[nyuuinkanja]

>集合で考えないと正しい条件には辿り着けません。

たしかに、「blockしてるかされてるをいずれも満たす集合」という条件の和ではなくて、「blockしてる集合とされてる集合の和」という集合の和を示すのがANDですね。失礼いたしました。理解できました。ありがとうございます。

[nyuuinkanja]

このコメントは間違いでした

[nyuuinkanja]

このコメントは間違いでした

[nyuuinkanja]

>(【A】or【B】) and【C】 and【D】
のような気がしますけど

いえ、ご回答の方であっています。【A】に対して【C】の判定は不要です。【B】だけが【C】の判定を必要とします。

ちょうど【A】のその点についてお伺いしたいと思っておりまして、誠に恐れ入りますがお時間よろしいでしょうか…

[nyuuinkanja]

よく考えたら【A】の➀は自身の投稿であり、【D】と関係なく必ず取得するものでした。なのでそれらの判定から外して➀だけ以下のように独立させた方が良いでしょうか？

――――――――――
現状はこうですが
(【A】or (【B】and【C】)) and【D】

こうした方が良いですか？
(➀ or (( ➁ or (【B】and【C】)) and【D】)
――――――――――

後者は【D】の判定を➁～➇とし、➀は必ず取得するので独立させた構成です。
それともこうして➀を独立させるとorが一つ増えますから、やはり現状の方が良いでしょうか？

[sazi]

条件を細かく見ることは出来ない（複雑に見えて解析がしんどい）ので条件についてのアドバイスは控えますが、from句で結合して集合を作り出し、where条件は単純なフィルター(A=B)となるようにしてみる事とです。
where条件でサブクエリーを使用するのは最後の手段位にすると条件はすっきりすると思います。

[nyuuinkanja]

解析しんどいですね。冗長なコードで申し訳ございません。where条件は単純なフィルター、なるほど。そのお考えをヒントに自分なりにこれから頑張ってみたいと思います。ご回答ありがとうございました。

[nyuuinkanja]

こんばんは。仰っていていた「JOINしてからWHEREでフィルター」という流れについて少しよろしいでしょうか。

下記リンクでその流れを組んでいるのですが、行き詰った部分として74行目をご覧いただきたく思います。
http://sqlfiddle.com/#!9/d3afbc/91

つまり➁のフィルターをかけようとしている部分になりますが、

OR w.kind='comment' AND w.parent_thread_id=(※)

の「※」でやはりサブクエリ―のSELECTが必要になりませんでしょうか？

現状は

OR w.kind='comment' AND w.parent_thread_id=(w.author_id=1 AND w.kind='thread')

と書いていますが、これでは

#➁「user1が投稿したthread」へのcommentを取得

は実現できず、やはり「※（user1が投稿したthread）」という条件を指定するためにはサブクエリ―のSELECTを書く以外の方法が思いつけず、行き詰っているのです。

サブクエリ―のSELECTを書かずとも、この部分はJOINした結果からうまく拾えますでしょうか？

それ以降もおかしいとは思いますが、だいたいこの部分のアレンジでなんとかなりそうなので、恐れ入りますがこの部分についてアドバイス頂戴できましたら幸いです。