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

Replicating complex MySQL/MariaDB query with multiple joins in Laravel DB Builder

I have a query with multiple joins. Most joins are a 1 to 1 but two are 1 to man. My sql query returns results as expected with data from the 1 to many joins flattened into a single value. The two 1 to many joins are excuses and travel Here’s my query:

select 
    a.Lastn,
    c.name, 
    GROUP_CONCAT(f.type,',',f.description) as Excuses, 
    GROUP_CONCAT(i.start,',', i.end,',', i.destination) as Travel 
from 
    jurors as a left join        
    pool_lkp as b on a.id = b.j_id left join 
    pools as c on b.p_id = c.id left join 
    accommodations as d on a.id = d.juror_id left join 
    employment as e on a.id = e.juror_id left join 
    excuses as f on a.id = f.juror_id left join 
    new_address as g on a.id = g.juror_id left join 
    spouse as h on a.id = h.juror_id left join 
    travel as i on a.id = i.juror_id 
where 
    c.id = 1 
group by 
    a.id

I attempted to recreate this in Laravel using the DB Query builder. The data from the 1 to many joins repeats several times. I suspect it is because of the two 1 to many joins would return multiple rows for a single record where that row has values in the two 1 to many tables.

Here’s my attempt at replicating my query using the Laravel DB builder.

$results = DB::table('jurors')
    ->leftJoin('pool_lkp','jurors.id','=','pool_lkp.j_id')
    ->leftJoin('pools','pool_lkp.p_id','=','pools.id')
    ->leftJoin('accommodations','jurors.id','=','accommodations.juror_id')
    ->leftJoin('employment','jurors.id','=','employment.juror_id')
    ->leftJoin('excuses','jurors.id','=','excuses.juror_id')
    ->leftJoin('new_address','jurors.id','=','new_address.juror_id')
    ->leftJoin('spouse','jurors.id','=','spouse.juror_id')
    ->leftJoin('travel','jurors.id','=','travel.juror_id')
    ->select(
        'jurors.id as jurorId',
        'jurors.*',
        'pools.*',
        DB::raw("GROUP_CONCAT(excuses.type,',',excuses.description) as Excuses"),
        DB::raw("GROUP_CONCAT(travel.start,',', travel.end,',', travel.destination) as Travel")
    )
    ->where('pools.id','=',$request->segment(3))
    ->groupBy('jurors.id')
    ->get();

I expected to see the data for the 1 to many tables flattened into a string in Excuses and Travel within my returned results. I see the flattened strings in the results returned for the Laravel DB builder query but the strings repeat several times.

Is there a bug in Laravel’s DB Builder?

Tags:

2

Answers


  1. 0

    My guess because Your queries differ?
    In Raw SQL You do: SELECT FROM pool and in Laravel You do: SELECT FROM jurors

    Login or Signup to reply.
  2. 0

    Different queries tend to give different results. You can call the toSql() or toRawSql() method on a query builder object to make sure your query is looking the way you expect it to.

    Other than that, for large queries like this you can make the Builder look very similar to SQL.

    $query = DB::query()
    ->select([
        'a.name', 
        'c.Lastn', 
        DB::raw('GROUP_CONCAT(f.type, ",", f.description) AS Excuses'), 
        DB::raw('GROUP_CONCAT(i.start, ",", i.end, ",", i.destination) AS Travel') 
    ])
    ->from('pools', 'a')
    ->leftJoin('pool_lkp AS b', 'a.id', 'b.p_id')
    ->leftJoin('jurors AS c', 'b.j_id', 'c.id')
    ->leftJoin('accommodations AS d', 'c.id', 'd.juror_id')
    ->leftJoin('employment AS e', 'c.id', 'e.juror_id')
    ->leftJoin('excuses AS f', 'c.id', 'f.juror_id')
    ->leftJoin('new_address AS g', 'c.id', 'g.juror_id')
    ->leftJoin('spouse AS h', 'c.id', 'h.juror_id')
    ->leftJoin('travel AS i', 'c.id', 'i.juror_id')
    ->where('a.id', 1)
    ->groupBy('c.id');

// dd($query->toRawSql());
$results = $query->get();

    Perhaps one of the aliases was the issue?

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

Back To Top
Search