Order by based on field created by relationship

Question

I'm trying to orderBy() data from db based on field created by relationship with() without success. I'm using laravel/php and mongodb.

Here's my model relationship :

public function game(): BelongsTo
{
    return $this->belongsTo(Game::class, 'game_id');
}

Here's my data structure :

{
    "_id":"25673",
    "game_id":"4",
    "user_id":"22",
    "score":50,
    "created_at":"2021-09-11T17:04:32.473000Z",
    "user" : {
        "_id":"22",
        "email":"[email protected]"
    },
    "game": {
        "_id": "4",
        "name":"Snake" //==>orderBy this field 
    }
},

And here's my request :

 $gamesPlayedRequest = GamesPlayed::select('created_at', 'user_id', 'game_id', 'score')
        ->with('game:id,name')
        ->orderBy('game.name', 'DESC'); //==>the field 'game.name' is not reachable

So I would like to order my data by the name of the game.

I don't get any error, but the data are not sorted properly or effectively. For a little bit of context : the data are displayed in a table, and when the user click on the header of the column "Game" he-she can re-order the games by there name (alphabetically or reverse).

I can I sort my data with the name of the game (field created by the relationship) in php and with mongodb ?

Thank you,

Answer 1

You can order the result collection directly like this :

$gamesPlayedRequest = GamesPlayed::select('created_at', 'user_id', 'game_id', 'score') 
    ->with('game:id,name') 
    ->orderBy('game.name', 'DESC') 
    ->get() 
    ->sortByDesc('game.name');