internal orderBy gets overwritten by external orderBy in Laravel Eloquent

Question

I have a reliationship User hasOne Position and I'm fetching the users, but I want to sort them first by Position->name and then by User->name. I tried the following

<?php
$sorted = Position::where('groupId', $this->groupId)
    ->whereIn('id', $positions)
    ->with(['user' => function($query) {
        $query->orderBy('user.name'); // internal sort
    }])
    ->orderBy('position.name') // external sort
    ->get();

This way the results are sorted by the external sort only, or, by Position->name. Different users with the same Position->name are listed unsorted. If I remove the external sort, and leave only the sortBy User->name, it works, BUT only for the names, while positions are random.

I have tried different ways

setting the order in the Position->user relationship, does not work
setting the order in the User->position relationship, does not work
defining only an external orderBy('position.name, user.name'), crashes, saying user table is not in the query.

I also tried following similar questions like

but they don't seem to be trying to sort the results both by the parent and the relationship. It seems my only solution is to walk the result and sort them in PHP instead of from the DB, but this sounds dumb.

Please advice, thank you.

@FrankB Uncaught BadMethodCallException: Call to undefined method Illuminate\Database\Eloquent\Builder::addOrderBy() Looks like that's the Doctrine way. — StR

Tim Lewis Tim Lewis · Accepted Answer · 2020-04-19T16:32:48

When you want to sort the parent Position by the relationship User, you need to use a join():

$sorted = Position::where(...)
->whereIn(...)
->with('user')
->join('users', 'users.id', '=', 'positions.user_id') // Or whatever the join logic is
->orderBy('users.name')
->orderBy('positions.name')
->get();

Note: The orderBy() on the user relationship within with() doesn't seem necessary, as by convention, a singular-named relationship should only return 1 record, and sorting on a single record is pointless.

This will return a Collection of Position models, with an attached User model, sorted by the User's name, then the Position's name. You might need to add a select('positions.*') to avoid any ambiguity issues, but this should give you the general idea.

internal orderBy gets overwritten by external orderBy in Laravel Eloquent

1 Answers