如何通过QueryBuilder按帖子维度查询全量评论状态用户列表

这个需求完全可以实现，核心问题出在原有查询的数据集构建逻辑上——你当前的查询逻辑天生无法覆盖“未发布评论的用户与对应帖子的配对记录”。

• 数据集起点选错：以Comments评论表为查询根数据集，天生只包含“已存在评论”的行，根本没有覆盖所有帖子与所有用户的全量配对关系，哪怕加了右连接也无法补全缺失的配对
• 连接逻辑缺失：既没有生成「每篇帖子 × 所有用户」的基础配对行，原有的右连接只关联用户和评论，也没有拆分帖子维度的独立行，导致无评论的用户在对应帖子下根本不存在记录，自然无法被筛选返回
• 筛选逻辑错配：当按post_id过滤时，因为缺失的配对行不在初始数据集内，最终只能返回有评论的用户记录
• 字段映射偏差：原有代码取comments.updated_at作为返回字段，和需求要求的评论创建时间字段不匹配

要达成目标，必须先构建正确的基础数据集，再关联评论表匹配对应内容，分三步实现：

1. 生成全量配对基础集：通过CROSS JOIN（交叉连接）把帖子表和用户表做笛卡尔积，生成的每一行对应「某一篇帖子 + 某一个注册用户」的固定配对，这就是最终列表的全量行来源，行数等于总帖子数×总用户数，和示例结构完全匹配
2. 左连接评论表做匹配：在基础集上通过LEFT JOIN关联评论表，匹配条件必须同时带两个字段：基础集的post_id等于评论表的post_id、基础集的user_id等于评论表的user_id。左连接的特性是无论评论表有没有匹配结果，基础集的行都会完整保留
3. 占位符填充：使用COALESCE函数把左连接匹配不到的text、created_at字段的NULL值替换成要求的'-'占位符

use Spatie\QueryBuilder\QueryBuilder;
use Illuminate\Support\Facades\DB;

// 构建全量配对基础集
$baseQuery = DB::table('posts')
    // 交叉连接生成 帖子×全量用户 的配对行
    ->crossJoin('users')
    // 左连接评论表，双字段匹配避免错配
    ->leftJoin('comments', function ($join) {
        $join->on('posts.id', '=', 'comments.post_id')
             ->on('users.id', '=', 'comments.user_id');
    })
    ->select(
        // 生成连续行号作为列表id
        DB::raw('ROW_NUMBER() OVER (ORDER BY posts.id, users.id) AS id'),
        'posts.id AS post_id',
        'users.id AS user_id',
        // 无匹配记录时填充占位符
        DB::raw("COALESCE(comments.text, '-') AS text"),
        DB::raw("COALESCE(comments.created_at, '-') AS created_at")
    );

// 包裹查询构建器支持筛选
return QueryBuilder::for($baseQuery)
    ->allowedFilters([
        'post_id'
    ])
    ->get();

• 左连接的匹配条件必须同时带上post_id和user_id双字段，否则会把用户在其他帖子下的评论错误匹配到当前帖子的记录上
• 避免把post_id筛选条件写在评论表的字段上，否则会把左连接退化为内连接，重新出现“只返回有评论用户”的问题——当前实现里post_id是从基础集的帖子表字段映射出来的，筛选不会破坏全量配对
• 如果站点的帖子、用户量级较大，可以提前把「帖子×用户」的配对关系物化到独立存储表，避免每次查询实时计算笛卡尔积带来的性能开销

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