Count of left Join with subquery on latest raw filtered with whereRaw - Laravel

I have my customers purchase history query, with chats between customer and admin in each purchases, these chats are grouped by comment_topics which are listed in comment_topics table.

For now everything works fine as below, except that i want to get number of total chats corresponding to a comment_topic inside a purchase, but for now i have used latest raw from chats table to get details like last_author of chat. How do i get total number of chats before executing latest raw from the subquery.

    $purchases = new Purchases;
    $purchases = $purchases->newQuery();        
    $purchases = $purchases->join('customers', 'purchases.customerid', '=', 'customers.id');
    
    $purchases = $purchases->leftjoin('comment_topics', 'purchases.purchaseid', '=', 'comment_topics.chats_ref');

    $purchases = $purchases->leftjoin('chats', function($query) {
        $query->on('comment_topics.id','=','chats.chat_topic_id')
        ->whereRaw('chats.id IN (select MAX(chats.id) from chats join comment_topics on comment_topics.id = chats.chat_topic_id group by comment_topics.id)');
    });
    $purchases = $purchases->leftJoin('branches', 'branches.id','=','purchases.branch_id');   

    $purchases = $purchases->selectRaw('purchases.purchaseid,purchases.customerid,purchases.order_total, purchases.order_name as customer_name, store_name, purchases.branch_id, comment_topics.id as chat_topic_id, chats.chat_author as last_author');

    $purchases = $purchases->orderBy('purchases.created_at', 'desc')->paginate(10);
    return response()->json($purchases);

The above is my working code. I have been trying many ways to get the count of chats and none succeeded.

This is the subquery part:

$purchases = $purchases->leftjoin('chats', function($query) {
    $query->on('comment_topics.id','=','chats.chat_topic_id')
    ->whereRaw('chats.id IN (select MAX(chats.id) from chats join comment_topics on comment_topics.id = chats.chat_topic_id group by comment_topics.id)');
});

Please guide. Thanks in advance.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3vN7L0j
via IFTTT