仿知乎评论业务设计（Redis点赞功能设计）
前言
明年就要秋招了，想着做个项目丰富下自己的简历。项目中有个评论业务，一开始在百度上搜相关方案，要么内容不全复制粘贴，要么设计有硬伤无法采用。因为本人是重度知乎使用者，觉得知乎的评论设计还算可以，因此下定决心自己从零开始设计一款评论系统。笔者能力有限，有什么错误还望各位大神在评论区指出。
业务要求
先看知乎是如何设计的。
对于文章详情页，显示两条热点评论、发起评论用户名、id、时间、点赞数、评论内容。
点击查看全部评论后，显示评论以及评论的子评论。子评论只显示两条。
点击查看全部回复后，显示该评论下的所有回复，按回复时间顺序排列。
业务总结
-
文章只显示两条评论，点击查看全部评论进入评论详情页
-
评论要显示的内容：用户头像、用户名称，评论时间，评论内容。子评论（如果有）只显示两条，不显示评论的回复。
-
文章的评论按照点赞数排序。
-
子评论按照评论时间顺序排列。对于子评论的评论显示@符号，与被@人。
综上，采用二级结构（一级为文章的评论，二级为评论的评论）进行设计。
表设计
评论表
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
<!-- 获取两条子评论，并且按发表时间顺序排列， <= 是小于等于的意思，总感觉这里写的不太好，应该有效率更高的手段，各位大佬如果有想法可以在评论区说明-->
and (select count(*) from mj_comment as cchild2 where cchild2.parent_id = cchild.parent_id and cchild.upload_time >= cchild2.upload_time) <= 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>
小结
这样基本就大功告成了，对于本文有什么困惑或者遗漏烦请在评论区指出。后续会继续更新点赞业务的设计（绝对和当前能查到的设计方案不一样，而且我自认为更好），想看后续的请点赞并关注哦~
扫一扫
343
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
