framework: eloquent count() does not work well with groupBy()

I’ve had the same issue and I’ve just solved it using getCountForPagination().

So I did: $queryWithGroupBy->getQuery()->getCountForPagination();

The issue with $queryWithGroupBy->get()->count() is that the query gets executed before counting the results, which is useless since you could just do count($queryWithGroupBy->get()).

But if you want to get the number of rows before doing any queries (in my case I needed this number to calculate the query progress time), this is might be the one of the best ways.

@36864 you are 100% but I already know what is happening behind scene, that second count() call adds “count(*) as aggregate_count” to select(), and first count() performs count() on collection, but that is not the question here, only question is if this behaviour is normal :

//result here is 10
Seller::select('sellers.* ')
    ->join('locations', 'locations.seller_id', '=', 'sellers.id')
    ->groupBy('sellers.id')
    ->get()
    ->count();

//result here is 1
Seller::select('sellers.* ')
    ->join('locations', 'locations.seller_id', '=', 'sellers.id')
    ->groupBy('sellers.id')
    ->count()

?

I don’t think so, and I think that 99% people which use above eloquent example expect 10 in second example.

One way to do it is to use DB::raw() expression as per this answer.

$sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
    ->count();

It’s cleaner than remove groups and can be done in one line.

I expect a grouped count to return the count of each group. That’s literally what you’re asking the builder to do.

I would not expect the behavior of Collection::count() to be the same as that of Builder::count() in this context, much like I don’t expect Collection::groupBy() to behave like Builder::groupBy(), and I don’t expect the result of calling Model::groupBy()->get()->count() to have the same result as Mode::all()->groupBy()->count().

While these methods have the same name, they behave very differently, and their behavior is well documented and has been in use for a long time. Changing it now would just cause confusion.

How would I get a grouped count if the method was changed as you’re suggesting? Would I need to add a raw select to my query? That’s inconvenient at best, not to mention a huge breaking change.

No, no no. This question is not about finding appropriate query for that what I want. I want exact this query, and this query works perfectly for me.

select sellers.* from sellers
        left join locations
        on locations.seller_id = sellers.id
        group by sellers.id

Above query is easily generated by laravel eloquent functions and get() at the end

But running count() on the same builder gives me wrong result.

$builder->get()->count() //result here is 10 $builder->count() //result here is 1

How this can be normal ?

There are 2 solutions :

1.eloquent should forbid running count() when groupBy() is applied because the result is wrong 2.or eloquent can change count() function as I described in my first post.

//result here is 10
Seller::select('sellers.* ')
    ->join('locations', 'locations.seller_id', '=', 'sellers.id')
    ->groupBy('sellers.id')
    ->get()
    ->count();

//result here is 1
Seller::select('sellers.* ')
    ->join('locations', 'locations.seller_id', '=', 'sellers.id')
    ->groupBy('sellers.id')
    ->count()

Do you understand now what is the problem ?

PS. If you are confused, I am joining locations because I want sort by locations. address, but that is really not important here…

@36864 ok, then we are not expecting the same thing.

1.I am an expection same value for count() on builder and on collection 2.I am expection exact count() value for builder so I can calculate pagination values(hasMorePages, nextPage, totalPages etc.).

I would be happy with answer “yes, you are right, but this is breaking change we can only change this in laravel 6. x”

@Dylan-DPC I don’t feel this is subjective, eloquent allows using groupBy() with count() but then result is not correct and this is the fact, no subjective…

Is there not a method to remove groupBy()s?

Right now, I do this:

$ids = $complexQuery->join()->join()->join()->where()->where()->where()
    ->select('id')->pluck('id');

$limitedQuery->whereIn('id, $ids);

The problem is when one of the methods that builds the complex query’s joins and wheres also needs a groupBy. Then, $complexQuery->count() returns 1, while the number of rows returned by ->pluck()` can be many more.

To get past this unfortunate bug that seems to be in a WONT_FIX state, I use the following hack:

        $totalPeople = $peopleQuery->count();

        // Hack to get the real count if a groupBy had to be used in a filter.
        // See the official Laravel bug report for more info:
        // https://github.com/laravel/framework/issues/22883
        if ($totalPeople === 1) {
            $totalPeople = $peopleQuery->pluck('people.id')->count();
        }

I truly hope that this really helps someone else out.

@timmcleod Thank you for showing me that ->get()->count() works. My method saves a lot of memory, as it only deals with primary keys and no mass object creation.

I’ve encountered this on a few different occasions and it gets me every time (including today). Just noticed that the paginator uses a workaround for this problem @fico7489.

When paginating (is that a word? ha), builder uses $this->toBase()->getCountForPagination() to find the total number of records instead of just querying with ->count(). That method checks to see if the query is grouping, and if so, just counts the results using count($results).

/**
 * Get the count of the total records for the paginator.
 *
 * @param  array  $columns
 * @return int
 */
public function getCountForPagination($columns = ['*'])
{
    $results = $this->runPaginationCountQuery($columns);

    // Once we have run the pagination count query, we will get the resulting count and
    // take into account what type of query it was. When there is a group by we will
    // just return the count of the entire results set since that will be correct.
    if (isset($this->groups)) {
        return count($results);
    } elseif (! isset($results[0])) {
        return 0;
    } elseif (is_object($results[0])) {
        return (int) $results[0]->aggregate;
    }

    return (int) array_change_key_case((array) $results[0])['aggregate'];
}

So, slightly annoying, but looks like we’re stuck with ->get()->count().

I feel this is subjective. If I have to do a groupby-count in SQL i would write the same query that eloquent is generating.

I agree it’s not working good with joins and groupBy. I was also facing this problem when building pagination. I just applied all filters in query and then used ->get()->count()

$data = QUERY->get(); $dataCount = $data->count(); $data = $data->splice(OFFSET, LIMIT);

aggregate queries + group by return the aggregate for each group. eg how many locations the first seller has, then how many the second seller has etc etc

your example for what you think it “should” generate isnt even a valid query in default installs of more recent versions of mysql

what it looks like you are trying to do is get the number of sellers, which should be done as select count(distinct sellers.id) from sellers (or even just select count(*) from sellers as the id should be unique anyway). also as far as i know that left join in your query is useless, you arent using any of the fields from the joined table, and as it is left join its not going to effect the count