Ad

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)

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.

Ad

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();
Ad
source: stackoverflow.com
Ad