Laravel HasMany: Get Parent with Total Sum of Children Column and Order By It

If you have a hasMany relationship and want to get the parent record with a total sum of specific children column values and order by it, here's how to do it.

Imagine you have this DB structure.

players

• id
• name

games

• id
• player_id
• score

And the relationship is this:

app/Models/Player.php:

public function games(): HasMany
{
    return $this->hasMany(Game::class);
}

Then, if you want to get the players with their total scores from all games, you can do this:

$players = Player::withSum('games', 'score')->get();

Then the result will be something like this:

Illuminate\Database\Eloquent\Collection {#1937
    all: [
      App\Models\Player {#2044
        id: 1,
        name: "Player 1",
        created_at: "2023-07-11 07:41:44",
        updated_at: "2023-07-11 07:41:44",
        games_sum_score: "17",
      },
      App\Models\Player {#1234
        id: 2,
        name: "Player 2",
        created_at: "2023-07-11 07:41:44",
        updated_at: "2023-07-11 07:41:44",
        games_sum_score: "13",
      },
    ],
  }

As you can see, the child record total column is returned as [table]sum[column]

Want to order by total score and get the top 5 players? No problem.

Player::withSum('games', 'score')
    ->orderBy('games_sum_score', 'desc')
    ->limit(5)
    ->get();

Here's the SQL query that would be executed under the hood:

select
  `players`.*,
  (
    select
      sum(`games`.`score`)
    from
      `games`
    where
      `players`.`id` = `games`.`player_id`
  ) as `games_sum_score`
from
  `players`
order by
  `games_sum_score` desc
limit
  5

As you can see, this calculation also increases the performance, compared to the alternative that you would launch a separate loop to calculate the sum.

The withSum() executes one SQL query and returns only the sum column without needing more SQL queries or columns from the games table.

Laravel also offers similar methods for aggregating data:

• withCount()
• withMax()
• withMin()
• withAggregate()

You can read about them in the official documentation.