仿知乎评论业务设计(Redis点赞功能设计)

仿知乎评论业务设计(Redis点赞功能设计)

前言

明年就要秋招了,想着做个项目丰富下自己的简历。项目中有个评论业务,一开始在百度上搜相关方案,要么内容不全复制粘贴,要么设计有硬伤无法采用。因为本人是重度知乎使用者,觉得知乎的评论设计还算可以,因此下定决心自己从零开始设计一款评论系统。笔者能力有限,有什么错误还望各位大神在评论区指出。

业务要求

先看知乎是如何设计的。

对于文章详情页,显示两条热点评论、发起评论用户名、id、时间、点赞数、评论内容。

点击查看全部评论后,显示评论以及评论的子评论。子评论只显示两条。

点击查看全部回复后,显示该评论下的所有回复,按回复时间顺序排列。

业务总结

  1. 文章只显示两条评论,点击查看全部评论进入评论详情页

  2. 评论要显示的内容:用户头像、用户名称,评论时间,评论内容。子评论(如果有)只显示两条,不显示评论的回复。

  3. 文章的评论按照点赞数排序。

  4. 子评论按照评论时间顺序排列。对于子评论的评论显示@符号,与被@人。

综上,采用二级结构(一级为文章的评论,二级为评论的评论)进行设计。

表设计

评论表

sql语句

实际使用还需要建索引,这里不再赘述,读者自行操作即可。

create table mianjing.mj_comment
(
    id          bigint                             not null
        primary key,
    mj_id       bigint                             not null,
    parent_id   bigint   default -1                not null,
    user_id     bigint                             not null,
    content     varchar(256)                       not null,
    reply_to    bigint                             null,
    like_count  int      default 0                 null,
    upload_time datetime default CURRENT_TIMESTAMP null
)
    comment '评论表';
字段名字段含义
comment_id评论表id
mj_id所属文章
parent_id父评论id(如果是一级评论则为-1)
user_id用户id
upload_time发表时间
content内容
reply_to回复人(如果是对文章的评论,则为空)
like_count点赞数

用户表(部分)

此处用户表仅贴出业务需要的部分。

sql语句
create table mianjing.mj_user
(
    id            bigint                                not null
        primary key,
    name          varchar(20)                           not null,
    password      varchar(128)                          not null,
    avatar        varchar(256)                          null,
)
    comment '用户表';

业务设计

前期准备

导包
<!--其他的mysql、mybatisplus等包自行导入-->

<!--字符串处理用到了这个-->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.7</version>
</dependency>

<!--时间处理用到了这个-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.7</version>
</dependency>
配置
//分页插件
@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}
//分页配置类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageParams {

    private int page = 1;
    private int pageSize = 10;
}
@Data
@TableName("mj_comment")
public class Comment {
    @TableId(value = "id",type = IdType.ASSIGN_ID)
    private Long id;

    private Long parentId;

    private Long mjId;

    private Long userId;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "Asia/Shanghai")
    private LocalDateTime uploadTime;

    private String content;

    private Long replyTo;

    private Integer likeCount;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
/**
 * 评论VO
 */
public class CommentVO {
    private Long commentId;
    private Long userId;
    private String avatar;
    private String content;
    private String parentId;
    private String username;
    private String uploadTime;
    private Integer likeCount;
    private Long replyId;
    private String replyName;
    private List<CommentVO> child;
    private Integer childCount;
}
/**
 * 评论或者回复的个数以及评论列表
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CommentDetailVO{
    int count;
    List<CommentVO> records;
}

业务代码

@RestController
@RequestMapping("/comment")
public class CommentController {
    /**
     * 文章默认展示两条热门评论,id为文章id,Result为公共返回类
     */
    @GetMapping("/{id}")
    public Result getHotComment(@PathVariable("id")Long id){
        return commentService.getHotComment(id);
    }


    @GetMapping("/all/{id}")
    public Result getAllComment(@PathVariable("id")Long id,
                                @RequestParam(value = "page",required = false,defaultValue = "1") Integer page){
        return commentService.getAllComment(page,id);
    }

    @GetMapping("/all/reply/{commentId}")
    public Result getAllReply(@PathVariable("commentId")Long id,
                              @RequestParam(value = "page",required = false,defaultValue = "1") Integer page){
        return commentService.getAllReply(page,id);
    }
}
public interface CommentService {

    Result getHotComment(Long id);

    Result getAllComment(int page,Long id);

    Result getAllReply(int page,Long id);
}
@Service
@Transactional
@Slf4j
public class CommentServiceImpl implements CommentService {

    @Autowired
    private CommentMapper commentMapper;

    @Override
    public Result getHotComment(Long id) {
        List<CommentVO> hotComment = commentMapper.getHotComment(id);
        return Result.success(hotComment);
    }

    @Override
    public Result getAllComment(int p,Long id) {
        PageParams pageParams = new PageParams();
        pageParams.setPage(p);
        Page<CommentVO> page = new Page<>(pageParams.getPage(),pageParams.getPageSize());
        IPage<CommentVO> commentVOIPage = commentMapper.getAllComment(page,id);
        List<CommentVO> records = commentVOIPage.getRecords();
        List<Long> list = new ArrayList<>();
        for(int i = 0; i < records.size();i++){
            list.add(records.get(i).getCommentId());
        }
        //每个评论有多少回复(子评论)
        List<Integer> counts = commentMapper.getReplyCount(list);
        for(int i = 0; i < records.size();i++){
            records.get(i).setChildCount(counts.get(i));
        }

        QueryWrapper<Comment> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("mj_id",id);
        //总评论数
        Integer count = commentMapper.selectCount(queryWrapper);

        CommentDetailVO commentDetailVO = new CommentDetailVO(count,records);
        return Result.success(commentDetailVO);
    }

    @Override
    public Result getAllReply(int p, Long id) {
        PageParams pageParams = new PageParams();
        pageParams.setPage(p);
        Page<CommentVO> page = new Page<>(pageParams.getPage(),pageParams.getPageSize());
        IPage<CommentVO> allReply = commentMapper.getAllReply(page, id);
        List<CommentVO> records = allReply.getRecords();
        QueryWrapper<Comment> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("parent_id",id);
        //评论的子评论数
        Integer count = commentMapper.selectCount(queryWrapper);
        CommentDetailVO commentDetailVO = new CommentDetailVO(count,records);
        return Result.success(commentDetailVO);
    }
@Mapper
public interface CommentMapper extends BaseMapper<Comment> {

    List<CommentVO> getHotComment(@Param("id") Long id);

    IPage<CommentVO> getAllComment(Page<CommentVO> page, @Param("id") Long id);

    List<Integer> getReplyCount(@Param("list") List<Long> list);

    IPage<CommentVO> getAllReply(Page<CommentVO> page, @Param("id") Long id);
}
<resultMap id="getCommentVO" type="com.mianjingnet.comment.vo.CommentVO">
        <id column="comment_id" property="commentId"/>
        <result column="user_id" property="userId"/>
        <result column="parent_id" property="parentId"/>
        <result column="avatar" property="avatar"/>
        <result column="content" property="content"/>
        <result column="username" property="username"/>
        <result column="upload_time" property="uploadTime"/>
        <result column="like_count" property="likeCount"/>
        <collection property="child" ofType="com.mianjingnet.comment.vo.CommentVO">
            <id column="child_comment_id" property="commentId"/>
            <result column="child_user_id" property="userId"/>
            <result column="child_parent_id" property="parentId"/>
            <result column="child_avatar" property="avatar"/>
            <result column="child_username" property="username"/>
            <result column="child_content" property="content"/>
            <result column="child_upload_time" property="uploadTime"/>
            <result column="child_like_count" property="likeCount"/>
            <result column="child_reply_to" property="replyId"/>
            <result column="child_reply_name" property="replyName"/>
        </collection>
    </resultMap>

    <select id="getHotComment" resultType="com.mianjingnet.comment.vo.CommentVO">
        select c.id as comment_id,
        u.id as user_id,
        u.avatar as avatar,
        c.content as content,
        u.name as username,
        c.upload_time as upload_time,
        c.like_count as like_count
        from mj_comment as c left join mj_user as u on c.user_id = u.id
        where c.mj_id = #{id} and c.parent_id = -1
        order by c.like_count desc,c.upload_time desc
        limit 2
    </select>

<!--   这个sql可太复杂了。。-->
    <select id="getAllComment" resultMap="getCommentVO">
        select c.id as comment_id,
        c.user_id as user_id,
        c.parent_id as parent_id,
        c.content as content,
        c.upload_time as upload_time,
        c.like_count as like_count,
        u.name as username,
        u.avatar as avatar,

        cchild.id as child_comment_id,
        cchild.user_id as child_user_id,
        cchild.parent_id as child_parent_id,
        u1.avatar as child_avatar,
        u1.name as child_username,
        cchild.content as child_content,
        cchild.upload_time as child_upload_time,
        cchild.like_count as child_like_count,
        cchild.reply_to as child_reply_to,
        u2.name as child_reply_name
        from mj_comment as c left join mj_comment as cchild on c.id = cchild.parent_id
        left join mj_user as u on c.user_id = u.id
        left join mj_user as u1 on cchild.user_id = u1.id
        left join mj_user as u2 on cchild.reply_to = u2.id
        where c.mj_id = #{id} and c.parent_id = -1
        <!--   获取两条子评论,并且按发表时间顺序排列, &lt;=  是小于等于的意思,总感觉这里写的不太好,应该有效率更高的手段,各位大佬如果有想法可以在评论区说明-->
        and (select count(*) from mj_comment as cchild2 where cchild2.parent_id = cchild.parent_id and cchild.upload_time >= cchild2.upload_time) &lt;= 2
        <!--   按照点赞量从大到小,时间从后到前,子评论从前到后排序-->
        order by c.like_count desc, c.upload_time desc , cchild.upload_time asc
    </select>

<!--    因为需要对于id,所以按照评论的排序规则进行排序-->
    <select id="getReplyCount" resultType="java.lang.Integer">
        select count(m2.id)
        from mj_comment as m1 left join mj_comment as m2 on m1.id = m2.parent_id
        where m1.id in
        <foreach collection="list" item="commentId" open = "(" close=")" separator=",">
            #{commentId}
        </foreach>
        group by m1.id
        order by m1.like_count desc, m1.upload_time desc
    </select>
    <select id="getAllReply" resultType="com.mianjingnet.comment.vo.CommentVO">
        select m1.id as comment_id,
        m1.user_id as user_id,
        u.name as username,
        u.avatar as avatar,
        m1.content as content,
        m1.parent_id as parent_id,
        m1.upload_time as uploadTime,
        m1.like_count as like_count,
        m1.reply_to as reply_id,
        u1.name as reply_name
        from mj_comment as m1 left join mj_user as u on m1.user_id = u.id
        left join mj_user as u1 on m1.reply_to = u1.id
        where parent_id = #{id}
        order by upload_time
    </select>

小结

这样基本就大功告成了,对于本文有什么困惑或者遗漏烦请在评论区指出。后续会继续更新点赞业务的设计(绝对和当前能查到的设计方案不一样,而且我自认为更好),想看后续的请点赞并关注哦~

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值