How to get records count greater than x (using MySQL Query) in Laravel

user_9365633 0
Viewed 565
$lists = DB::table('searches')->orderBy('query', 'DESC')->paginate(15);

@foreach ($lists as list)
  <div> {{$list->name}} </div>
@endforeach

I did write this query and It shows:

Apple Apple Apple Apple Apple Apple Banana Banana Banana Grapes Grapes Orange ....

I want to show only the names of the fruits having an overall count of occurences greater than 3 (if count>3) I used:

DB::select('SELECT query, count(*) as count FROM searches GROUP BY query HAVING count > 3');

but in view file I cannot count by $lists->count()

Error: Call to a member function count() on array

I want my output as: Apple Banana ... and $lists->count() as 2

Since Orange and Grapes count < 3 so they won't be included in the output.

laravel group-by mysql sql php
3 Answers

I am not sure to understand what exactly you want, but I guess you'd want to use a GROUP BY and HAVING

SELECT name, count(*)
FROM fruits
WHERE ...
GROUP BY name
HAVING count(*) > 3

This query will count the number of records of the same name, but only if the count is greater than 3-

user_24432260

You can get records by using selectRaw and havingRaw

DB::table('searches')->selectRaw('query,count(*) as count')->groupBy('query')->havingRaw('count(*) > 3')->orderBy('query', 'DESC')->paginate(15);

This query will give output 15 records (as defined in paginate(15)) when the value count is GREATER than 3.

user_77470950

using the ->simplePaginate()

user_164934090
Related