CODEWITHSUNDEEP
laraveleloquent
Klick
Klick

Reputation: 1653

Laravel eloquent query with sum of related table

I have a table users and posts with columns user_id and post_views. In post_views I keep information how many times post was display.

And now, in query I would like to get user with sum of post_views all his posts.

I tried do something like this:

User::where(['id'=>$id])->with('posts')->get();

And in model I defined:

public function posts()
    {
        return $this->hasMany('App\Models\Post')->sum('post_views','AS','totalViews');
    }

But without success.

How to do it?

Thank you

Upvotes: 4

Views: 9771

Answers (3)

mchev
mchev

Reputation: 725

As always laravel has a method for that : withSum (Since Laravel v8)

Note : I know that at the time of the message was posted, the method did not exist, but since I came across this page when I was looking for the same result, I though it might be interesting to share.

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

In your case it should be :

$user = User::withSum('posts as total_views', 'post_views')->find($id);

Then you can access to the result :

$user->total_views

Upvotes: 0

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

You can use a modified withCount():

public function posts()
{
    return $this->hasMany('App\Models\Post');
}

$user = User::withCount(['posts as post_views' => function($query) {
    $query->select(DB::raw('sum(post_views)'));
}])->find($id);
// $user->post_views

Upvotes: 22

Frnak
Frnak

Reputation: 6792

You can use

User::withCount('posts')->find($id)

to get the user with the id $id and a posts_count attribute in the response

I'm not fully sure what the intention of ->sum('game_plays','AS','totalVies'); is - you would need to add more context if you want this

Just something to add with regards to your shown code: No need to query by id using where + the get() at the end will make you query for a collection. If you want to get a single result use find when searching by id

Upvotes: 0

Related Questions