抖音用户关注设计方案

文章目录

1. 1. 表设计
2. 2. 功能模块
   1. 2.1. 关注和取消关注
   2. 2.2. 关注、粉丝列表
   3. 2.3. 查询关注、粉丝数
3. 3. 总结

最近公司需要做一个类似抖音的用户关注、粉丝模块，当然不仅仅是查关注用户和粉丝用户，主要是查用户的关注、粉丝用户和当前登录用户的关系，这个有点绕！

表设计

这里创建了两张表一张用户关注和被关注的关系表，另外的一张为日志表，具体按你的业务逻辑设计，切忌不要将用户和被关注的用户记录数只记录一条，后面查询会非常复杂，本人亲测

CREATE TABLE `t_base_custom_follow` (
  `id` varchar(32) NOT NULL COMMENT '主键 id',
  `custom_id` varchar(32) NOT NULL COMMENT '关注用户',
  `be_custom_id` varchar(32) NOT NULL COMMENT '被关注用户',
  `create_time` datetime NOT NULL COMMENT '关注时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `status` int(1) NOT NULL COMMENT '状态 0关注 1相互关注 2拉黑',
  PRIMARY KEY (`id`),
  UNIQUE KEY `custom_id_union` (`custom_id`,`be_custom_id`) USING BTREE COMMENT '唯一索引',
  KEY `custom_id` (`custom_id`) USING BTREE COMMENT '关注用户',
  KEY `be_custom_id` (`be_custom_id`) USING BTREE COMMENT '被关注用户'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户关注关系表';

CREATE TABLE `t_base_custom_follow_log` (
  `id` varchar(32) NOT NULL COMMENT '主键 id',
  `custom_id` varchar(32) NOT NULL COMMENT '关注用户',
  `be_custom_id` varchar(32) NOT NULL COMMENT '被关注用户',
  `create_time` datetime NOT NULL COMMENT '关注时间',
  `type` int(1) NOT NULL COMMENT '类型 0关注 1取消关注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户关注关系记录表';

功能模块

关注和取消关注

public Integer updateFollow(String customId, String beCustomId, Integer type) {
        Assert.hasText(customId, "参数错误");
        Assert.hasText(beCustomId, "参数错误");
        Assert.notNull(type, "参数错误");

        Integer status = 0;

        TBaseCustomFollow model = tBaseCustomFollowMapper.getFollowInfo(customId, beCustomId);
        if (type == 0) {
            //关注
            Assert.isTrue(!customId.equalsIgnoreCase(beCustomId), "不能关注自己");

            Assert.isNull(model, "已经关注了");

            TBaseCustomFollow isFollow = tBaseCustomFollowMapper.getFollowInfo(beCustomId, customId);
            if(isFollow!=null){
                status = 1;
                isFollow.setStatus(1);
                isFollow.setUpdateTime(new Date());
                this.updateByPKNotNull(isFollow);
            }

            model = new TBaseCustomFollow();
            model.setCustomId(customId);
            model.setBeCustomId(beCustomId);
            model.setCreateTime(new Date());
            model.setId(null);
            model.setStatus(status);
            model.setUpdateTime(new Date());
            this.saveNotNull(model);


        } else {
            Assert.isTrue(!customId.equalsIgnoreCase(beCustomId), "不能取消关注自己");
            //取消关注
            Assert.notNull(model, "还未关注，不能取消");

            this.deleteByPK(model.getId());

            TBaseCustomFollow isFollow = tBaseCustomFollowMapper.getFollowInfo(beCustomId, customId);
            if(isFollow!=null){
                isFollow.setStatus(0);
                isFollow.setUpdateTime(new Date());
                this.updateByPKNotNull(isFollow);
            }
            status = 2;
        }

        TBaseCustomFollowLog logModel = new TBaseCustomFollowLog();
        logModel.setCustomId(customId);
        logModel.setBeCustomId(beCustomId);
        logModel.setCreateTime(new Date());
        logModel.setType(type);
        itBaseCustomFollowLogService.saveNotNull(logModel);

        return status;
    }

关注、粉丝列表

public PageInfo getFollowList(TBaseCustomFollow model) {
        Assert.hasText(model.getCustomId(), "参数错误");
        Assert.notNull(model.getType(), "参数错误");
        Assert.hasText(model.getUserId(), "请登录");
        Assert.hasText(model.getCustomId(), "用户id不能为空");

        doPage(model.getPageNo(), model.getPageSize());
        return new PageInfo(tBaseCustomFollowMapper.getFollowList(model));
    }

mybatis sql 语句

<select id="getFollowList" resultType="com.kanglefucn.business.admin.dao.model.TBaseCustomFollow">
        <if test="type==0">
            select
            A.id,
            A.create_time createTime,
            A.update_time updateTime,
            A.be_custom_id customId,
            <if test="userId==customId">
                A.`status` 'userStatus',
            </if>
            <if test="userId!=customId">
                IF(tt.status is null,2,tt.status) 'userStatus',
            </if>
            B.nickname userName,
            IFNULL(B.oss_pictureurl, C.headimgurl) headerUrl
            from t_base_custom_follow A
            left join t_base_custom B on A.be_custom_id = B.id
            left join t_base_custom_wechat C ON B.id = C.accountid
            <if test="userId!=customId">
                left join (
                    select
                    B.status,A.be_custom_id
                    from t_base_custom_follow A left join
                    t_base_custom_follow B on (A.be_custom_id = B.custom_id and B.be_custom_id = #{userId})
                    where A.custom_id = #{customId}
                    group by A.custom_id,A.be_custom_id
                ) tt on A.be_custom_id = tt.be_custom_id
            </if>
            where A.custom_id = #{customId}
            order by A.create_time desc
        </if>
        <if test="type==1">
            select
            A.id,
            A.create_time createTime,
            A.update_time updateTime,
            A.custom_id customId,
            IF(tt.status is null,2,tt.status) 'userStatus',
            B.nickname userName,
            IFNULL(B.oss_pictureurl, C.headimgurl) headerUrl
            from t_base_custom_follow A
            left join t_base_custom B on A.custom_id = B.id
            left join t_base_custom_wechat C ON B.id = C.accountid
            <if test="userId!=customId">
                left join (
                    SELECT
                        B.STATUS,A.custom_id
                    FROM
                    t_base_custom_follow A left join
                    t_base_custom_follow B on (A.custom_id = B.be_custom_id and B.custom_id = #{userId})
                    WHERE
                    A.be_custom_id = #{customId}
                    GROUP BY
                    A.custom_id,
                    A.be_custom_id
                ) tt on A.custom_id = tt.custom_id
            </if>
            <if test="userId==customId">
                left join (select status,be_custom_id from t_base_custom_follow where custom_id = #{customId}
                group by custom_id,be_custom_id ) tt on A.custom_id = tt.be_custom_id
            </if>
            where A.be_custom_id = #{customId}
            order by A.create_time desc
        </if>
    </select>

查询关注、粉丝数

mybatis sql 语句

<select id="getFollowCount" resultType="map">
        select sum(followCount) followCount,sum(t.beFollowCount) beFollowCount from(
        select
        count(1) followCount,0 beFollowCount
        from t_base_custom_follow
        where custom_id = #{customId}
        union all
        select
        0 followCount, count(1) beFollowCount
        from t_base_custom_follow
        where be_custom_id = #{customId}
        ) t
    </select>

总结

现阶段先这样使用着先吧，设计是开发效率最大的提升，设计的好，你写代码事半功倍，设计不好，代码写的复杂，效率性能也差，如果你有更好的方案，请留言，谢谢指教