【前提・実現したいこと】
MySQL5.7 にて、以下の【条件】でpostsテーブルから取得ができました。
しかし、【現状のコード】はWHEREの対象がpostsのすべてになっていると思います。
そこで、➀➁➂➃に限定したpostsを対象に、➄➅でWHEREをかけることができれば早くなると思っているのですが、そのための【試したコード】で躓いています。
###【条件】
★ 取得条件
➀post_kind = 1 のもの
AND
指定ユーザが ➁投稿したもの OR ➂フォローしたもの OR ➃保存したもの
☆ 除外条件
➄削除されたもの( is_trash=1 のもの )
OR
➅ブロックした、されたユーザによるもの
【CREATE、INSERT】
ユーザ、投稿、アクション(フォロー、保存、ブロック)
という5つのテーブルで構成されます。
SQL
1-- ユーザ 2CREATE TABLE users( 3 `ID` INT NOT NULL AUTO_INCREMENT, 4 `name` VARCHAR(100) NOT NULL, 5 PRIMARY KEY (`ID`) 6); 7INSERT INTO users 8 (`name`) 9VALUES 10 ('A子'),('B子'),('C子'),('D子'),('E子') 11; 12 13-- 投稿 14CREATE TABLE posts( 15 `ID` INT NOT NULL AUTO_INCREMENT, 16 `user_ID` INT NOT NULL, 17 `title` VARCHAR(100) NOT NULL, 18 `post_kind` VARCHAR(10) NOT NULL, 19 `is_trash` INT(1) NOT NULL DEFAULT 0, 20 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 21 PRIMARY KEY (`ID`), 22 INDEX (user_ID), 23 CONSTRAINT FOREIGN KEY (`user_ID`) REFERENCES users(`ID`) 24); 25INSERT INTO posts 26 (`user_ID`, `title`, `post_kind`) 27VALUES 28 (4, 'a', 1), (2, 'b', 1), (2, 'c', 1), (3, 'd', 1), (1, 'e', 1), 29 (5, 'd', 2), (2, 'f', 2), (5, 'g', 2), (2, 'h', 2), (1, 'j', 2) 30; 31 32-- 保存した投稿 33CREATE TABLE keeps( 34 `user_ID` INT NOT NULL, 35 `post_ID` INT NOT NULL, 36 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 37 PRIMARY KEY (`user_ID`,`post_ID`), 38 INDEX (`user_ID`,`post_ID`), 39 CONSTRAINT FOREIGN KEY (`user_ID`) REFERENCES users(`ID`), 40 CONSTRAINT FOREIGN KEY (`post_ID`) REFERENCES posts(`ID`) 41); 42INSERT INTO keeps 43 (`user_ID`,`post_ID`) 44VALUES 45 (3,1),(2,4),(2,1),(1,1),(4,1),(2,2),(3,5),(5,1),(3,2),(4,5) 46; 47 48-- フォローした投稿 49CREATE TABLE follows( 50 `user_ID` INT NOT NULL, 51 `post_ID` INT NOT NULL, 52 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 53 PRIMARY KEY (`user_ID`,`post_ID`), 54 INDEX (`user_ID`,`post_ID`), 55 CONSTRAINT FOREIGN KEY (`user_ID`) REFERENCES users(`ID`), 56 CONSTRAINT FOREIGN KEY (`post_ID`) REFERENCES posts(`ID`) 57); 58INSERT INTO follows 59 (`user_ID`,`post_ID`) 60VALUES 61 (5,3),(4,4),(2,2),(2,3),(1,4),(2,5),(5,1),(2,4),(5,4),(3,1) 62; 63 64-- ブロックしたユーザ 65CREATE TABLE blocks( 66 `user_ID` INT NOT NULL, -- した側 67 `user_ID2` INT NOT NULL, -- された側 68 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 69 PRIMARY KEY (`user_ID`,`user_ID2`), 70 INDEX (`user_ID2`,`user_ID`), 71 CONSTRAINT FOREIGN KEY (`user_ID`) REFERENCES users(`ID`), 72 CONSTRAINT FOREIGN KEY (`user_ID2`) REFERENCES users(`ID`) 73); 74INSERT INTO blocks 75 (`user_ID`,`user_ID2`) 76VALUES 77 (1,5),(1,2),(3,4),(5,1),(4,1) 78;
###【現状のコード】
このデータから条件に合致した投稿を得るSELECTですが、先述のようにWHEREの対象がpostsのすべてになっていると思います。
SQL
1SELECT 2 p1.ID post_id, 3 CASE WHEN p1.post_kind = 1 THEN 'dog' WHEN p1.post_kind = 2 THEN 'cat' END post_kind, 4 k.created_at date_keeping, 5 f.created_at date_following, 6 p2.created_at date_posting, 7 u.ID author_id, 8 u.name author_name, 9 -- 以下2つのblock日はSELECTに不要でした ( 2020/08/15 14:21 に削除 ) 10 -- b1.created_at date_blocking, 11 -- b2.created_at date_blocked 12 13FROM 14 posts p1 15 16 -- 投稿者情報を連結 17 LEFT JOIN users u ON u.ID = p1.user_ID 18 19 -- 2(B子) のブロック情報を連結 20 LEFT JOIN blocks b1 ON b1.user_ID = 2 AND b1.user_ID2 = p1.user_ID 21 LEFT JOIN blocks b2 ON b2.user_ID2 = 2 AND b2.user_ID = p1.user_ID 22 23 -- 2(B子) の keep 情報を連結 24 LEFT JOIN keeps k ON k.user_ID = 2 AND k.post_ID = p1.ID 25 26 -- 2(B子) の follow 情報を連結 27 LEFT JOIN follows f ON f.user_ID = 2 AND f.post_ID = p1.ID 28 29 -- 2(B子) の 投稿 情報を連結 30 LEFT JOIN posts p2 ON p2.user_ID = 2 AND p2.ID = p1.ID 31 32-- 以下のWHEREは間違いです ( 2020/08/15 14:07 に削除 ) 33/* 34WHERE 35 p1.post_kind = 1 -- ➀ 36 AND p1.is_trash = 0 -- ➄ 37 AND b1.created_at IS NULL -- ➅ 38 AND b2.created_at IS NULL -- ➅ 39 -- 以下忘れていたので追記( 2020/08/14 13:34 に追加 ) 40 AND ( k.created_at IS NOT NULL OR f.created_at IS NOT NULL ) -- ➂➃ 41*/ 42 43-- 以下のWHEREが正しいです ( 2020/08/15 14:07 に追加 ) 44WHERE 45 p1.post_kind = 1 -- ➀ 46 AND p1.is_trash = 0 -- ➄ 47 AND ( 48 ( 49 b1.created_at IS NULL -- ➅ 50 AND b2.created_at IS NULL -- ➅ 51 AND ( k.created_at IS NOT NULL OR f.created_at IS NOT NULL ) -- ➂➃ 52 ) 53 OR p2.created_at IS NOT NULL -- ➁ 54 ) 55
【試したコード】
➀➁➂➃に限定したpostsを対象に、➃➄でWHEREをかけようと思い、以下、下の3つでINNSER JOINを使ったのですが、これは連続では使えないようで、どうすればいいかわからず躓いています。
SQL
1FROM 2 posts p1 3 4 -- 投稿者情報を連結 5 LEFT JOIN users u ON u.ID = p1.user_ID 6 7 -- 2(B子) のブロック情報を連結 8 LEFT JOIN blocks b1 ON b1.user_ID = 2 AND b1.user_ID2 = p1.user_ID 9 LEFT JOIN blocks b2 ON b2.user_ID2 = 2 AND b2.user_ID = p1.user_ID 10 11 -- 2(B子) の keep したものに限定 12 INNER JOIN keeps k ON k.user_ID = 2 AND k.post_ID = p1.ID 13 14 -- 2(B子) の follow したものに限定 15 INNER JOIN follows f ON f.user_ID = 2 AND f.post_ID = p1.ID 16 17 -- 2(B子) の 投稿 したものに限定 18 INNER JOIN posts p2 ON p2.user_ID = 2 AND p2.ID = p1.ID
自分なりの考えで【試したコード】の書き方になりましたが、INNER JOINよりも良い方法などあればぜひ知りたいです。
とにかく最適な取得は何かについて勉強されていただければと思っていますので、ご回答宜しくお願い申し上げます。