Filtering unanswered tickets with the query builder

Question

I need to generate a table of tickets not answered by the user, in this case the student. I would like to make the filter directly in the DB because it depends on other filters and there is also pagination.

students	tickets	ticket_comments
user_id	student_id	ticket_id
		user_id

Filtering should capture tickets with no comments and tickets where the last comment is not from the student in question, ie tickets where the last comment is not from students.user_id, but this in reference to the ticket itself.

This filtering I've done so far mixes the query with "latest()" with all tickets, I've done several tests and the results are confusing.

$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');

Can someone with vast knowledge of queries help me?

In the last case I will make a workaround to work, but I was curious to know if it was possible to do this filtering.

Answer 1

after a while a came to this solution, this is primary, but from this example anyone could do more.

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