count() method does not return actual number of records when using groupBy() statement.

I have 10 unique records by user_id in my children table. I want to get count of users which has at-least one child so I put group by statement on user_id. below is my code.

    $count = \App\Children::whereIn('status', ['A','I'])
    ->groupBy('user_id')
    ->count();

With this it return me 1 instead of 10.

I suspect this might be bug in the Eloquent ORM. I also read in laravel docs that Pagination does not work efficiently on groupBy statement.

What are your thoughts

Thanks.

crnkovic

8 years ago

Level 43

Dump the SQL query in here, so we can debug into DBMS, I believe the SQL works like that: https://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php

mrbadr

8 years ago

Best Answer

Level 1

Hello you can use count() from collection by getting the results befor count like this:

$count = \App\Children::whereIn('status', ['A','I'])
    ->groupBy('user_id')
    ->get()
    ->count();

try this and tell me

COACHTHEM

8 years ago

Level 2

Hi @mrbadr , you are right. Frst need to make the collection using get() and call count() method. With this it return 10 for me. thanks.

1 like

Please or to participate in this conversation.