How to query the parent according to the the sum of child fields?

I have two models Game and Round. A game has many rounds and a round belongs to a game. The structure is something like this.

{
  "game":{
    "title": "Game One",
    "description": "A game one",
    "rounds": [
      {
        "title": "Round 1",
        "points": 10
      },
     {
        "title": "Round 2",
        "points": 10
      },
      {
        "title": "Round 3",
        "points": 10
      }
    ]
  }
}

I would like to query the game according to the total points from all the round. For above example, the total points would be 30. I am using jenssegers/laravel-mongodb package and would like to know how can I achieve that? Thanks in advance.

sazvader

4 years ago

Level 1

For now. I've came up with this.

$games= Game::all();

 return $games->each(function ($g)  {
         $g['total_points'] = $g->rounds->sum('points');
  })->filter(function ($g) {
         return $g['total_points'] == 30;
  })->first();

It is not very clean or optimal. Is there anything that could be improved?

MichalOravec

4 years ago

Level 75

Use withSum for that

$games = Post::withSum('rounds as total_points', 'points')->get();

For that condition total_points == 30 you can use having

$games = Post::withSum('rounds as total_points', 'points')
    ->having('total_points', 30)
    ->get();

https://laravel.com/docs/9.x/eloquent-relationships#other-aggregate-functions

2 likes

sazvader

4 years ago

Level 1

@MichalOravec I've already tried using it. But, laravel-mongodb package throws errors if I use withSum or withCount.

kevinbui

4 years ago

Level 41

I think you can simply do this:

return $games->first(function ($game)  {
    return $game->rounds->sum('points') == 30;
});

// Or
return $games->first(function ($game)  {
    return $game->rounds->sum->points == 30;
});

Please or to participate in this conversation.