HasMany关联下JOIN表并获取最新关联数据的正确实现方案

方法1：子查询锁定最新评论 #

通过子查询先获取每个帖子的最新评论ID，再关联主查询，确保每个帖子仅返回一条结果，同时支持对post_comments的筛选。

SELECT 
    p.*,
    pc.*
FROM posts p
LEFT JOIN (
    -- 子查询：按帖子分组，获取每个帖子的最新评论ID
    SELECT post_id, MAX(id) AS latest_comment_id
    FROM post_comments
    -- 可在此添加评论筛选条件，比如特定用户的评论
    -- WHERE user_id = 123
    GROUP BY post_id
) latest_pc ON p.id = latest_pc.post_id
LEFT JOIN post_comments pc ON latest_pc.latest_comment_id = pc.id
-- 筛选有特定用户评论的帖子（若需要）
-- WHERE EXISTS (
--     SELECT 1 FROM post_comments pc_filter 
--     WHERE pc_filter.post_id = p.id AND pc_filter.user_id = 123
-- )
ORDER BY p.created_at DESC
LIMIT 10 OFFSET 0; -- 分页参数，LIMIT是每页条数，OFFSET是偏移量

说明 #

• 子查询latest_pc通过MAX(id)（或MAX(created_at)，取决于你定义“最新”的逻辑）锁定每个帖子的最新评论ID
• 外层通过该ID关联post_comments，确保每个帖子仅匹配一条最新评论
• 用EXISTS子查询处理基于评论的筛选，避免JOIN导致的帖子重复，同时精准定位符合条件的帖子
• 分页直接作用于最终结果，因为每条结果对应一个帖子

方法2：窗口函数（适配MySQL 8+/PostgreSQL等支持窗口函数的数据库） #

利用窗口函数给每个帖子的评论排序，直接取排名第一的最新评论：

SELECT 
    p.*,
    pc.*
FROM posts p
LEFT JOIN (
    SELECT 
        *,
        -- 按帖子分组，评论按创建时间倒序排名
        ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY created_at DESC) AS rn
    FROM post_comments
    -- 可在此添加评论筛选条件
    -- WHERE user_id = 123
) pc ON p.id = pc.post_id AND pc.rn = 1
-- 筛选有特定用户评论的帖子（若需要）
-- WHERE EXISTS (
--     SELECT 1 FROM post_comments pc_filter 
--     WHERE pc_filter.post_id = p.id AND pc_filter.user_id = 123
-- )
ORDER BY p.created_at DESC
LIMIT 10 OFFSET 0;

说明 #

• ROW_NUMBER()窗口函数为每个帖子的评论生成倒序排名，rn=1即最新评论
• 同样用EXISTS处理评论筛选，避免结果重复
• 分页逻辑与方法1一致，针对帖子维度分页

ORM层面实现（以Laravel Eloquent为例） #

如果使用ORM框架，可通过预加载和存在查询实现需求：

// 获取带最新评论的帖子，同时筛选有用户123评论的帖子
$posts = Post::query()
    ->with(['latestComment' => function ($query) {
        // 预加载每个帖子的最新评论
        $query->latest()->take(1);
    }])
    ->whereExists(function ($query) {
        // 筛选有指定用户评论的帖子
        $query->select(DB::raw(1))
            ->from('post_comments')
            ->whereColumn('post_comments.post_id', 'posts.id')
            ->where('post_comments.user_id', 123);
    })
    ->orderBy('created_at', 'desc')
    ->paginate(10); // 自动处理分页

对应的Post模型需定义关联：

class Post extends Model
{
    public function latestComment()
    {
        return $this->hasOne(PostComment::class)->latest();
    }
}

说明 #

• with(['latestComment'])预加载最新评论，避免N+1查询问题，且每个帖子仅加载一条最新评论
• whereExists精准筛选符合评论条件的帖子，不会导致结果重复
• paginate(10)直接生成分页数据，符合需求

为什么直接按post_id分组不合理？ #

直接按post_id分组后SELECT帖子和评论字段，会导致评论字段取分组后的任意一条数据（部分数据库如开启ONLY_FULL_GROUP_BY会直接报错），无法保证取到的是最新评论；同时如果筛选条件涉及评论，分组逻辑可能会过滤掉本该符合条件的帖子，因此这种方式不严谨。

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