使用查询构造器筛选学生未回复工单的技术求助

需求说明 #

需要生成学生未回复的工单表格，必须在数据库层面完成过滤（后续还要叠加其他过滤条件及分页需求）。

数据库表结构 #

过滤规则 #

需筛选出两类工单：

• 无任何评论的工单
• 最后一条评论的发布者不是对应学生（即students.user_id）的工单

当前问题 #

已编写的查询逻辑将latest()与所有工单的评论混排，导致测试结果不符合预期，需要优化查询逻辑。同时希望确认该过滤逻辑是否可在数据库层面实现，若无法解决再考虑替代方案。

当前代码 #

$this->select('tickets.*')
  ->join('students', 'tickets.student_id', 'students.id')
  ->leftJoin('ticket_comments', 'tickets.id', 'ticket_comments.ticket_id')
  ->where('ticket_comments.id', function($query) {
    $query->select('id')
      ->from('ticket_comments')
      ->whereColumn('ticket_comments.ticket_id', '=', 'tickets.id')
      ->whereColumn('ticket_comments.user_id', '!=', 'students.user_id')
      ->latest()
      ->limit(1);
  })->orWhereDoesntHave('comments');

优化后的查询方案 #

原查询的核心问题是：没有确保筛选出的“非学生评论”是该工单的最后一条评论，导致误判。以下是两种可行的优化方案：

方案1：基于MAX(created_at)获取最后评论 #

通过子查询先锁定每个工单的最后一条评论，再判断评论者是否为学生：

$this->select('tickets.*')
    ->join('students', 'tickets.student_id', '=', 'students.id')
    ->leftJoinSub(
        function ($query) {
            $query->select('ticket_id', 'user_id')
                ->from('ticket_comments')
                ->whereRaw('(ticket_id, created_at) IN (
                    SELECT ticket_id, MAX(created_at) 
                    FROM ticket_comments 
                    GROUP BY ticket_id
                )');
        },
        'latest_comments',
        'tickets.id',
        '=',
        'latest_comments.ticket_id'
    )
    ->where(function ($query) {
        // 条件：无评论 或 最后评论者不是当前学生
        $query->whereNull('latest_comments.ticket_id')
              ->orWhereColumn('latest_comments.user_id', '!=', 'students.user_id');
    });

方案2：使用窗口函数（MySQL 8+及以上支持） #

利用ROW_NUMBER()窗口函数给每个工单的评论按时间倒序排名，取排名第一的评论进行判断：

$this->select('tickets.*')
    ->join('students', 'tickets.student_id', '=', 'students.id')
    ->leftJoinSub(
        function ($query) {
            $query->select(
                'ticket_id',
                'user_id',
                \DB::raw('ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY created_at DESC) as rn')
            )->from('ticket_comments');
        },
        'ranked_comments',
        function ($join) {
            $join->on('tickets.id', '=', 'ranked_comments.ticket_id')
                 ->where('ranked_comments.rn', '=', 1);
        }
    )
    ->where(function ($query) {
        $query->whereNull('ranked_comments.ticket_id')
              ->orWhereColumn('ranked_comments.user_id', '!=', 'students.user_id');
    });

这两种方案都能准确实现过滤逻辑，且完全在数据库层面完成，支持后续叠加其他过滤条件和分页操作。

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