如何在单条SQL查询中实现帖子查询、点赞统计及点赞状态判断

问题场景 #

开发博客应用，涉及posts和likes两张表，需要执行单条SQL查询完成以下需求：

1. 获取指定用户发布的所有帖子
2. 同时返回每条帖子的总点赞数
3. 标记当前登录用户是否点赞过该帖子

原尝试的SQL存在语法逻辑错误，无法正确返回结果。

正确的SQL实现 #

SELECT 
    post.id,
    post.title,
    post.body,
    COUNT(likes.post_id) AS total_likes,
    CASE WHEN lk.liker_id IS NOT NULL THEN 1 ELSE 0 END AS has_liked
FROM posts AS post
LEFT JOIN likes 
    ON likes.post_id = post.id
LEFT JOIN likes AS lk 
    ON lk.post_id = post.id AND lk.liker_id = 'current_user_id'
WHERE post.owner_id = 'target_user_id'
GROUP BY post.id, post.title, post.body, lk.liker_id;

关键修正点 #

• WHERE条件分离：原SQL把post.owner_id放在JOIN条件里，导致会关联所有帖子的点赞数据，应该放到WHERE里过滤目标用户的帖子
• COUNT指定字段：COUNT(likes.post_id)避免统计NULL值（未被点赞的帖子不会返回错误计数）
• 用CASE判断点赞状态：通过判断关联的当前用户点赞记录是否存在，用1/0标记has_liked状态
• GROUP BY完整字段：MySQL 5.7+要求GROUP BY包含所有非聚合查询字段，避免分组逻辑错误

对应的GORM实现 #

如果使用GORM框架，可以通过原生SQL绑定到自定义结构体实现：

首先定义接收结果的结构体：

type PostWithLikeInfo struct {
    Id         int    `json:"id"`
    Title      string `json:"title"`
    Body       string `json:"body"`
    TotalLikes int    `json:"total_likes"`
    HasLiked   int    `json:"has_liked"` // 1表示已点赞，0表示未点赞
}

然后执行查询：

var posts []PostWithLikeInfo
targetUserId := 123 // 要查询的帖子作者ID
currentUserId := 456 // 当前登录用户ID

db.Raw(`
    SELECT 
        post.id,
        post.title,
        post.body,
        COUNT(likes.post_id) AS total_likes,
        CASE WHEN lk.liker_id IS NOT NULL THEN 1 ELSE 0 END AS has_liked
    FROM posts AS post
    LEFT JOIN likes 
        ON likes.post_id = post.id
    LEFT JOIN likes AS lk 
        ON lk.post_id = post.id AND lk.liker_id = ?
    WHERE post.owner_id = ?
    GROUP BY post.id, post.title, post.body, lk.liker_id
`, currentUserId, targetUserId).Scan(&posts)

性能优化提示 #

• 给likes表创建post_id和liker_id的组合索引：CREATE INDEX idx_likes_post_liker ON likes(post_id, liker_id);，避免全表扫描
• 针对大量帖子场景，建议添加分页逻辑，避免一次性返回过多数据

内容的提问来源于stack exchange，提问作者darkstar