MySQLのtableのjoinについて
今日はMySQLの使い方、特にtableのjoinについてメモします。
MySQLではtableをjoinすることで、新しいエンティティを作ることができる。
例えば、topic_tableがとある。
+----+---------------+-------------+---------+
| id | name | category_id | user_id |
+----+---------------+-------------+---------+
| 1 | topic test 1 | 5 | 9 |
| 2 | topic test 2 | 6 | 10 |
| 3 | topic test 3 | 7 | 11 |
| 4 | topic test 4 | 1 | 12 |
| 5 | topic test 5 | 2 | 13 |
| 6 | topic test 6 | 3 | 14 |
| 7 | topic test 7 | 4 | 15 |
| 8 | topic test 8 | 5 | 16 |
| 9 | topic test 9 | 6 | 17 |
| 10 | topic test 10 | 7 | 18 |
| 11 | topic test 11 | 1 | 19 |
| 12 | topic test 12 | 2 | 20 |
| 13 | topic test 13 | 3 | 1 |
| 14 | topic test 14 | 4 | 2 |
| 15 | topic test 15 | 5 | 3 |
| 16 | topic test 16 | 6 | 4 |
| 17 | topic test 17 | 7 | 5 |
| 18 | topic test 18 | 1 | 6 |
| 19 | topic test 19 | 2 | 7 |
| 20 | topic test 20 | 3 | 8 |
| 21 | topic test 21 | 4 | 9 |
| 22 | topic test 22 | 5 | 10 |
| 23 | topic test 23 | 6 | 11 |
| 24 | topic test 24 | 7 | 12 |
| 25 | topic test 25 | 1 | 13 |
| 26 | topic test 26 | 2 | 14 |
| 27 | topic test 27 | 3 | 15 |
| 28 | topic test 28 | 4 | 16 |
| 29 | topic test 29 | 5 | 17 |
| 30 | topic test 30 | 6 | 18 |
| 31 | topic test 31 | 7 | 19 |
| 32 | topic test 32 | 1 | 20 |
| 33 | topic test 33 | 2 | 1 |
| 34 | topic test 34 | 3 | 2 |
| 35 | topic test 35 | 4 | 3 |
| 36 | topic test 36 | 5 | 4 |
| 37 | topic test 37 | 6 | 5 |
| 38 | topic test 38 | 7 | 6 |
| 39 | topic test 39 | 1 | 7 |
| 40 | topic test 40 | 2 | 8 |
| 41 | topic test 1 | 5 | 9 |
| 42 | topic test 2 | 6 | 10 |
| 43 | topic test 3 | 7 | 11 |
| 44 | topic test 4 | 1 | 12 |
| 45 | topic test 5 | 2 | 13 |
| 46 | topic test 6 | 3 | 14 |
| 47 | topic test 7 | 4 | 15 |
| 48 | topic test 8 | 5 | 16 |
| 49 | topic test 9 | 6 | 17 |
| 50 | topic test 10 | 7 | 18 |
| 51 | topic test 11 | 1 | 19 |
| 52 | topic test 12 | 2 | 20 |
| 53 | topic test 13 | 3 | 1 |
| 54 | topic test 14 | 4 | 2 |
| 55 | topic test 15 | 5 | 3 |
| 56 | topic test 16 | 6 | 4 |
| 57 | topic test 17 | 7 | 5 |
| 58 | topic test 18 | 1 | 6 |
| 59 | topic test 19 | 2 | 7 |
| 60 | topic test 20 | 3 | 8 |
| 61 | topic test 21 | 4 | 9 |
| 62 | topic test 22 | 5 | 10 |
| 63 | topic test 23 | 6 | 11 |
| 64 | topic test 24 | 7 | 12 |
| 65 | topic test 25 | 1 | 13 |
| 66 | topic test 26 | 2 | 14 |
| 67 | topic test 27 | 3 | 15 |
| 68 | topic test 28 | 4 | 16 |
| 69 | topic test 29 | 5 | 17 |
| 70 | topic test 30 | 6 | 18 |
| 71 | topic test 31 | 7 | 19 |
| 72 | topic test 32 | 1 | 20 |
| 73 | topic test 33 | 2 | 1 |
| 74 | topic test 34 | 3 | 2 |
| 75 | topic test 35 | 4 | 3 |
| 76 | topic test 36 | 5 | 4 |
| 77 | topic test 37 | 6 | 5 |
| 78 | topic test 38 | 7 | 6 |
| 79 | topic test 39 | 1 | 7 |
| 80 | topic test 40 | 2 | 8 |
+----+---------------+-------------+---------+
（＊詳しく説明するためテストデータを必要以上に入れている。）
このでのtopicは、ユーザーがあるカテゴリーの下で作るので、"category_id"と"user_id"でどのカテゴリーにどのユーザーが作ったのかを見分けることができる。
次にユーザーテーブルを見てみる。
+----+--------------------+------------------------+
| id | name | email |
+----+--------------------+------------------------+
| 1 | usertest1 | usertest1@gmail.com |
| 2 | usertest2 | usertest2@gmail.com |
| 3 | usertest3 | usertest3@gmail.com |
| 4 | usertest4 | usertest4@gmail.com |
| 5 | usertest5 | usertest5@gmail.com |
| 6 | usertest6 | usertest6@gmail.com |
| 7 | usertest7 | usertest7@gmail.com |
| 8 | usertest8 | usertest8@gmail.com |
| 9 | usertest9 | usertest9@gmail.com |
| 10 | usertest10 | usertest10@gmail.com |
| 11 | usertest11 | usertest11@gmail.com |
| 12 | usertest12 | usertest12@gmail.com |
| 13 | usertest13 | usertest13@gmail.com |
| 14 | usertest14 | usertest14@gmail.com |
| 15 | usertest15 | usertest15@gmail.com |
| 16 | usertest16 | usertest16@gmail.com |
| 17 | usertest17 | usertest17@gmail.com |
| 18 | usertest18 | usertest18@gmail.com |
| 19 | usertest19 | usertest19@gmail.com |
| 20 | usertest20 | usertest20@gmail.com |
| 22 | usertest1 | usertest1@gmail.com |
| 23 | usertest2 | usertest2@gmail.com |
| 24 | usertest3 | usertest3@gmail.com |
| 25 | usertest4 | usertest4@gmail.com |
| 26 | usertest5 | usertest5@gmail.com |
| 27 | usertest6 | usertest6@gmail.com |
| 28 | usertest7 | usertest7@gmail.com |
| 29 | usertest8 | usertest8@gmail.com |
| 30 | usertest9 | usertest9@gmail.com |
| 31 | usertest10 | usertest10@gmail.com |
| 32 | usertest11 | usertest11@gmail.com |
| 33 | usertest12 | usertest12@gmail.com |
| 34 | usertest13 | usertest13@gmail.com |
| 35 | usertest14 | usertest14@gmail.com |
| 36 | usertest15 | usertest15@gmail.com |
| 37 | usertest16 | usertest16@gmail.com |
| 38 | usertest17 | usertest17@gmail.com |
| 39 | usertest18 | usertest18@gmail.com |
| 40 | usertest19 | usertest19@gmail.com |
| 41 | usertest20 | usertest20@gmail.com |
| 42 | usertest1 | usertest1@gmail.com |
| 43 | usertest2 | usertest2@gmail.com |
| 44 | usertest3 | usertest3@gmail.com |
| 45 | usertest4 | usertest4@gmail.com |
| 46 | usertest5 | usertest5@gmail.com |
| 47 | usertest6 | usertest6@gmail.com |
| 48 | usertest7 | usertest7@gmail.com |
| 49 | usertest8 | usertest8@gmail.com |
| 50 | usertest9 | usertest9@gmail.com |
| 51 | usertest10 | usertest10@gmail.com |
| 52 | usertest11 | usertest11@gmail.com |
| 53 | usertest12 | usertest12@gmail.com |
| 54 | usertest13 | usertest13@gmail.com |
| 55 | usertest14 | usertest14@gmail.com |
| 56 | usertest15 | usertest15@gmail.com |
| 57 | usertest16 | usertest16@gmail.com |
| 58 | usertest17 | usertest17@gmail.com |
| 59 | usertest18 | usertest18@gmail.com |
| 60 | usertest19 | usertest19@gmail.com |
| 61 | usertest20 | usertest20@gmail.com |
+----+--------------------+------------------------+
この二つのテーブルをjoinすることで、TopicAndUserのentityができる。
どういう意味かというと、このようなclassを作ることができる：
public class TopicAndUser {
private Integer topicId;
private String topicName;
private Integer categoryId;
private Integer userId;
private String userName;
//getters and setters
}
こうすることによって、mappingしたり、topicとuserの両方の情報が必要な時に一つのqueryで済むことができる。
で実際どうやるかというと、このようなSQLを使うと、
String sql = "SELECT topic.id AS topicId, topic.name AS topicName, topic.category_id AS categoryId, user.id AS userId, user.name AS userName FROM topic INNER JOIN user ON topic.user_id = user.id";
このようにテーブルをjoinしてくれる
+---------+---------------+------------+--------+------------+
| topicId | topicName | categoryId | userId | userName |
+---------+---------------+------------+--------+------------+
| 1 | topic test 1 | 5 | 9 | usertest9 |
| 2 | topic test 2 | 6 | 10 | usertest10 |
| 3 | topic test 3 | 7 | 11 | usertest11 |
| 4 | topic test 4 | 1 | 12 | usertest12 |
| 5 | topic test 5 | 2 | 13 | usertest13 |
| 6 | topic test 6 | 3 | 14 | usertest14 |
| 7 | topic test 7 | 4 | 15 | usertest15 |
| 8 | topic test 8 | 5 | 16 | usertest16 |
| 9 | topic test 9 | 6 | 17 | usertest17 |
| 10 | topic test 10 | 7 | 18 | usertest18 |
| 11 | topic test 11 | 1 | 19 | usertest19 |
| 12 | topic test 12 | 2 | 20 | usertest20 |
| 13 | topic test 13 | 3 | 1 | usertest1 |
| 14 | topic test 14 | 4 | 2 | usertest2 |
| 15 | topic test 15 | 5 | 3 | usertest3 |
| 16 | topic test 16 | 6 | 4 | usertest4 |
| 17 | topic test 17 | 7 | 5 | usertest5 |
| 18 | topic test 18 | 1 | 6 | usertest6 |
| 19 | topic test 19 | 2 | 7 | usertest7 |
| 20 | topic test 20 | 3 | 8 | usertest8 |
| 21 | topic test 21 | 4 | 9 | usertest9 |
| 22 | topic test 22 | 5 | 10 | usertest10 |
| 23 | topic test 23 | 6 | 11 | usertest11 |
| 24 | topic test 24 | 7 | 12 | usertest12 |
| 25 | topic test 25 | 1 | 13 | usertest13 |
| 26 | topic test 26 | 2 | 14 | usertest14 |
| 27 | topic test 27 | 3 | 15 | usertest15 |
| 28 | topic test 28 | 4 | 16 | usertest16 |
| 29 | topic test 29 | 5 | 17 | usertest17 |
| 30 | topic test 30 | 6 | 18 | usertest18 |
| 31 | topic test 31 | 7 | 19 | usertest19 |
| 32 | topic test 32 | 1 | 20 | usertest20 |
| 33 | topic test 33 | 2 | 1 | usertest1 |
| 34 | topic test 34 | 3 | 2 | usertest2 |
| 35 | topic test 35 | 4 | 3 | usertest3 |
| 36 | topic test 36 | 5 | 4 | usertest4 |
| 37 | topic test 37 | 6 | 5 | usertest5 |
| 38 | topic test 38 | 7 | 6 | usertest6 |
| 39 | topic test 39 | 1 | 7 | usertest7 |
| 40 | topic test 40 | 2 | 8 | usertest8 |
| 41 | topic test 1 | 5 | 9 | usertest9 |
| 42 | topic test 2 | 6 | 10 | usertest10 |
| 43 | topic test 3 | 7 | 11 | usertest11 |
| 44 | topic test 4 | 1 | 12 | usertest12 |
| 45 | topic test 5 | 2 | 13 | usertest13 |
| 46 | topic test 6 | 3 | 14 | usertest14 |
| 47 | topic test 7 | 4 | 15 | usertest15 |
| 48 | topic test 8 | 5 | 16 | usertest16 |
| 49 | topic test 9 | 6 | 17 | usertest17 |
| 50 | topic test 10 | 7 | 18 | usertest18 |
| 51 | topic test 11 | 1 | 19 | usertest19 |
| 52 | topic test 12 | 2 | 20 | usertest20 |
| 53 | topic test 13 | 3 | 1 | usertest1 |
| 54 | topic test 14 | 4 | 2 | usertest2 |
| 55 | topic test 15 | 5 | 3 | usertest3 |
| 56 | topic test 16 | 6 | 4 | usertest4 |
| 57 | topic test 17 | 7 | 5 | usertest5 |
| 58 | topic test 18 | 1 | 6 | usertest6 |
| 59 | topic test 19 | 2 | 7 | usertest7 |
| 60 | topic test 20 | 3 | 8 | usertest8 |
| 61 | topic test 21 | 4 | 9 | usertest9 |
| 62 | topic test 22 | 5 | 10 | usertest10 |
| 63 | topic test 23 | 6 | 11 | usertest11 |
| 64 | topic test 24 | 7 | 12 | usertest12 |
| 65 | topic test 25 | 1 | 13 | usertest13 |
| 66 | topic test 26 | 2 | 14 | usertest14 |
| 67 | topic test 27 | 3 | 15 | usertest15 |
| 68 | topic test 28 | 4 | 16 | usertest16 |
| 69 | topic test 29 | 5 | 17 | usertest17 |
| 70 | topic test 30 | 6 | 18 | usertest18 |
| 71 | topic test 31 | 7 | 19 | usertest19 |
| 72 | topic test 32 | 1 | 20 | usertest20 |
| 73 | topic test 33 | 2 | 1 | usertest1 |
| 74 | topic test 34 | 3 | 2 | usertest2 |
| 75 | topic test 35 | 4 | 3 | usertest3 |
| 76 | topic test 36 | 5 | 4 | usertest4 |
| 77 | topic test 37 | 6 | 5 | usertest5 |
| 78 | topic test 38 | 7 | 6 | usertest6 |
| 79 | topic test 39 | 1 | 7 | usertest7 |
| 80 | topic test 40 | 2 | 8 | usertest8 |
+---------+---------------+------------+--------+------------+
ここではたまたまtopicIdのオーダに並べられているだけで、本来はjoinした後はorderを使うのが基本：
mysql> SELECT topic.id AS topicId, topic.name AS topicName, topic.category_id AS categoryId, user.id AS userId, user.name AS userName FROM topic INNER JOIN user ON topic.user_id = user.id ORDER BY topic.id;
+---------+---------------+------------+--------+------------+
| topicId | topicName | categoryId | userId | userName |
+---------+---------------+------------+--------+------------+
| 1 | topic test 1 | 5 | 9 | usertest9 |
| 2 | topic test 2 | 6 | 10 | usertest10 |
| 3 | topic test 3 | 7 | 11 | usertest11 |
| 4 | topic test 4 | 1 | 12 | usertest12 |
| 5 | topic test 5 | 2 | 13 | usertest13 |
| 6 | topic test 6 | 3 | 14 | usertest14 |
| 7 | topic test 7 | 4 | 15 | usertest15 |
| 8 | topic test 8 | 5 | 16 | usertest16 |
| 9 | topic test 9 | 6 | 17 | usertest17 |
| 10 | topic test 10 | 7 | 18 | usertest18 |
| 11 | topic test 11 | 1 | 19 | usertest19 |
| 12 | topic test 12 | 2 | 20 | usertest20 |
| 13 | topic test 13 | 3 | 1 | usertest1 |
| 14 | topic test 14 | 4 | 2 | usertest2 |
| 15 | topic test 15 | 5 | 3 | usertest3 |
| 16 | topic test 16 | 6 | 4 | usertest4 |
| 17 | topic test 17 | 7 | 5 | usertest5 |
| 18 | topic test 18 | 1 | 6 | usertest6 |
| 19 | topic test 19 | 2 | 7 | usertest7 |
| 20 | topic test 20 | 3 | 8 | usertest8 |
| 21 | topic test 21 | 4 | 9 | usertest9 |
| 22 | topic test 22 | 5 | 10 | usertest10 |
| 23 | topic test 23 | 6 | 11 | usertest11 |
| 24 | topic test 24 | 7 | 12 | usertest12 |
| 25 | topic test 25 | 1 | 13 | usertest13 |
| 26 | topic test 26 | 2 | 14 | usertest14 |
| 27 | topic test 27 | 3 | 15 | usertest15 |
| 28 | topic test 28 | 4 | 16 | usertest16 |
| 29 | topic test 29 | 5 | 17 | usertest17 |
| 30 | topic test 30 | 6 | 18 | usertest18 |
| 31 | topic test 31 | 7 | 19 | usertest19 |
| 32 | topic test 32 | 1 | 20 | usertest20 |
| 33 | topic test 33 | 2 | 1 | usertest1 |
| 34 | topic test 34 | 3 | 2 | usertest2 |
| 35 | topic test 35 | 4 | 3 | usertest3 |
| 36 | topic test 36 | 5 | 4 | usertest4 |
| 37 | topic test 37 | 6 | 5 | usertest5 |
| 38 | topic test 38 | 7 | 6 | usertest6 |
| 39 | topic test 39 | 1 | 7 | usertest7 |
| 40 | topic test 40 | 2 | 8 | usertest8 |
| 41 | topic test 1 | 5 | 9 | usertest9 |
| 42 | topic test 2 | 6 | 10 | usertest10 |
| 43 | topic test 3 | 7 | 11 | usertest11 |
| 44 | topic test 4 | 1 | 12 | usertest12 |
| 45 | topic test 5 | 2 | 13 | usertest13 |
| 46 | topic test 6 | 3 | 14 | usertest14 |
| 47 | topic test 7 | 4 | 15 | usertest15 |
| 48 | topic test 8 | 5 | 16 | usertest16 |
| 49 | topic test 9 | 6 | 17 | usertest17 |
| 50 | topic test 10 | 7 | 18 | usertest18 |
| 51 | topic test 11 | 1 | 19 | usertest19 |
| 52 | topic test 12 | 2 | 20 | usertest20 |
| 53 | topic test 13 | 3 | 1 | usertest1 |
| 54 | topic test 14 | 4 | 2 | usertest2 |
| 55 | topic test 15 | 5 | 3 | usertest3 |
| 56 | topic test 16 | 6 | 4 | usertest4 |
| 57 | topic test 17 | 7 | 5 | usertest5 |
| 58 | topic test 18 | 1 | 6 | usertest6 |
| 59 | topic test 19 | 2 | 7 | usertest7 |
| 60 | topic test 20 | 3 | 8 | usertest8 |
| 61 | topic test 21 | 4 | 9 | usertest9 |
| 62 | topic test 22 | 5 | 10 | usertest10 |
| 63 | topic test 23 | 6 | 11 | usertest11 |
| 64 | topic test 24 | 7 | 12 | usertest12 |
| 65 | topic test 25 | 1 | 13 | usertest13 |
| 66 | topic test 26 | 2 | 14 | usertest14 |
| 67 | topic test 27 | 3 | 15 | usertest15 |
| 68 | topic test 28 | 4 | 16 | usertest16 |
| 69 | topic test 29 | 5 | 17 | usertest17 |
| 70 | topic test 30 | 6 | 18 | usertest18 |
| 71 | topic test 31 | 7 | 19 | usertest19 |
| 72 | topic test 32 | 1 | 20 | usertest20 |
| 73 | topic test 33 | 2 | 1 | usertest1 |
| 74 | topic test 34 | 3 | 2 | usertest2 |
| 75 | topic test 35 | 4 | 3 | usertest3 |
| 76 | topic test 36 | 5 | 4 | usertest4 |
| 77 | topic test 37 | 6 | 5 | usertest5 |
| 78 | topic test 38 | 7 | 6 | usertest6 |
| 79 | topic test 39 | 1 | 7 | usertest7 |
| 80 | topic test 40 | 2 | 8 | usertest8 |
+---------+---------------+------------+--------+------------+
というわけで、ここまではjoinの基本な使い方を説明した。
これは以前ここでも紹介した：
今回はこの上でちょっと他の使い方をしてみる。
今作ってるプロジェクトでは、特定のcategoryから指定した数だけのtopicを持ってくる。例えば、ページ1ではcategory_idが1の中からtopicを順番に5個displayし、ページ2ではcategory_idが1の次の5個のtopicを表示させる。
この場合に使うのが以下：
mysql> SELECT topic.id AS topicId, topic.name AS topicName, topic.category_id AS categoryId, user.id AS userId, user.name AS userName FROM topic INNER JOIN user ON topic.user_id = user.id WHERE topic.category_id = 1 AND topic.id >= 0 ORDER BY topic.id LIMIT 5;
+---------+---------------+------------+--------+------------+
| topicId | topicName | categoryId | userId | userName |
+---------+---------------+------------+--------+------------+
| 4 | topic test 4 | 1 | 12 | usertest12 |
| 11 | topic test 11 | 1 | 19 | usertest19 |
| 18 | topic test 18 | 1 | 6 | usertest6 |
| 25 | topic test 25 | 1 | 13 | usertest13 |
| 32 | topic test 32 | 1 | 20 | usertest20 |
+---------+---------------+------------+--------+------------+
そして2ページ目ではtopic.idのスタート位置を変えるだけでいい：
mysql> SELECT topic.id AS topicId, topic.name AS topicName, topic.category_id AS categoryId, user.id AS userId, user.name AS userName FROM topic INNER JOIN user ON topic.user_id = user.id WHERE topic.category_id = 1 AND topic.id >= 33 ORDER BY topic.id LIMIT 5;
+---------+---------------+------------+--------+------------+
| topicId | topicName | categoryId | userId | userName |
+---------+---------------+------------+--------+------------+
| 39 | topic test 39 | 1 | 7 | usertest7 |
| 44 | topic test 4 | 1 | 12 | usertest12 |
| 51 | topic test 11 | 1 | 19 | usertest19 |
| 58 | topic test 18 | 1 | 6 | usertest6 |
| 65 | topic test 25 | 1 | 13 | usertest13 |
+---------+---------------+------------+--------+------------+
こうすれば、一つのqueryでこのcategoryにあるtopicとuser情報を取得することができる。
それで今の問題は、新しい機能、"like機能を"を実装する際に、DBではどのユーザーがどのtopicに対してlikeしたかを保存する必要がある。
tableは以下のよう：
CREATE TABLE star_topic_user (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
topic_id INT UNSIGNED NOT NULL,
already_liked BOOLEAN DEFAULT FALSE,
CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER (id),
CONSTRAINT FOREIGN KEY (topic_id) REFERENCES topic (id),
PRIMARY KEY (id)
);
ためしにテストデータを入れてみる：
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (3, 11, true);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (3, 18, true);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (3, 31, true);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (4, 1, true);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (4, 11, true);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (4, 32, true);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (5, 4, true);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (5, 11, true);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO star_topic_user (user_id, topic_id, already_liked) VALUES (5, 18, true);
Query OK, 1 row affected (0.00 sec)
mysql> select * from star_topic_user;
+----+---------+----------+---------------+
| id | user_id | topic_id | already_liked |
+----+---------+----------+---------------+
| 1 | 3 | 11 | 1 |
| 2 | 3 | 18 | 1 |
| 3 | 3 | 31 | 1 |
| 4 | 4 | 1 | 1 |
| 5 | 4 | 11 | 1 |
| 6 | 4 | 32 | 1 |
| 7 | 5 | 4 | 1 |
| 8 | 5 | 11 | 1 |
| 9 | 5 | 18 | 1 |
+----+---------+----------+---------------+
ここではtableに入れた全てのデータはalready_likedがtrueになっている。
そしてここでさっきみたいに、一つのcategoryの下でtopicとuserとこの新しいstarテーブルをjoinさせて、もしユーザーが常にlikeをしていたらもちろんalready_likedはtrueで、もまだしていなかったらNULLを返すようにしたい。(＊注意点：topicAndUserテーブルのuser_idはこのtopicを作ったユーザーのidで、このstar_topic_userテーブルのuser_idはそのtopicをlikeしたユーザーのID、違うユーザーID)結果としては、例えばlikeしたユーザーのidが3場合、：
+---------+---------------+------------+-----------+--------+------------+--------------+
| topicId | topicName | categoryId | topicStar | userId | userName | alreadyLiked |
+---------+---------------+------------+-----------+--------+------------+--------------+
| 4 | topic test 4 | 1 | 0 | 12 | usertest12 | NULL |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 |
| 18 | topic test 18 | 1 | 0 | 6 | usertest6 | 1 |
| 25 | topic test 25 | 1 | 0 | 13 | usertest13 | NULL |
| 32 | topic test 32 | 1 | 0 | 20 | usertest20 | NULL |
+---------+---------------+------------+-----------+--------+------------+--------------+
これなら一つのqueryでcategoryの下にあるtopic、user、starを全部取得することができる。問題はどうやって？
もちろんjoinを使うので、inner joinをしてみた：
mysql> SELECT topic.id AS topicId, topic.name AS topicName, topic.category_id AS categoryId, topic.topic_star AS topicStar, user.id AS userId, user.name AS userName, star_topic_user.already_liked AS alreadyLiked FROM topic INNER JOIN user ON topic.user_id = user.id INNER JOIN star_topic_user ON topic.id = star_topic_user.topic_id WHERE topic.category_id = 1 AND topic.id >= 0 ORDER BY topic.id LIMIT 5;
+---------+---------------+------------+-----------+--------+------------+--------------+
| topicId | topicName | categoryId | topicStar | userId | userName | alreadyLiked |
+---------+---------------+------------+-----------+--------+------------+--------------+
| 4 | topic test 4 | 1 | 0 | 12 | usertest12 | 1 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 |
| 18 | topic test 18 | 1 | 0 | 6 | usertest6 | 1 |
+---------+---------------+------------+-----------+--------+------------+--------------+
これはおかしい。。。どうして全く同じrowが三つもあるんだ？
調べたら、LEFT JOINというのがあった。これだったら大丈夫だろう：
mysql> SELECT topic.id AS topicId, topic.name AS topicName, topic.category_id AS categoryId, topic.topic_star AS topicStar, user.id AS userId, user.name AS userName, star_topic_user.already_liked AS alreadyLiked FROM topic INNER JOIN user ON topic.user_id = user.id LEFT JOIN star_topic_user ON topic.id = star_topic_user.topic_id WHERE topic.category_id = 1 AND topic.id >= 0 ORDER BY topic.id LIMIT 5;
+---------+---------------+------------+-----------+--------+------------+--------------+
| topicId | topicName | categoryId | topicStar | userId | userName | alreadyLiked |
+---------+---------------+------------+-----------+--------+------------+--------------+
| 4 | topic test 4 | 1 | 0 | 12 | usertest12 | 1 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 |
| 18 | topic test 18 | 1 | 0 | 6 | usertest6 | 1 |
+---------+---------------+------------+-----------+--------+------------+--------------+
全く同じだ。。。何かがおかしい
ここでの問題点は、topicIdが11のtopicに対し、これをlikeしたuserIdは3,4,5と三つある。
実際にこれを打ってみるとわかる：
mysql> SELECT topic.id AS topicId, topic.name AS topicName, topic.category_id AS categoryId, topic.topic_star AS topicStar, user.id AS userId, user.name AS userName, star_topic_user.already_liked AS alreadyLiked, star_topic_user.user_id AS likedUserId FROM topic INNER JOIN user ON topic.user_id = user.id LEFT JOIN star_topic_user ON topic.id = star_topic_user.topic_id WHERE topic.category_id = 1 AND topic.id >= 0 ORDER BY topic.id LIMIT 5;
+---------+---------------+------------+-----------+--------+------------+--------------+-------------+
| topicId | topicName | categoryId | topicStar | userId | userName | alreadyLiked | likedUserId |
+---------+---------------+------------+-----------+--------+------------+--------------+-------------+
| 4 | topic test 4 | 1 | 0 | 12 | usertest12 | 1 | 5 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 | 3 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 | 4 |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 | 5 |
| 18 | topic test 18 | 1 | 0 | 6 | usertest6 | 1 | 3 |
+---------+---------------+------------+-----------+--------+------------+--------------+-------------+
topicIdが11の時、likedUserIdが3,4,5となっている。
これをどう回避するかというと、実はすごく簡単で、WHEREのところに条件を入れる：
mysql> SELECT topic.id AS topicId, topic.name AS topicName, topic.category_id AS categoryId, topic.topic_star AS topicStar, user.id AS userId, user.name AS userName, star_topic_user.already_liked AS alreadyLiked FROM topic INNER JOIN user ON topic.user_id = user.id LEFT JOIN star_topic_user ON (topic.id = star_topic_user.topic_id && star_topic_user.user_id = 3) WHERE topic.category_id = 1 AND topic.id >= 0 ORDER BY topic.id LIMIT 5;
+---------+---------------+------------+-----------+--------+------------+--------------+
| topicId | topicName | categoryId | topicStar | userId | userName | alreadyLiked |
+---------+---------------+------------+-----------+--------+------------+--------------+
| 4 | topic test 4 | 1 | 0 | 12 | usertest12 | NULL |
| 11 | topic test 11 | 1 | 0 | 19 | usertest19 | 1 |
| 18 | topic test 18 | 1 | 0 | 6 | usertest6 | 1 |
| 25 | topic test 25 | 1 | 0 | 13 | usertest13 | NULL |
| 32 | topic test 32 | 1 | 0 | 20 | usertest20 | NULL |
+---------+---------------+------------+-----------+--------+------------+--------------+
これで解決！
