Laravel Group and Sum hasMany Relationship

Question

I have a users table with a column country and the user belongsToMany Categories. The user also hasMany relationship with a Payments table.

I am trying to:

Group the users by country showing the most popular category for each country (this is solved already)
Sum the amount columns in the payments table for each grouped country

Every time I try to join the payments table it returns multiple results which makes the sum entirely wrong. I believe I am not using the right query.

Here are the table structures:

Users

id | name  | country

1  | UserA | Canada
2  | UserB | USA
3  | UserC | Canada

Categories

id | Name
1  | Housing
2  | Cooking

category_user

id  | category_id | user_id
1   | 1           | 1
2   | 2           | 2
3   | 1           | 3

Payments

id | amount | user_id
1  | 500    | 1
2  | 200    | 2
3  | 150    | 1
4  | 100    | 3

The first problem has been solved using the code below:

return User::leftJoin('category_user as cu', 'users.id', '=', 'cu.user_id')
        ->join('categories as c', 'cu.category_id', '=', 'c.id')
        ->groupBy(['country', 'c.name'])
        ->get([
            'users.country',
            'c.name',
            DB::raw('count(*) as total')
        ])
        ->groupBy('country')
        ->values()
        ->map(function (Collection $elt) {
            return $elt->sortByDesc('total')->first();
        });

which results in something like this:

[
  {
    "country": "Canada",
    "name": "Housing",
    "total": 2
  },
  {
    "country": "USA",
    "name": "Cooking",
    "total": 1
  }
]

I would like to get the SUM(amount) from payments as total_payments. Any help would be appreciated.

Thanks.

user_2188922 · Answer

I couldn't find a way to minimize number of queries but following works on my local tests.

public function foobar()
{
    $popular = User::leftJoin('category_user as cu', 'users.id', '=', 'cu.user_id')
        ->join('categories as c', 'cu.category_id', '=', 'c.id')
        ->groupBy(['country', 'c.name'])
        ->get([
            'users.country',
            'c.name',
            DB::raw('count(*) as total')
        ])
        ->groupBy('country')
        ->values()
        ->map(function (Collection $elt) {
            return $elt->sortByDesc('total')->first();
        }); // this one is same (to get popular category)

    // this one for payments and number of users from that country
    return User::leftJoin('payments as p', 'users.id', '=', 'p.user_id')
        ->groupBy('country')
        ->get(['country', DB::raw('sum(p.amount) as total'), DB::raw('count(distinct(users.id)) as userCount')])
        ->map(function ($col) use ($popular) { // merging two collections into one
            $col->name = $popular->where('country', $col->country)->first()->name;

            return $col;
        });
}

It prints something like this;

[
  {
    "country": "Canada",
    "total": "1150",
    "userCount": 2,
    "name": "Housing"
  },
  {
    "country": "Italy",
    "total": "100",
    "userCount": 1,
    "name": "Kitchen"
  },
  {
    "country": "USA",
    "total": "200",
    "userCount": 1,
    "name": "Cooking"
  }
]

Laravel Group and Sum hasMany Relationship

1 Answers