CODEWITHSUNDEEP
phpmysqlsqllaravelgroup-by
Anonymous Girl
Anonymous Girl

Reputation: 642

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

$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.

Upvotes: 2

Views: 1155

Answers (3)

milind prajapati
milind prajapati

Reputation: 86

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.

Upvotes: 3

weiwait
weiwait

Reputation: 1

using the ->simplePaginate()

Upvotes: -1

Honk der Hase
Honk der Hase

Reputation: 2488

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-

Upvotes: 3

Related Questions