Database JUNKY

MySQL,MariaDBを中心としたブログです

トップ > MariaDB > フォロー/フォロワーの構築 ～ソーシャル系のDBを構築する

フォロー/フォロワーの構築 ～ソーシャル系のDBを構築する

MariaDB MySQL SQL facebook twitter follow follower フレンド 友達

フォロー/フォロワーのテーブル設計

f:id:hit10231023:20180309104332j:plain

f:id:hit10231023:20160517190757j:plain

ソーシャルネットワーク系のデータベースでフォロー/フォロワーってどんなしくみで設計しているの？なんていうのを中心にまとめてみました

NURO光キャンペーン ネットのみでも必ず3万円が貰える！

有効な暇つぶしの方法とは。。

フォロー・フォロワーを考える

twitter等というか、最近どこでもあるけど、フォロー・フォロワーのテーブルについて考えたいと思います まず、フォローって何なの？というところですが、以下を引用しますね。

フォローとは？

フォローとは、「追う」「従う」「関心をもつ」「傾聴する」などのような意味を持つ英語であるが、 IT用語としては、Twitterをはじめとするソーシャルサービスにおいて、他のユーザーの動向を知ることができるよう つながりを得ることである。特にTwitterにおいては、対象ユーザーのツイートを受信するように登録すること、および、 そうしてツイートを受信している状態のことである。

とのことです。（私も初めて知りました）

フォロー・フォロワーの位置付け

データベースにおけるフォローとフォロワーの位置付けですが

フォロー (follow)

自分が相手をフォローする

フォロワー (follower)

相手が自分をフォローされる

といった関係値になると思います

ツイッターのF太さん　?4万フォロワーを集める人生のつぶやきメモ? (impress QuickBooks)

フォロー・フォロワーのテーブル定義

たとえば、フォローとフォロワーがあるからって、フォローとフォロワーの２テーブルを設計必要はありません。テーブルはfollowsという名前の1テーブルだけでいいです。 followsというテーブル定義は以下の通りとなります

 CREATE TABLE `follows` (
  `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 ,
  `user_id` bigint(20) unsigned NOT NULL,
  `follow_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix01_follows` (`user_id`,`follow_id`),
  KEY `ix02_follows` (`user_id`),
  KEY `ix03_follows` (`follow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
  • user_id ユーザーID（内部ID）です
  • follow_id これは、フォローされる人のユーザーIDです

mineo エントリーパッケージ au/ドコモ対応SIMカード データ通信/音声通話 (ナノ/マイクロ/標準SIM/VoLTE)

SQL サンプル

あ、一応ユーザーマスタなるものがあるという前提でフォローとフォロワーを考えたいと思います

フォローする

フォローのテストデータをインサートしていきます

-- user_id:1 さんが user_id:2さんをフォローする
INSERT INTO follows
  (user_id,follow_id) VALUES (1,2) ;

-- user_id:1 さんが user_id:3さんをフォローする
INSERT INTO follows
  (user_id,follow_id) VALUES (1,3) ;

-- user_id:1 さんが user_id:10さんをフォローする
INSERT INTO follows
  (user_id,follow_id) VALUES (1,10) ;

-- user_id:10 さんが user_id:1さんをフォローする
INSERT INTO follows
  (user_id,follow_id) VALUES (10,1) ;

-- user_id:3 さんが user_id:1さんをフォローする
INSERT INTO follows
  (user_id,follow_id) VALUES (3,1) ;

あなたを女性目線でスタイリング！ 新感覚ファッションサービスbemool

  • 登録結果 テストデータのインサート結果は以下のようになりました
mysql>
SELECT * FROM follows ;
+----+---------------------+---------------------+---------+-----------+
| id | created_at          | updated_at          | user_id | follow_id |
+----+---------------------+---------------------+---------+-----------+
|  1 | 2016-05-02 19:36:50 | 2016-05-02 19:36:50 |       1 |         2 |
|  2 | 2016-05-02 19:36:50 | 2016-05-02 19:36:50 |       1 |         3 |
|  3 | 2016-05-02 19:36:50 | 2016-05-02 19:36:50 |       1 |        10 |
|  4 | 2016-05-02 19:36:50 | 2016-05-02 19:36:50 |      10 |         1 |
|  5 | 2016-05-02 19:36:51 | 2016-05-02 19:36:51 |       3 |         1 |
+----+---------------------+---------------------+---------+-----------+

それぞれのフォロー/フォロワー数を出す

ちょっと複雑なSQLになってきましたが、読み解くと簡単です

mysql>
SELECT
  X.user_id,
  SUM(CASE X.kbn WHEN 'follow' THEN X.cnt ELSE 0 END) AS follow_count,
  SUM(CASE X.kbn WHEN 'follower' THEN X.cnt ELSE 0 END) AS follower_count
FROM
(
  SELECT
    'follow' AS kbn,
    fl.user_id AS user_id,
    count(fl.follow_id) AS cnt
  FROM
    follows fl
  GROUP BY
    fl.user_id
UNION ALL
  SELECT
    'follower' AS kbn,
    flw.follow_id AS user_id,
    count(flw.user_id) AS cnt
  FROM
    follows flw
  GROUP BY
    flw.follow_id
) X
GROUP BY
  X.user_id
;
  • 集計結果

一例ですが、1さんは、3人をフォローしていて、2人からフォローされております。

+---------+--------------+----------------+
| user_id | follow_count | follower_count |
+---------+--------------+----------------+
|       1 |            3 |              2 |
|       2 |            0 |              1 |
|       3 |            1 |              1 |
|      10 |            1 |              1 |
+---------+--------------+----------------+

相互フォロー

では、お互いがフォローし合っている人がいるかをSQLで表現してみます

いわゆる、多対多のSQLになるのかな？

SELECT
  s1.user_id,
  s1.follow_id
FROM
(
SELECT
  fl.user_id AS user_id,
  fl.follow_id AS follow_id
FROM
  follows fl
) s1
INNER JOIN
(
SELECT
  flw.follow_id AS user_id,
  flw.user_id AS follow_id
FROM
  follows flw
) s2
ON
  s1.user_id = s2.user_id AND
  s1.follow_id = s2.follow_id
;
  • 結果は以下の通り
+---------+-----------+
| user_id | follow_id |
+---------+-----------+
|       1 |         3 |
|       1 |        10 |
|       3 |         1 |
|      10 |         1 |
+---------+-----------+

これが、猛烈に難しかったです。 ちょっと都合のいい解釈になりますが、user_id さんからみた、follow_id が相互フォローになってますよという表現になります、実際のケースで、ユーザー関係なく、相互フォロー一覧なんて出すことはないと思いますので、これでいいんじゃないかな？と思います

自分だけのPC・スマホケースのデザインができるカスタマイズショップ！

user_id=1さんが相互フォローしているユーザーさんを出す

  • こんなSQLになるのかな？と
SELECT
 *
FROM
(
  SELECT
    s1.user_id,
    s1.follow_id
  FROM
  (
  SELECT
    fl.user_id AS user_id,
    fl.follow_id AS follow_id
  FROM
    follows fl
  ) s1
INNER JOIN
  (
  SELECT
    flw.follow_id AS user_id,
    flw.user_id AS follow_id
  FROM
    follows flw
  ) s2
ON
  s1.user_id = s2.user_id AND
  s1.follow_id = s2.follow_id
) follow_list
WHERE
  Follow_list.user_id = 1
;
  • 結果は以下の通りです
+---------+-----------+
| user_id | follow_id |
+---------+-----------+
|       1 |         3 |
|       1 |        10 |
+---------+-----------+

1さん視点でみた場合に、3,10が相互フォローしていることになりますね。

フォローとフォロワーの紐づきをうまく操作できるようになれば、お勧めのユーザーとか、友達かも？とかそんな機能を実装するのもきっと余裕になります