Laravel eloquent query with sum of related table

Question

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

Answer 1

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