laravel leftjoin on json-CodePudding

I have Raw query running as

select meeting.id, GROUP_CONCAT(users.name separator " | ") AS present_user_id from `meeting` left join `users` on JSON_CONTAINS(meeting.present_user_id, JSON_ARRAY(users.id), '$') group by `meeting`.`id`

Which provide proper result, I tried to convert same in Laravel 8 like this

DB::table('meeting')
->selectRaw(' meeting.id, GROUP_CONCAT(users.name separator " | ") AS present_user_id')
->leftJoin('users', DB::raw("JSON_CONTAINS(meeting.present_user_id, JSON_ARRAY(users.id), '$')", DB::raw(' '), DB::raw(' ')))
->groupBy('meeting.id')
->get();

This create query as

select meeting.id, GROUP_CONCAT(users.name separator " | ") AS present_user_id from `meeting` left join `users` on JSON_CONTAINS(meeting.present_user_id, JSON_ARRAY(users.id), '$') = `` group by `meeting`.`id`

So Laravel add ( = `` ) at the end of join which I don't want and want to remove let me know how can I achieve it. I do want to use QueryBuilder only.

CodePudding user response：

In this case, writing it with laravel's QueryBuilder does not provide any readability or code enhancement. I would K.I.S.S.:

$result = DB::select('
   select 
     meeting.id, 
     GROUP_CONCAT(users.name separator " | ") AS present_user_id 
   from `meeting` 
   left join `users` on JSON_CONTAINS(meeting.present_user_id, JSON_ARRAY(users.id), '$') 
   group by `meeting`.`id`
');

CodePudding user response：

Without having access to your database... I suspect the issue is that ->leftJoin requires all 4 parameters and you're passing the last 2 as blank.

Maybe try

DB::table('meeting')
->selectRaw(' meeting.id, GROUP_CONCAT(users.name separator " | ") AS present_user_id')
->leftJoin('users', DB::raw("JSON_CONTAINS(meeting.present_user_id, JSON_ARRAY(users.id), '$')", DB::raw('IS NOT'), DB::raw('NULL')))
->groupBy('meeting.id')
->get();

This is just a guess since there isnt enough info.

If that doesnt work... try sharing some records from your database.