How to get all first records (ordered by a column) of a nested one to many relationship in Laravel?

Question

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.

No. I mean I wouldn't bother with any of this laravel/eloquent stuff until I had a working query — Strawberry, Feb 04 '20 at 17:12
@Strawberry Well It can go both ways. I don't know how to do it in SQL either. If you have a solution in SQL I can use it either way. — Arman Momeni, Feb 04 '20 at 17:15
Try: `$query->where('id', DB::raw('select min(processes.id) from processes where processes.component_id = components.id'));` — Paul Spiegel, Feb 04 '20 at 17:17
For SQL - see this: [sql-select-only-rows-with-max-value-on-a-column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Paul Spiegel, Feb 04 '20 at 17:19
@PaulSpiegel well it didn't work. having min and max in DB::raw I don't know but it gives syntax error I tried different ways the link you provided was really helpful.but now I'm basically struggling with syntax problems — Arman Momeni, Feb 05 '20 at 17:08
`$query->where('order', DB::raw('(select min(p.order) from processes p where p.component_id = processes.component_id)'));` — Paul Spiegel, Feb 05 '20 at 17:12

kburlz · Answer 1 · 2020-02-04T18:27:12.890

0

Can't you define a relationship on your Component model like this:

class Component {
    public function firstProcess() {
        return $this->hasMany(Process::class)->orderBy('order')->take(1);
    }
}

And then do something like this:

$enquiry->load('items.components.firstProcess');

Then you should be able to access that property on each of your components:

foreach ($enquiry->items as $item) {
    foreach($item->components as $component) {
        print_r($component->firstProcess);
    }
}

I didnt test this code.

edited Feb 04 '20 at 18:27

answered Feb 04 '20 at 18:19

kburlz

606
1
4
18

I tried that it actually only works on the first process of the first component of the first item. same as my own way of getting it. It doesn't give all first processes. – Arman Momeni Feb 05 '20 at 17:07
Please don't write guesses as answer. – Paul Spiegel Feb 05 '20 at 17:15

score 0 · Accepted Answer · answered Feb 10 '20 at 13:06

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();

How to get all first records (ordered by a column) of a nested one to many relationship in Laravel?

2 Answers2