The query is based on optimized code from#372 The method takes two optional search parameters and binds result to : - Subject
- Array of Creator’s Emails.
public static function searchInThreads(string $search = null, array $creators ) { try { return Thread::join('participants as p', 'p.id', 'threads.id' ) ->join('messages as m', 'm.thread_id', 'threads.id' ) ->join('users as u', 'm.user_id', 'u.id' ) ->select ( array( 'u.email as creator', 'p.*', 'threads.*', 'threads.id as id', 'm.*', \DB::raw( '(select count(*) from participants where thread_id = threads.id) as participants'), \DB::raw( '(select count(*) from messages where thread_id = threads.id) as message_count'), \DB::raw( '( select count(m.id) from messages m inner join participants p ON m.thread_id = p.thread_id where m.thread_id = threads.id and p.last_read < m.updated_at and p.user_id = '. Auth::id() .' ) as last_read_count') ) ) ->when($search, function ($query, $search) { return $query->where("subject", 'like', '%' . $search . "%"); }) ->when($creators, function ($query, $creators) { return $query->whereIn("u.email", $creators ); }) ->latest('threads.updated_at') ->groupBy('threads.id') ->paginate(self::PAGINATION_MESSAGES);
Following attributes will be available out of the box in partials/thread.blade.php, others may be added directly into the above query. $thread->thread_id, $thread->subject, $thread->last_read_count, $thread->participants, $thread->message_count, $thread->creator
|