[Bug] Query Builder count() return wrong number with groupBy()

[wafgomes]

Using Query Builder count() with groupBy() doesn't return the correct number of rows, not considering the group by condition

I just made an example to show the problem

consider following table and code:

id	cat	desc
1	1	abc
2	1	abc
3	2	abc

$count = DB::table('product')
    ->where('cat', 1)
    ->groupBy('cat')
    ->count();

var_dump($count);

$count = DB::table('product')
    ->where('cat', 1)
    ->groupBy('cat')
    ->get();

var_dump(count($count));

Output:

int(2)

int(1)

[taylorotwell]

Perhaps send a pull request to this repository?

[wafgomes]

nah, I'll let this open for who knows how to do that.

I can't run unit tests

[ecsos]

something like this?
in Illuminate\Database\Query\Builder.php

    /**
     * Retrieve the "count" result of the query.
     *
     * @param  string  $columns
     * @return int
     */
    public function count($columns = '*')
    {
        if ( ! is_array($columns))
        {
            $columns = array($columns);
        }

        if (!empty($this->groups)) {
            return count($this->select($this->raw('count(*) AS AGGREGATE'))->get());
        } else {
            return (int) $this->aggregate(__FUNCTION__, $columns);
        }
    }

[ebrahimradi]

I have this problem too.
What should I do?

[GrahamCampbell]

This is already fixed in laravel 5.

[ebrahimradi]

what should I do for 4.2 ?
Thanks

[Sladewill]

Its not difficult to upgrade to 5/5.1.

[mzur]

@GrahamCampbell I have a similar Issue with v5.1.16:

public function points()
{
    return $this->hasMany('Point')->orderBy('index', 'asc')->groupBy('id');
}

var_dump($annotation->points()->count()); // int(1)
var_dump(count($annotation->points()->get())); // int(2)

So points()->count() returns 1 although there should be two elements. Grouping by the ID was a workaround because otherwise I got an SQL error SQLSTATE[42803]: Grouping error: 7 ERROR: column "points.index" must appear in the GROUP BY clause or be used in an aggregate function when trying things like points()->max('index').

Edit: Never mind... I executed the resulting SQL query manually and it returns the count for each group which is always 1 of course. Seems like I have to fix the problem with orderBy and max first.

[steve3d]

this is not fixed in 5.2.35

if I use $query->count() with a groupBy, this count will be the first item's count, not total count.

[enbaby]

@steve3d yeah,I have the same problem,when i use count method after groupBy method,I get 1,but actually it has 77.How to solve it.

[jbroudou]

This is still an issue with 5.5.16
I've had to get around this by running a separate query

SELECT FOUND_ROWS()