How In LeftJoin to add additive parameter?

In my laravel 5.7 / eloquent/ Mysql 5.5 app I need in chats listing to get values of messsages of any chat and 1 more column of messsages of any chat for given useronly ($user_id):

I tried as :

$chats          = Chat
     ::select( \DB::raw( ' chats.*, count(cm.id) as messages_count, count(cmu.id) as user_messages_count' ) )
    ->orderBy('name', 'asc')
    ->groupBy('chats.id')
    ->leftJoin( \DB::raw('chat_messages as cm'), \DB::raw('cm.chat_id'), '=', \DB::raw('chats.id') )
    ->leftJoin( \DB::raw('chat_messages as cmu'), \DB::raw(' cmu.chat_id = chats.id and cmu.chat_id = ' .$user_id ) )
    ->get();    

But error :

 Column not found: 1054 Unknown column '' in 'on clause'.

Looks like for leftJoin expression can not be used with empty value paramerter. Or in some other way?

$chats          = Chat
     ::select( \DB::raw( ' chats.*, count(cm.id) as messages_count, count(cmu.id) as user_messages_count' ) )
    ->orderBy('name', 'asc')
    ->groupBy('chats.id')
    ->leftJoin( \DB::raw('chat_messages as cm'), \DB::raw('cm.chat_id'), '=', \DB::raw('chats.id') )
    ->leftJoin(\DB::raw('chat_messages as cmu'), \DB::raw('cmu.chat_id'), '=', \DB::raw('chats.id'))
    ->where( 'cmu.user_id', $user_id)
    ->get();    // `chat_messages` ORDER BY `chat_id` A

That works , but not correctly as for condition

>where( 'cmu.user_id', $user_id)

left join is skipped and rows with user_messages_count = 0 are skipped, but I need them.

Which is the right way?

Thanks!

from Newest questions tagged laravel-5 - Stack Overflow http://bit.ly/2Ri16de
via IFTTT