Question 17208 in Laravel Question posted in
The official Laravel Documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    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`
');
    Login or Signup to reply.
  2. 0

    — UPDATE —

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

    https://laraveltesting.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 = ItulSqlToLaravelSqlToLaravel::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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search