[Eloquent] Order by on an eager loaded relationship's foreign key / relationship.

Adapted from the docs [ http://laravel.com/docs/5.0/eloquent#eager-loading ].

$user->load(['cars' => function($query)
{
    $query->orderBy('colour_id', 'asc');
}]);

If you want to order by colour name, do this:

$user->load(['cars' => function($query)
{
    $query->select('cars.*')->join('colours', 'cars.colour_id', '=', 'colours.id')->orderBy('colours.colour', 'asc');
}]);

@rodrigo.pedra hmm, maybe I've found a bug then?

I did actually try something along the lines of your second example but then when you continue to eager load in nested resources, it does it based on the joined table's ids instead of the proper table.

Example:

Let's assume for the example that you also want to eager load the table parts (assume for this example that each part belongs to 1 car and each car can have many parts)

The query would look like this, right?

$user->load(['cars' => function($query)
{
    $query->join('colours', 'cars.colour_id', '=', 'colours.id')->orderBy('colours.name');
}, 'cars.parts']);

The resulting query for parts would use the car_id foreign key on the parts table, but in the WHERE IN clause it would provide the colour ids rather than the cars ids. Weird?

Can anyone else replicate this behaviour or did I do something wrong?

@ryanmortier No bug there, you need to select proper columns. Anyway, there's no way to order by relation in eager loading - you need joins for this.

@JarekTkaczyk oh wow, ok if I add in the select clause, everything works now. Why does leaving that out (so that it just uses *) break it?

@ryanmortier Because you're getting 2 id columns, of which colours.id is taken (because it's simply last one - joined, so it overrides the value bc it's an array key). You must be aware that Eloquent doesn't use joins for loading relations by default, so it's your job to take care of such things.

The eager-loading works like this, when you this:

$user->load(['cars' => function($query)
{
    // I'm putting the ->select() back here from my previous post
    $query->select('cars.*')->join('colours', 'cars.colour_id', '=', 'colours.id')->orderBy('colours.name');
}, 'cars.parts']);

Laravel executes this:

SELECT * FROM users;

-- Here Laravel replaces the IN with the id's returned by the first query
SELECT cars.* FROM cars INNER JOIN colours ON cars.colour_id = colours.id WHERE user_id IN (...) ORDER BY colours.name;

-- Here Laravel replaces the IN with the id's returned by the second query
SELECT * FROM parts WHERE part_id IN ( ... );

So you see that:

1. Users will note be ordered
2. Cars will be ordered by its colour's name, but colours model will not be loaded
3. Parts will not be ordered

The key here is to understand that when eager-loading Laravel executes a different query for each relationship. If you want a parent model to be ordered by a child model attribute, as @JarekTkaczyk said, you'll need to use ->join() and ->orderBy() together.

I understand what it's doing now. thank you, both of you.

take a look on this package https://github.com/fico7489/laravel-eloquent-join