Laravel order data by relation property

Question

I have two related models 'Yards' & 'Inspections'. A yard can have multiple inspections.

I want to be able to order the Yard list based on the inspection property 'reinspect_at'. I have a Model method that returns the Latest Inspection for the yard based on the inspection date.

I have tried the following:

$yards = Yard::orderBy('quarantined', 'DESC')
        ->with(['latestInspection' => function($query){
            $query->orderBy('reinspect_at', 'asc');
        }])
        ->get();

But this doesn't order the yard based on the reinspect_at date, it is only ordering the latest inspection for each yard.

I want to be able to always return the latest inspection for the yard, but need to be able to reorder the yard list based on the reinspect_at date for the latest inspection for each yard. Cheers

Screenshot of yard list display below. As you can see the Inspection due date is how I want to be able to order the result.

** Update ** Looks like what I want is possible in the frontend template using:

@foreach( $yards->sortBy('latestInspection.reinspect_at') as $yard )

It would be preferable to do be able to do this from the controller though, so I can have other options for ordering by.

i am using datatable plugin for sorting...can you show a screenshot of a result that you get — lewis4u
– lewis4u, Commented Dec 6, 2016 at 20:52

Pedro · Accepted Answer · 2016-12-06 21:55:33Z

1

Looks like I can do the following:

$yards = Yard::orderBy('quarantined', 'DESC')
        ->orderBy('archived', 'ASC')
        ->orderBy('name', 'ASC')
        ->get();
$yards = $yards->sortByDesc('latestInspection.reinspect_at');

answered Dec 6, 2016 at 21:55

Pedro

1,1785 gold badges18 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pedro Over a year ago

Yes, this line: $yards = $yards->sortByDesc('latestInspection.reinspect_at'); Sorts it based on the relation date

Illia Yaremchuk · Accepted Answer · 2016-12-06 20:53:54Z

0

For example:

function latestInspection() {
   return $this->hasOne('App\Yard')->orderBy('reinspect_at', 'asc');
}

And

$yards = Yard::with('latestInspection')->orderBy('quarantined', 'DESC')->get();

answered Dec 6, 2016 at 20:53

Illia Yaremchuk

2,0232 gold badges22 silver badges38 bronze badges

1 Comment

Pedro Over a year ago

Hi, this way only orders the inspection for each yard, I want to order the yard list based on the reinspect_at date from the latest inspection. i.e yard 1 - 01-01-2016 yard 2 - 02-01-2016

user1669496 · Accepted Answer · 2016-12-06 21:11:36Z

0

You could use some of the methods of the returned collection. Generally you want to handle this all on the database end but I think this way is worth it because it greatly simplifies the process.

$yards = Yard::with(['inspections'])->get()->sortBy(function($yard) {
    return $yard->inspections->max('reinspect_at');
});

I removed your ordering by quarantine. Not sure how you want to handle that if you are also sorting by the reinspect_date.

answered Dec 6, 2016 at 21:11

user1669496

33.3k9 gold badges76 silver badges65 bronze badges

1 Comment

Pedro Over a year ago

Hi thanks for the reply, this doesn't seem to make a difference to the ordering of the list of yards.

Collectives™ on Stack Overflow

Laravel order data by relation property

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related