How To Get All First Records (ordered By A Column) Of A Nested One To Many Relationship In Laravel?
I've some nested one to many relationship as follows:
Enquiry
hasMany Item
Item
hasMany Component
Component
hasMany Process
Imagine I have the Enquiry
Model loaded already. Now I would like to access the first Process
of each Component
of each Item
ordered by a column in Process
called order
This picture might show it better (Sorry for using ERD like this)
What I've got so far is something like this:
$enquiry->items->load(['components' => function($query) {
$query->with(['processes' => function($query) {
$query->orderBy('order')->limit(1)
}]);
}])->get();
but it only gives me the top process, not the top Process
of each Component
of each Item
belonging to the given Enquiry
.
Any help would be appreciated. And ofcourse any help regarding a better title would be much appreciated as well. Thank you.
Answer
Thanks to @PaulSpiegel Link it directed me to the right way. This is how I did it with subqueries in Laravel using QueryBuilder.
$subQuery = DB::table('processes as icp')
->selectRaw('min(icp.order), icp.*')
->groupBy('icp.component_id');
$firstProcesses = DB::table('enquiries as enq')
->where('enq.id', $enquiry->id)
->join('items', 'items.enquiry_id', 'enq.id')
->join('components as ic', 'ic.item_id', 'items.id')
->joinSub($subQuery, 'icp', function ($join) {
$join->on('ic.id', 'icp.component_id');
})->get();
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key