Search by Title and Thread Creators.

[ronlinet]

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