Laravel : How to using group By with sum and select multi field

Question

In my Laravel project ,I have users table contain id and username ...etc. another table called predictors ,both of them related in user_id (one to many),now I want to create array contain username from users table and sum of point field in predictors table. I tried many thing but don't work like this :

public function leader()
    {
    $predects = Predector::selectRaw(' user_id ,sum(point) as points')->groupBy('user_id')->get();
    foreach($predects as $predect){
        $predect->User->username;
    }
    return $predects;
    }

but it gave me this result

{
    "user_id": 1,
    "points": "3",
    "user": {
        "id": 1,
        "username": "sarmed",
        "nat_id": "123456",
        "email_verified_at": null,
        "created_at": "2020-11-30T17:10:27.000000Z",
        "updated_at": "2020-11-30T17:10:27.000000Z"
    }
},

and I want something like this :

 {
        "user_id": 1,
        "points": "3",
        "username": "sarmed",
    },

Check this post https://stackoverflow.com/questions/21679678/laravel-eloquent-sum-of-relations-column — Giacomo M, Dec 01 '20 at 21:39

Donkarnash · Answer 1 · 2020-12-01T22:38:33.817

So if I understand correctly, goal is to get a sum of all points for a user and then provide a dataset of User's id, points and username

Assuming the relation on User model as

class User extends Model
{
    public function predects()
    {
        return $this->hasMany(Predector::class);
    }
}

Try the following snippet to achieve the desired output

public function leader()
{
    return User::query()
        ->with('predects:id,user_id,point')
        ->select('id', 'username')
        ->get()
        ->map(function($user){
            $predects = $user->predects;
            unset($user->predects);
            $user->points = $predects->sum('point');
            return $user;
        })
        ->sortByDesc('points')
        ->toArray();
}

For Laravel versions below 8.x have a look at Laravel Subquery package https://github.com/Alexmg86/laravel-sub-query to simplify things a bit more

score 0 · Answer 2 · answered Dec 02 '20 at 17:22

I solved it by adding this line :

unset($predect->User);

now the correct code :

public function leader()
    {
        
        $predects = Predector::selectRaw(' user_id ,sum(point) as points')->groupBy('user_id')->orderBy('points','desc')->get();
        foreach($predects as $predect){
            $predect->username=$predect->User->username;
            unset($predect->User);
        }
        return $predects;
    }

and the result is :

[
    {
        "user_id": 2,
        "points": "7",
        "username": "sermedov"
    },
    {
        "user_id": 1,
        "points": "3",
        "username": "sarmed"
    }
]

Laravel : How to using group By with sum and select multi field

2 Answers2