Laravel Eloquent sort by related field

I have the following variable I am sending to a view:

$locationtypes = LocationType::with('locations')->get();

A LocationType hasMany Location. A Location belongsTo LocationType. Both have a name field.

In my view, I need the data sorted by the LocationType name field, and then by the Location name field, so when I do this:

@foreach($locationtypes as $locationtype)
    <h1>{!! $locationtype->name !!}</h1>
    <ul>
    @foreach($locationtype->locations as $location)
        <li>$location->name</li>
    @endforeach
    </ul>
@endforeach

the headings will be sorted, and the list items under each heading will be sorted.

I've searched many forums, and I have only seen seemingly complicated solutions that I have been unable to correctly implement. I feel that this MUST be a common requirement which has a simple, elegant solution.

Thank you for your help.

bobbybouwmann

10 years ago

Best Answer

Level 88

I think that this would work for you

$locationtypes = LocationType::with(['locations' => function($query) {
    $query->orderBy('name');
}])->orderBy('name')->get();

If this is not what you want take a look at the sort function for collections: http://laravel.com/docs/5.1/collections#method-sortby

3 likes

skihansen

10 years ago

Level 3

@bobbybouwmann - that's exactly what I needed. Thanks!

If anyone can point me to a Laracast video or external resource that could help me understand what is going on in the solution provided by @bobbybouwmann that would be great. Specifically, I am lacking PHP and/or Laravel knowledge to understand what is going on inside the with() part of the query.

bobbybouwmann

10 years ago

Level 88

Well actually when you use with Laravel will create two queries for you

select * from location_type order by name
select * from locations order by name where location_type_id in (10, 11, 23, 45) order by name

The where location_type_id in (<list of ids>) is the part where all the id's of the parent relation will be added. So in this case it's all the items, but if you would have added a where statement you would have only a selection of the ids.

This serie might help as well: https://laracasts.com/series/advanced-eloquent

murph133

7 years ago

Level 1

The ordering methods mentioned above did not work for me using Laravel 5.6

However, I found a solution to orderBy a relation model using counts.

I wanted to order my business listings by their membership_level:

Premium Plus
Premium

$businesses = Business::withCount('subscription_premium_plus')->withCount('subscription_premium_plus')->orderBy('subscription_premium_plus_count', 'desc')->orderBy('subscription_premium_count', 'desc')->get();

Then I had two methods in my Business model:

public function subscriptionPremiumPlus()
{
    return $this->hasOne('App\Subscription')->where('end_date', '>', Carbon::now())->where('membership_level', 2);
}

public function subscriptionPremium()
{
    return $this->hasOne('App\Subscription')->where('end_date', '>', Carbon::now())->where('membership_level', 1);
}

My personal code actually included another relationship, but I have simplified things so you get the basic idea of what I did. Using the custom relationship methods also allowed me to add conditionals, such as checking whether the subscription was still valid.

I found this solution much more preferable because it meant I didn't have to right raw mysql code and could continue using Eloquent, which is very pleasant to use.

Please or to participate in this conversation.