Eloquent - belongsTo orderBy

I have 2 tables:

|       LOCATIONS              |
|------------------------------|
| id | region_id | name        |
|----|-----------|-------------|
| 1  | 1         | Los Angeles |
| 2  | 1         | San Diego   |
| 3  | 2         | Phoenix     |
| 4  | 2         | Yuma        |


|      REGIONS    |
|-----------------|
| id | name       |
|----|------------|
| 1  | California |
| 2  | Arizona    |

I want to query locations, sorting the LOCATIONS by their associated REGION. So the result should be

Phoenix (Arizona)
Yuma (Arizona)
Los Angeles (California)
San Diego (California)

So my LOCATION model has this function:

public function region() {
        return $this->belongsTo('App\Region', 'region_id', 'id');
    }

Itried something like this, but this does not seem to work:

$locations = App\Location::with(['region' => function ($query) {
    $query->orderBy('name');
}])->get();

Snapey

8 years ago

Level 122

you will need to use a join and then sort on the region as if the region was part of the locations table

MiguelBarros

8 years ago

Level 44

You can do that with sortBy instead of orderBy i think

Snapey

8 years ago

Best Answer

Level 122

this

$locations = App\Location::with(['region' => function ($query) {
    $query->orderBy('name');
}])->get();

only sorts the relations not the parent

using a join, you have to account for the fact that you have two 'name' columns;

Location::join('REGIONS','REGIONS.id','=','LOCATIONS.region_id')
    ->select('REGIONS.name as regionName','LOCATIONS.*')
    ->orderBy('regionName','ASC')
    ->get();

Correct if your table names are not really UPPERCASE

2 likes

Please or to participate in this conversation.