CODEWITHSUNDEEP
laravelcountgroup-byeloquenthaving
Василий Бурлака
Василий Бурлака

Reputation: 3

Laravel Eloquent. How to count rows on query with group_by and having

In order to get clients, that have more than 2 payments, I have written this code:

Clients->select(....)
    ->leftJoin('payments')
    ->selectRaw('count(payments.client_id) as num_payments')
    ->groupBy($this->primaryKey)
    ->having('num_payments', '>', 2)
->get()

and it works fine. Generating something like that:

select `client_id`, ..., count(payments.client_id) as num_payments
from `clients` left join `payments` on ... 
group by `id_client`
having `num_payments` > 2

Now I need to get the quantity of clients without getting all info about them.

Eloquent method "->count()" always returns 1 when I use groupBy. Because it's generate wrong sql:

select count(*) from `clients` left join `payments` on ... group by `id_client`

Explain pls, how can I get in Laravel Eloquent something like 

select count(*) from
  (select `client_id`, ..., count(payments.client_id) as num_payments
  from `clients` left join `payments` on ... 
  group by `id_client`) as new_table

Upvotes: 0

Views: 1105

Answers (1)

Joel Hinz
Joel Hinz

Reputation: 25384

If you don't need all of the info, I would suggest not using explicit joins. Instead, assuming your relations are correctly setup, you can use Eloquent to say "count the number of clients that have more than two payments", like this:

Client::has('payments', '>', 2)->count();

Simple, huh? :)

Upvotes: 1

Related Questions