用户关注表设计(查询包含是否互关等状态)

最近在写用户关注这方面的东西，本想偷懒，结果网上也没找到一个能在查出来粉丝列表的同时告知我是否与该用户为互相关注，又碍于项目没法用图数据库，只能自己写一写了

表结构

首先uid和followed_uid为本表的唯一主键， id为自增字段，用来统计，其他字段多为辅助字段。

说明一下：status为1代表关注 为0代表未关注，这个的意义是在大部分情况下修改的效率是大于删除的。（大部分情况下修改不是单纯的删除再插入）

# postgresql

create table user_follows
(
    updated_at   timestamp with time zone,
    created_at   timestamp with time zone,
    status       integer default 1,
    followed_uid serial,
    uid          serial,
    id           serial,
    constraint user_follows_pkey
        primary key (uid, followed_uid)
);

# mysql

create table user_follows
(
    updated_at   timestamp     null,
    created_at   timestamp     null,
    status       int default 1 not null,
    followed_uid int           not null,
    uid          int           not null,
    primary key (uid, followed_uid)
);

表如下

关注与取关

这个sql的关键在于，存在就不再插入仅作修改

# postgresql
INSERT INTO user_follows(uid, followed_uid, status, created_at, updated_at)
values (1301, 1200, 1, now(), now())
ON CONFLICT (uid, followed_uid) DO UPDATE SET status = 1,  updated_at = now();

# mysql
INSERT INTO test.user_follows(uid, followed_uid, status, created_at, updated_at) 
values (1301, 1200, 0, now(), now()) 
ON duplicate KEY UPDATE status=1,  updated_at=NOW();

我的关注列表

注意3970代表我的uid

SELECT a.followed_uid  as uid,
       CASE WHEN a.followed_uid = b.uid THEN true ELSE false END as status
FROM (SELECT uid, followed_uid FROM user_follows WHERE uid = 3970) as a
         LEFT JOIN user_follows b ON b.followed_uid = a.uid AND b.uid = a.followed_uid;

结果：
uid代表关注的id， status为true代表互相关注， false代表用户未关注我

我的粉丝列表

注意3970代表我的uid

SELECT a.uid, CASE WHEN a.followed_uid = b.uid THEN true ELSE false END as status
FROM (SELECT uid, followed_uid FROM user_follows WHERE followed_uid = 3970 and status = 1) as a
         LEFT JOIN user_follows b ON b.uid = a.followed_uid AND b.followed_uid = a.uid AND b.status = 1
         LEFT JOIN users ON users.id = a.uid;

结果：
uid代表粉丝id， status为true代表互相关注， false代表我未关注此用户

对于查看别人的粉丝/关注列表,我关心的是， 别人的关注者/粉丝，我有没有关注他的这个关注者/粉丝, 如下图

别人的关注列表

注意1199代表自己，3970代表我们查看的用户

SELECT a.followed_uid as uid,
       CASE WHEN a.followed_uid = b.followed_uid THEN true ELSE false END as status
FROM (SELECT followed_uid, uid FROM user_follows WHERE uid = 3970 AND status = 1) as a
         LEFT JOIN user_follows as b ON b.uid = 1199 AND a.followed_uid = b.followed_uid AND status = 1
         LEFT JOIN users ON users.id = a.followed_uid;

结果：
uid代表别人的关注的用户， status为true代表我关注了这个用户， status为false代表我没有关注

别人的粉丝列表

注意1199代表自己，3970代表我们查看的用户

SELECT a.uid,
       CASE WHEN a.uid = b.followed_uid THEN true ELSE false END as status
FROM (SELECT uid FROM user_follows WHERE followed_uid = 3970 AND status = 1) a
         LEFT JOIN user_follows b ON b.uid = 1199 AND a.uid = b.followed_uid AND status = 1
         LEFT JOIN users ON users.id = a.uid;

结果：
uid代表别人的粉丝， status为true代表我关注了这个粉丝， status为false代表我没有关注