How to have the sum of withCount on the multiple relationships in laravel

Question

I have a basic three tables users and guests and posts

Also i have table named user_views: to store unique users views of posts

post_id user_id 1 10002 2 10003 3 10011

And another table named guest_views: to store unique guests views of posts

post_id guest_id 1 10002 2 10003 3 10011

In Post model i have:

public function user_views() { return $this->hasMany('App\Models\PostUserView'); } public function guest_views() { return $this->hasMany('App\Models\PostGuestView'); }

In the following code get the correct results but with two different keys.

$posts = Post::all() ->withCount('user_views') ->withCount('guest_views')

I thought of merge user_views and guest_views then compute count as follow but the result only includes count of user_views

public function views() { return $this->user_views()->unionAll($this->guest_views()); }

After you perform the following code

$posts = Post::all() ->withCount('views') ->withCount('user_views') ->withCount('guest_views')

I get this result

"views_count": 5, "user_views_count": 5, "guest_views_count": 2,

The expected result for prevues example is:

"views_count": 7, "user_views_count": 5, "guest_views_count": 2,

Also i try to use sql query as follow

public function views() { return DB::raw("(select * from post_views) union all (select * from post_guest_views)"); }

But get

"Call to undefined method Illuminate\Database\Query\Expression::getRelationExistenceCountQuery()"

So I'd like to get total number of views from all users and guest for each post.

I think I need to perform the following query: select posts.*, (select count(*) from user_views` where posts.id = user_views.post_id) + (select count(*) from guest_views where posts.id = guest_views.post_id) , .... ` — Adel SaadEddin
– Adel SaadEddin, Commented May 21, 2019 at 14:17

Boni · Accepted Answer · 2019-05-21 17:16:13Z

In your Post model

public function user_views() { return $this->hasMany('App\Models\PostUserView','user_id','id'); } public function guest_views() { return $this->hasMany('App\Models\PostGuestView','user_id','id'); } public function views() { return $this->user_views()->unionAll($this->guest_views()); }

Now you can access

$posts = Post::withCount(['user_views','guest_views','views'])->get()

Note

Every parameter that is specified in withCount() method, becomes main object’s _count property. So in this case, we will have $posts->guest_views_count,$posts->views_count and $posts->user_views_count variables.

Hope it will work for you.

I get this exception : "Call to a member function addEagerConstraints() on integer"
when you got the error and which line you got please give me the exact error ?
do you have the user_views and guest_views function above
please tell the unique key with user and user_views and guest_views

Lakhwinder Singh · Accepted Answer · 2019-05-21 13:56:00Z

you can get number of view counts using join(). Below is the example of code:-

$posts = Post::join('user_views as uv', function($q) { $q->on('uv.post_id', '=', 'posts.id'); }) ->join('guest_views as gv', function($q) { $q->on('gv.post_id', '=', 'posts.id'); }) ->selectRaw("posts.*, COUNT(uv.id) as user_views_count, COUNT(gv.id) guest_views_count, SUM(COUNT(uv.id) + COUNT(gv.id)) as views_count") ->groupBy("posts.id") ->get();

Try this.

I think the query is equivalent to the following : select posts.*, COUNT(uv.user_id) as user_views_count, COUNT(gv.user_id) as guest_views_count, COUNT(uv.user_id) + COUNT(gv.user_id) as views_count from posts` inner join user_views as uv on uv.post_id = posts.id inner join guest_views as gv on gv.post_id = posts.`id`` I think it needs a "group by" but also will not get me the right result

iMezied · Accepted Answer · 2020-05-13 13:17:21Z

You can use the collection method reduce as below:

$posts->reduce(function($carry,$item){ return $carry+$item->user_views_count; },0);

for more please check the reduce documentation: https://laravel.com/docs/6.x/collections#method-reduce

Collectives™ on Stack Overflow

How to have the sum of withCount on the multiple relationships in laravel

3 Answers 3

14 Comments

1 Comment

Comments

Linked

Hot Network Questions