Level 75
Use two queries. An eloquent one to many is two queries.
Nov 24, 2021
9
Level 1
How to receive nested db result in laravels query builder
Hi
I'm struggling with Laravels Query Builder.
My question: Is there a way to retrieve "nested" results as shown in the first case (using Eloquent) when I'm using Laravels Query Builder (second case)?
First case: If I'm using the following Eloquent query, I receive a "nested" result:
Shift::with('users')->get();
//Result:
Illuminate\Database\Eloquent\Collection {
all: [
App\Models\ShiftPlanner\Shift {
id: 1,
name: "Shift 1",
date: "2021-11-01",
begin: "09:00:00",
end: "11:00:00",
users: App\Models\UserManagement\User {
id: 8,
first_name: "Eliane",
name: "Bürgi",
...,
},
},
...
],
}
Second case: If I'm using the following Laravel Query Builder query, I receive a "flat" array:
DB::table('shifts')
->join('users' , 'shifts.user_id' , '=' , 'users.id')
->get();
//Result
Illuminate\Support\Collection {
all:
[
{
"name": "Shift 1",
"date": "2021-11-01",
"begin": "09:00:00",
"end": "11:00:00",
"first_name": "Eliane",
"name": "Bürgi",
...,
},
{
"name": "Shift 2",
"date": "2021-11-05",
"begin": "09:00:00",
"end": "11:00:00",
"first_name": "Michaela",
"name": "Kaiser",
...,
},
]
}
In the second case, I get two rows back if there are two users assigned to the layer. This is then rather tedious to output the result with the help of loops.
Level 1
@jlrdw Sorry, I do not know what you mean. Could you give me an example, please?
Level 75
@2108web Just quick example:
public function ownerPets() {
$page = Request()->input('page', '1');
$ownerid = Request()->input('ownerid', '1');
$owner = Powner::find($ownerid); // parent record
$pets = DB::table('dc_pets')
->where('owner_id', '=', $ownerid)
->orderBy('petname', 'asc')->limit(3)->get(); // child records
return view('pet/ownerpet')->with(compact('owner', 'pets'));
}
In view you could put parent at top then child (related records in table)
But are you wanting to only use a join.
Above output:
JIMMIE The Pet Owner
======================
45 | COLBY
2 | DERRICK
19 | DILAN
Just done a quick view, not formatted.
1 like
Level 54
When you eager load relations using with() under the hood a separate query runs for each relation being loaded.
For example, if you fetch all your shifts, with users, all the shifts will be fetched with one query, and then all the users where the user id is in the list you already fetched with the first query will be loaded from the user table. The results of the two queries get shuffled together by eloquent under the hood giving you the nested structure.
You can't really do that without eloquent as it's a function OF eloquent to hydrate models, rather than just fetch raw data from the DB. If you need it in that format, use eloquent - and add on extra logic as necessary with DB::raw and whatnot
Otherwise, yea, run separate queries, and manually fiddle with the data to get what you need.
1 like
Level 1
@willvincent Thanks, so it is like a left join?
Level 75
@2108web no a relation is not a left join, it's 2 queries. But your question was how to do this in query builder. In query builder you manually write the two queries like example.
Are you wanting only a left join. Which is different from the way the standard relations work.
I suggest some sql tutorials and understand the difference between a one-to-many versus a join.
I guess what confused me is yes eloquent is the correct solution but that wasn't the question.
2 likes
Level 102
@2108web it is more like this
//get rows based on the main query
$query1 = DB::table('shifts')
->get();
//get an array of the relation key
$ids = $query1->pluck('user_id');
$query2 = DB::table('users')
->whereIn('id', $ids)
->get();
//key the users by id for easier lookup
$users = $query2->keyBy('id');
//now match them up
foreach ($query1 as $shift) {
$shift->user = $users[$shift->user_id];
}
This is close to what laravel does, but laravels version is of course better
Level 122
why not use eloquent?
1 like
Level 75
@2108web I agree you should use eloquent for that. My answer was only based on the fact that you specifically asked about query builder.
1 like
Please or to participate in this conversation.