遇到的问题

做项目时，评论表中包含以下两个字段

  • 发布评论的用户id
  • 此评论回复@的用户id

查询时需要查出以下字段

  • 评论表的全部字段
  • 发布评论的用户姓名和头像地址
  • 此评论回复@的用户姓名和头像地址

此时就遇到需要连续两次连用户表进行查询

解决办法

两次left join

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
	sonComment.*,
	user.nickname AS `commentUserName`,
	user.avatar AS `comment_user_avatar`,
	aa.nickname AS `comment_reply_name`,
	aa.avatar AS `comment_reply_avatar` 
FROM
	`sonComment`
	LEFT JOIN `user` ON user.user_id = sonComment.son_comment_user_id
	LEFT JOIN (
	SELECT
		a.son_comment_id,
		b.avatar,
		b.nickname,
		b.`user_id` 
	FROM
		sonComment a
	INNER JOIN user b ON b.user_id = a.son_comment_reply_user_id 
	) aa ON aa.`user_id` = sonComment.`son_comment_reply_user_id`

遇到的问题2

debug模式下，所查询出来的有结果。但是返回输出的对象为null，是怎么造成的呢？

注意返回对象和数据库返回字段的对应关系。java对象驼峰命名，mysql用_命名

在mapper.xml的resultType选择对应的java对象

1
2
<select id="listJoinInfoPages" resultType="com.yxz.wulibibiji.entity.Soncomment">
</select>
1
2
3
4
5
6
7
8
2023-01-10 18:22:23.359 DEBUG 4480 --- [nio-8081-exec-1] c.y.w.m.S.listJoinInfoPages              : ==>  Preparing: SELECT 
sonComment.*, user.nickname AS `comment_user_name`, user.avatar AS `comment_user_avatar`, aa.nickname AS `comment_reply_name`, aa.
avatar AS `comment_reply_avatar` FROM `sonComment` LEFT JOIN `user` ON user.user_id = sonComment.son_comment_user_id LEFT JOIN ( 
    SELECT a.son_comment_id, b.avatar, b.nickname, b.`user_id` FROM sonComment a INNER JOIN user b ON b.user_id = a.
    son_comment_reply_user_id ) aa ON aa.`user_id` = sonComment.`son_comment_reply_user_id` WHERE (son_comment_parent_id = ?) 
    ORDER BY son_comment_created_time ASC LIMIT ?
2023-01-10 18:22:23.360 DEBUG 4480 --- [nio-8081-exec-1] c.y.w.m.S.listJoinInfoPages              : ==> Parameters: 29(Integer), 10(Long)
2023-01-10 18:22:23.405 DEBUG 4480 --- [nio-8081-exec-1] c.y.w.m.S.listJoinInfoPages              : <==      Total: 1