Query Builder count() return wrong number with groupBy()

[enbaby]

sorry, my mistake. solved

My Laravel version info:
Laravel Framework version 5.1.40 (LTS)

See this issue
#4306

In my test,it's still has problem.

dd(Member::groupBy('id')->count());

I only get result 1.

but in this method

dd(Member::count());

I get right num(more than 1)

(the id is primary key, so i should get same result).

And both has problem use sum method after groupBy method,it seems only get the first query result.

dd(Member::groupBy('id')->sum('xxxx'));

IS ANYONE HAS THIS PROBLEM?

[davzie]

I'm not sure why this was closed. I haven't been able to find a workaround to this that uses Postgres' built in count methods. This is really frustrating because it rules out being able to use Laravel on Heroku at the moment for me :( HELP US @taylorotwell, you're our only hope!

[davzie]

I'm using the latest Laravel 5.3 by the way. Here's a simple example:

    // THIS IS THE DIRTY WORKAROUND
    $userPostCount = DB::table('posts')
                                ->whereNotNull('user_id')
                                ->groupBy('user_id')
                                ->groupBy('id')
                                ->select('user_id')
                                ->get()->count();

    // THIS IS WHAT I WANT BUT THAT ALWAYS RETURNS 1
    $userPostCount = DB::table('posts')
                                ->whereNotNull('user_id')
                                ->groupBy('user_id')
                                ->groupBy('id')
                                ->select('user_id')
                                ->count();

[tomlankhorst]

The issue here is that Laravel internally uses the SQL COUNT() aggregate function. An aggregate function applies to each group. If you don't specify GROUP BY, that means that COUNT() applies to the whole resultset, yielding the number of rows that are selected.

SELECT COUNT(*) FROM bakeries
The query result of which will be [N].

However, when specifying a GROUP BY, the COUNT() is applied to each group, yielding the number of elements in each group.

SELECT COUNT(*) FROM bakeries GROUP BY city_id
The query result will be: [N1, N2, ... Nm], for m groups where Ni is the number of bakeries in the group.

Then, Laravel's count() method adds the SQL COUNT() aggregate to your builder and just takes the first resulting element and returns that. That means that we're not counting the number of groups (i.e., the number of records that get() will yield) but the number of 'bakeries' in the first group.

Instead, when using Bakery::groupBy('city_id')->count(), one expects to retrieve the number of groups. Typically this is solved using either window functions or subqueries or translating the GROUP BY to a COUNT(DISTINCT ...).

The last is something we can use to fix this issue:

Model::groupBy('some_column')->foo()->count();
// replace groupBy()->count()
Model::distinct()->foo()->count('some_column');

[davzie]

@tomlankhorst If I had a cache of smart trophies, I would hand you one for such an eloquently put answer to why Laravel works the way it does here. <3