CODEWITHSUNDEEP
laravellaravel-8
Jay
Jay

Reputation: 841

laravel leftjoin on json

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.

Upvotes: 0

Views: 194

Answers (2)

dvicemuse
dvicemuse

Reputation: 586

--- UPDATE ---

I put together a quick package to turn raw sql into a laravel query builder object.

https://laraveldocs.itulbuild.com/documentation/sql-to-laravel

INSTALL

composer require itul/sql-to-laravel

EXAMPLE

//ORIGINAL RAW SQL
$sql = "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`";

//CONVERT TO A LARAVEL QUERY BUILDER OBJECT
$query = \Itul\SqlToLaravel\SqlToLaravel::convert($sql);

//THE QUERY CAN NOW BE USED AS IF IT WAS NATIVELY WRITTEN IN LARAVEL FORMAT
$query->where('meeting.id', '>=', 100);

//GET THE RESULTS
dd($query->get());

--- ORIGINAL ANSWER ---

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.

Upvotes: 0

Techno
Techno

Reputation: 1696

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`
');

Upvotes: 1

Related Questions