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の基本な使い方を説明した。

これは以前ここでも紹介した:

sallyhasnonose.hatenablog.com

今回はこの上でちょっと他の使い方をしてみる。

今作ってるプロジェクトでは、特定の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 |

+---------+---------------+------------+-----------+--------+------------+--------------+

これで解決!

 

 

 

 

 

 

 

 

 

 

 

 

Interceptorでユーザーログインやセッションの管理をする

今日は日本語で、Interceptorを使ってどうやってログインやセッションを管理するかを記録。

問題点:

1. Interceptorを使う際のinjectionエラー

2. そのinterceptorのTestを書くときのエラー

3. 多数のinterceptorを使う際の順番問題

 

まずは全体的のclassを紹介:

SessionInterceptor

public class SessionInterceptor extends HandlerInterceptorAdapter {
@Autowired
SessionHandler sessionHandler;
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
HttpSession session = request.getSession(
true);
sessionHandler.createSession(session);
return true;
}
}

 

SessionInterceptorConfig

@Configuration
public class SessionInterceptorConfig extends WebMvcConfigurerAdapter {
@Override
public void addInterceptors(InterceptorRegistry registry) {
registry.addInterceptor(sessionInterceptor()).addPathPatterns("/**");
}
}

 

わかりやすいように書いてるので、コードの中身はほぼ抜いた。

簡単に説明すると、これはInterceptorを使用する際の基本で、InterceptorはConfigファイルでこのように設定してあげないと使えない。

ここで一つ目のエラーが出る。

問題点1

エラーは何処かと言うと、すごく面白くて、

sessionHandler.createSession(session);

にヌルポエラーが出る。

What the hell???

って思う。

解決策:どうしてこのようなエラーが出るかというと、@AutowiredをされたsessionHandlerを使うためにはInterceptorをそもそもInjectしないとダメ、これ重要。

なので、上のSessionInterceptorConfigをこうする↓

@Configuration
@ComponentScan
public class SessionInterceptorConfig extends WebMvcConfigurerAdapter {
@Bean
public SessionInterceptor sessionInterceptor() {
return new SessionInterceptor();
}
@Override
public void addInterceptors(InterceptorRegistry registry) {
registry.addInterceptor(sessionInterceptor())
.addPathPatterns("/**");
}
}

ちなみに:これはできない↓

SessionInterceptorConfig

@Configuration
@ComponentScan
public class SessionInterceptorConfig extends WebMvcConfigurerAdapter {

@Override
public void addInterceptors(InterceptorRegistry registry) {
registry.addInterceptor(new SessionInterceptor())
.addPathPatterns("/**");
}

}

SessionInterceptor

@Component
public class SessionInterceptor extends HandlerInterceptorAdapter {
@Autowired
SessionHandler sessionHandler;
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
HttpSession session = request.getSession(false);
sessionHandler.createSession(session);
return true;
}
}

@Componentと@Beanを根本的に違うみたい。

基本Configでinjectする際は@Beanを使おう。

ということで、このSessionInterceptorの実装が終わったのでこれのunitテストを書こう!

これがTest、名前はSessionInterceptorTest

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = Application.class)
public class SessionInterceptorTest {

private MockHttpServletRequest mockHttpServletRequest;
private MockHttpServletResponse mockHttpServletResponse;
private Object handler;
private SessionInterceptor sessionInterceptor;

@Before
public void setup() {
mockHttpServletRequest = new MockHttpServletRequest();
mockHttpServletResponse = new MockHttpServletResponse();
handler = new Object();
sessionInterceptor = new SessionInterceptor();
}

@Test
public void preHandleTest() throws Exception {
//make sure this mockRequest doesn't have session yet
HttpSession httpSession = mockHttpServletRequest.getSession(false);
assertNull(httpSession);

//test preHandle method
sessionInterceptor.preHandle(mockHttpServletRequest, mockHttpServletResponse, handler);
}
}

で、これを実行するとやはりヌルポが出る、しかもこの

sessionInterceptor.preHandle(mockHttpServletRequest, mockHttpServletResponse, handler);

のところで。

基本、上の実装をしたので少ない経験から言うと、これは多分injectionの問題が大きい。なるほど、この行のsessionInterceptorは@Autowiredじゃない!なので以下が正しいコード↓

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = Application.class)
public class SessionInterceptorTest {

private MockHttpServletRequest mockHttpServletRequest;
private MockHttpServletResponse mockHttpServletResponse;
private Object handler;

@Autowired
SessionInterceptor sessionInterceptor;

  @Before
public void setup() {
mockHttpServletRequest = new MockHttpServletRequest();
mockHttpServletResponse = new MockHttpServletResponse();
handler = new Object();
}

@Test
public void preHandleTest() throws Exception {
//make sure this mockRequest doesn't have session yet
HttpSession httpSession = mockHttpServletRequest.getSession(false);
assertNull(httpSession);

//test preHandle method
sessionInterceptor.preHandle(mockHttpServletRequest, mockHttpServletResponse, handler);
}
}

これで正常に動いた。

見やすくしてるためコードの中身は抜いてるが、基本このSessionInterceptorでは初回アクセスのsessionをDB入れるようにしている。そして二回目からのアクセスはすでにセッションが作られているのでここはスキップされる。

 

これであと一つ、ユーザーがログインしているかのinterceptorを作りたい。

目的としては、もしユーザーがログインしている場合はそのままcontrollerにアクセスするようにして、してない場合はinterceptorから直接loginのcontrollerにjumpさせる。こうすることで全てのControllerクラスはログインの判断をする必要がなくなり、ユーザーがすでに正常にログインされているとの状態での処理だけが必要となる。

PS:関係ない話だが、ここはひとつのinterceptorにしても良かった。でも今回は出来るだけ分けてやりたいので二つ作った。

 

なので、このLoginInterceptorを作ってみた。

public class LoginInterceptor extends HandlerInterceptorAdapter {
@Value("${endpoint.URL}")
private String hostURL;

@Autowired
SessionHandler sessionHandler;

@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {

HttpSession session = request.getSession(false);

//when to check if user is login, check the DB if session attribute exists by session name
if (sessionHandler.fetchUserLoginSessionAttribute(session.getId()) == null) {
response.sendRedirect(hostURL + "/userLogin");
return false;
}
return true;
}
}

しかしここで第三の問題

このLoginInterceptorは

HttpSession session = request.getSession(false);

の次に直接sessionがnullじゃない前提で行動しているので、SessionInterceptorは必ずこのLoginInterceptorの前に実行されなければならない。

そこで調べたところ、以下の二つを参考に@Orderを使うことにした。

Spring MVC(+Spring Boot)上でのリクエスト共通処理の実装方法を理解する - Qiita

How to define the execution order of interceptor in Spring Boot application? - Stack Overflow

そしてこれがLoginSessionConfig

@ComponentScan
@Configuration
@Order(Ordered.LOWEST_PRECEDENCE)
public class LoginInterceptorConfig extends WebMvcConfigurerAdapter {

@Bean
public LoginInterceptor loginInterceptor() {
return new LoginInterceptor();
}

@Override
public void addInterceptors(InterceptorRegistry registry) {
registry.addInterceptor(loginInterceptor())
.addPathPatterns("/login/**");
}
}

もちろんSessionInterceptorCOnfigにも@Orderをつけた

@Order(Ordered.HIGHEST_PRECEDENCE)
public class SessionInterceptorConfig extends WebMvcConfigurerAdapter {

解決!

 

 

 

log4J2の基本

今回は日本語でなるべく簡単にlog4j2について話したいと思う。

まずはじめに、loggerと聞くとまぁだいたいログを作るもんだなーって感じがある。

例えばコンパイルの時とかアプリがrunnigしている時。

最初はこのloggerというものに自分の想像があり、それは多分どこかでloggerのインスタンスを作って、例えば"ここからここ"までな感じでコードの中でstartとstopを指定してあげればできるはずだと思った。

 

そして結果から言うとそんなに簡単ではない。

これをわかりやすく説明するため、以下のリンク先の内容を簡単に日本語にしてみた。(original source is here(Introducing Log4J 2 - Enterprise Class Logging - Spring Framework Guru)簡単に言うと最初に把握しておくポイントは二つある:

  • まず、logにはエラーによって異なるレベルがあるhttps://i2.wp.com/springframework.guru/wp-content/uploads/2016/02/Log_Levels.png?ssl=1

まずこれを覚えたほうがいい。

例えば: "While developing in your local machine, it is common to set the log level to DEBUG. This will give you detailed log messages for your development use. While on production, its typical set the log level to ERROR. This is to avoid filling your logs with excessive debug information. And while logging is very efficient, there is still a cost. In your application, once you have retrieved a logger, you call one of the printing methods debug()info()warn()error()fatal(), and log() on the logger to log messages."

  • 次に、Appenderとは何か?

Once you capture logging information through a logger, you need to send it to an output destination. The output destination is called an appender, and it is attached to the logger.

日本語で言うと、どういう方式、あるいはどこでそのlogの結果を見たいか。例えば、コンソールで?ファイル内で?何かのGUIで?みたいな

コードを書くについて思ったこと

初めての日本語のエントリー。

 

今週の土曜日はプログラミング勉強会をした。

内容はPHPをゼロから学ぶと同時にプログラミングを理解して行く会、みたいな。

ここで自分が感じたことを何点か書いておく:

  1. CでもJAVAでもPHPでも、プログラミング言語というものは単純にプログラムに命令を与えてそれを実行させる一つの方法。
  2. しかし、それができるからと行って機械(PC)と対話して何かをやってくるというわけではない。これはまるで計算が人なん億倍できて、しかし感情がなく物事を覚えられない子供と対話するような感じだと思う。
  3. だって、実際に命令すると行っても、何をするかはすべてひとがコード書いてそれを設計したりしておこなっている。
  4. なので、コードを書くというのは実は簡単で、難しいのはその結果に至るまでの設計になる。
  5. 例えば、1~100の素数を探すみたいないかにも練習問題で出てくるような質問には回答がいくつもある、あるいは方法がいくつもある。(具体的な問題の内容にもよるが)。
  6. だから、"コードを書く"より"この問題に関しての思考回路をどう整えるか"が重要になってくると思う。

The importance of ORDER BY in SQL query

This is what just happend yesterday during my work time.

I have two tables in my database, one is topic, another is user.

What I want to do is, I want to fetch a topic list with the user who created that topic.

 

Here is my original idea:

step 1, fetch the topic list

step 2, according to the topic list, fetch user object for each topic element

 

Then I was told this is N+1 query...

Okey, use table join to join the table and then fetch the TopicAndUser object.

 

I did,

SELECT * FROM topic INNER JOIN user ON topic.user_id = user.id;

ok, this works.

Next step, I wan to fetch the same object above, but with the specific category number. So I did,

SELECT * FROM topic INNER JOIN user ON topic.user_id = user.id WHERE category.id = 1;

ok, this also works.

So the last step, let's fetch the same object above with the specific category number and also I want specify the range of topic id, so I did,

SELECT * FROM topic INNER JOIN user ON topic.user_id = user.id WHERE category.id = 1  AND topic.id  > 5;

wait, something is wrong here.

It looks like the order of this result is ordered by user id.

But what I want is to order by topic id...

 

Okey, the thing is, when we join two tables together, we don't know what the order will be. Even when I succeed at the first step, I was just lucky.

So here is the importance of ORDER BY.

What we should do is:

SELECT * FROM topic INNER JOIN user ON topic.user_id = user.id WHERE category.id = 1  AND topic.id  > 5 ORDER BY topic.id;

 

ORDER BY, a good way to solve unexpected "wrong order" error.

How to solve file conflict while using git&github

Today, I will use images to explain how to slove conflict while using github.

There are many git or github related tutorials, but I could't find the one that suitable for me, which means none of them can make me understand.

So, I asked my colleague how I can solve this problem step by step, with paper and pen. Now, let's assume you have done half way of your work and here is the master branch on remote and local.

f:id:SallyHasNoNose:20161113181306p:plain

The two branches are synchronised. And now, you are gonna implement a new feature, let's call feature B which leads you to create a new branch called B. You start to change/add/delete something then you do "git add" and "git commit".

f:id:SallyHasNoNose:20161113181520p:plain

Now, as usual you do "git push" the branch B to remote github.

f:id:SallyHasNoNose:20161113181944p:plain

Github will record your local commits. You create a pull request and now are ready to ask someone to check your code. However, in our case, that somebody who is gonna check your code is out of the town and you can't wait him because you have to catch up with the deadline. So on your local, you continue working on another new feature, let's say feature C.

f:id:SallyHasNoNose:20161113182301p:plain

And after some changes and commits you decided to push this branch to github.

f:id:SallyHasNoNose:20161113182406p:plain

Ok, everything looks good. And you feel happy because that guy who is gonna check your code just came back and he commented "LGTM" then he merged your pull request.

f:id:SallyHasNoNose:20161113182557p:plain

He then checked your feature C implementation nad he is about to merge it into master branch. But, here, the problem is you changed a same file in branch B and branch C so the conflict occurs!

f:id:SallyHasNoNose:20161113194731p:plain

This is because B and C changed same file which is from A. Now branch C can not merged onto master branch. Next I will explain how to solve this problem in a better way without "git rebase"

First, on your local checkout to the master branch. And pull the latest master branch from remote.

f:id:SallyHasNoNose:20161113195301p:plain

Then, you checkout to branch C on your local, merge the master branch.

f:id:SallyHasNoNose:20161113195422p:plain

Right now, since your branch C and your master branch has the same file changed on both side, there should be a conflict just like on the remote branch. You can check the conflict part from that file. 

f:id:SallyHasNoNose:20161113195623p:plain

You found the conflict part, you fix the code to the newset version, then you push(in this case branch C) to the remote github, then now you can merge the branch C to master branch. Problem solved!

f:id:SallyHasNoNose:20161113195754p:plain