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.