MySQLでいいね！（LIKE）機能のDB設計をしてみた！

いいねってなに？

FACEBOOKですと、いいね、 Twitterだと♥のあれです。エンゲージメント率を高めるばかりではなく、記事の拡散にも効果を発揮しますよね。また、サービス運営者視点で話すと、話題が見つけやすくなりますよね。 ってなわけで、今度はいいね。のテーブル設計をどうするか？というのは試してみたいと思います

テーブル設計

構造はいたってシンプルです。

CREATE TABLE `likes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `article_id` bigint(20) NOT NULL  DEFAULT '0',
  `user_id` bigint(20) NOT NULL DEFAULT  '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix01_likes` (`user_id`,`article_id`),
  KEY `ix02_likes` (`user_id`),
  KEY `ix03_likes` (`article_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

いつ？ 何に？ 誰が？　

いいねしたのかを表現しただけです。あと、いいねしか入っていないので、いいねフラグとかもいらないかな？

SQL サンプル

簡単な例になりますが、ちょっと例外で、いいねを外すというSQLのサンプルも書いてみたいと思います

いいねする

-- user_id:1 さんが 記事id(article_id):1をいいねした
INSERT INTO likes
  (article_id,user_id) VALUES (1,1) ;
-- user_id:2 さんが 記事id(article_id):1をいいねした
INSERT INTO likes
  (article_id,user_id) VALUES (1,2) ;
-- user_id:3 さんが 記事id(article_id):1をいいねした
INSERT INTO likes
  (article_id,user_id) VALUES (1,3) ;
-- user_id:2 さんが 記事id(article_id):2をいいねした
INSERT INTO likes
  (article_id,user_id) VALUES (2,2) ;

• 登録結果

テストデータのインサート結果は以下のようになりました。まぁ。。とくに説明するまでもないですよね。。

MariaDB [social]> SELECT * FROM likes ;                                    
+----+---------------------+---------------------+------------+---------+
| id | created_at          | updated_at          | article_id | user_id |
+----+---------------------+---------------------+------------+---------+
|  1 | 2016-05-17 12:35:43 | 2016-05-17 12:35:43 |          1 |       1 |
|  2 | 2016-05-17 12:35:44 | 2016-05-17 12:35:44 |          1 |       2 |
|  3 | 2016-05-17 12:35:44 | 2016-05-17 12:35:44 |          1 |       3 |
|  5 | 2016-05-17 12:36:16 | 2016-05-17 12:36:16 |          2 |       2 |
+----+---------------------+---------------------+------------+---------+

記事に対する、いいね数の集計をとる

集計も簡単です。

mysql>
SELECT
  lk.article_id,
  COUNT(lk.id) AS cnt
FROM
  likes lk
GROUP BY
  lk.article_id
;

• 集計結果 記事id:1には３名がいいね、記事id:2には１名がいいねされています。ちょっと簡単すぎるかも。

+------------+-----+
| article_id | cnt |
+------------+-----+
|          1 |   3 |
|          2 |   1 |
+------------+-----+

いいねを解除する

では、いいねを解除する場合はどうでしょうか？、ここで、周りの人がよくやるのが、削除フラグを付けるとかなのですが、ここは、ばっさり削除しましょう！！使われないデータをテーブルに保有していたところでなんの意味もありません。逆に、テーブルの分母（レコード数）が増えていくので、パフォーマンスがどんどん低下していきます、消しましょう！！！

いいねを解除する

-- user_id:1 さんが 記事id(article_id):1をいいねを解除する
DELETE FROM likes
WHERE
  article_id = 1 AND
  user_id = 1
;

記事に対する、いいね数の集計をとる

先ほど一件、いいねを解除しているので、当然 記事id:1の件数が減っているかと思います

mysql>
SELECT
  lk.article_id,
  COUNT(lk.id) AS cnt
FROM
  likes lk
GROUP BY
  lk.article_id
;

• 集計結果 記事id:1には２名がいいね、記事id:2には１名がいいねされています。

+------------+-----+
| article_id | cnt |
+------------+-----+
|          1 |   2 |
|          2 |   1 |
+------------+-----+

人気の記事を優先的に表示する

すこし、記事のデータのいいねを増やしてみます。

INSERT INTO likes
  (article_id,user_id) VALUES (3,3),(4,3),(5,3),(6,3),(7,5),(8,5),(7,7),(8,7),(9,2) ;

• このようなデータになりました

MariaDB [social]> SELECT * FROM likes ;
+----+---------------------+---------------------+------------+---------+
| id | created_at          | updated_at          | article_id | user_id |
+----+---------------------+---------------------+------------+---------+
|  2 | 2016-05-17 12:35:44 | 2016-05-17 12:35:44 |          1 |       2 |
|  3 | 2016-05-17 12:35:44 | 2016-05-17 12:35:44 |          1 |       3 |
|  5 | 2016-05-17 12:36:16 | 2016-05-17 12:36:16 |          2 |       2 |
| 15 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          3 |       3 |
| 16 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          4 |       3 |
| 17 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          5 |       3 |
| 18 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          6 |       3 |
| 19 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          7 |       5 |
| 20 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          8 |       5 |
| 21 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          7 |       7 |
| 22 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          8 |       7 |
| 23 | 2016-05-17 18:41:06 | 2016-05-17 18:41:06 |          9 |       2 |
+----+---------------------+---------------------+------------+---------+

• もっともいいねされている記事の順に表示します

同いいね数の記事は、最新のものを優先に表示するようにします

SELECT
   lk.article_id,
   count(lk.id) AS like_count
FROM
  likes lk
GROUP BY 
  lk.article_id
ORDER BY
  like_count DESC, article_id DESC
;

• 集計結果

+------------+------------+
| article_id | like_count |
+------------+------------+
|          8 |          2 |
|          7 |          2 |
|          1 |          2 |
|          9 |          1 |
|          6 |          1 |
|          5 |          1 |
|          4 |          1 |
|          3 |          1 |
|          2 |          1 |
+------------+------------+

いかがだったでしょうか？ちょっと簡単すぎる例ではありましたが、実際には、カテゴリ別の記事のいいね数とか、ある特定の記事idにいいね人を出力するとか、さまざまなシチュエーションが考えられます。また、前述でいいねを解除した人のデータは消すとは書きましたが、いいねを解除したら、解除したという履歴がほしいというケースもあったりします。（私でしたら、解除は、別のテーブルに退避するかもしれません）　今回は基礎的ないいねの流れしか書きませんでしたが、そのあたりは臨機応変にSQLを組みなおすのがCOOLかな？と思います。