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!
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire