Optimize Thread::getAllLatest

[ronlinet]

The same as in #81 and #79 I have noticed an excessive SQL querying while loading the demo thread view blade.

The bellow code improves the thread list loading by 300%. All params used in the blade view are loaded only once and may be reused later in partials/thread.blade.php

    public static function getAllLatest( int $numberOfPages )
    {
        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')
                )
            )
            ->latest('threads.updated_at')
            ->groupBy('threads.id')
            ->paginate($numberOfPages);
    }

Following attributes will be available out of the box, 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

Beside that it is an excellent library it spares me lot of dev work from scratch. Thank your for your time guys.