Level 26
@pb1uk you want to use having which is applied after the group by.
Review::groupBy('user_id')->havingRaw('COUNT(*) > 10')->get();
The equivalent of this
select * from `reviews` group by `user_id` having COUNT(*) > 10
1 like
Sep 22, 2015
5
Level 1
Using count in a where clause
Hello,
I'm in the process of rebuilding my website in laravel and i've come across a query that i'm struggling to recreate. Below is the query that I have currently, i'm doing a count and grouping by a user id and i only want to select ones that have a count higher than 10 - is it possible to recreate this in eloquent or would I have to go down the raw option?
SELECT * FROM ( SELECT count(*) as review_count FROM reviews GROUP BY user_id ) as vtable WHERE review_count > 10
Level 88
First all this seems to be a really weird query to me.. In inner select for just the table? This is the same thing
SELECT *, COUNT(*) AS review_count FROM reviews GROUP BY user_id HAVING review_count > 10
I think you can get a way with doing something like this
DB::table('reviews')
->select('*', DB::raw('COUNT(*) as review_count'))
->groupBy('user_id')
->having('review_count', '>' , 10)
->get();
2 likes
Level 26
@bobbybouwmann Yeah its a lil weird, I would also do this @pb1uk to extract and make it flexible. I've called it topReviewers as a guess.
Review::topReviewers()->get();
public function scopeTopReviewers($query, $threshold = 10)
{
return $query->groupBy('user_id')->havingRaw('COUNT(*) > '. $threshold);
}
3 likes
Level 1
Thanks for your help guys, in all honesty i hadn't come across having before, is just what i needed.
Level 9
Could a query scope on the model handle this?
Please or to participate in this conversation.