Improve the behaviour of query builder's count() and other aggregates

[tomlankhorst]

There are some issues with ->count()ing queries with groups.
The current implementation executes the relevant aggregate function (SUM, MIN, MAX...) on the query and returns the first result. See this issue, other relevant issues: #4306 #14123.

This is typically what we want when not using GROUP BY, but when we are, the result of e.g. count() - just picking the first value - makes little sense.

SELECT count(*) FROM `jobs` GROUP BY `type`

Will return something along the lines of [N_1, N_2, ..., N_M] for M groups.

The framework will just answer: N_1

First of all, we should determine, what should count(), min(), max() and other aggregate functions do? I feel like there are two major options.

A. We're trying to make a function of the query builder behave comparable to an equally named function of a collection. Such that:

assert($query->count() === $query()->get()->count());

If this is the case, we should take care that count() works for groupBy(...) queries as well.

B. The alternative is that we want aggregate functions to execute a query and return the result of the aggregate function. This result might consist of N records, where N is typically 1 or another number when a GROUP BY is used. The current implementation, however, only returns the first result of the query. This makes little sense when N ≠ 1.
Furthermore, the downside here is that we'll only retrieve the aggregate function result without any other column so there's not much to interpret here.

So, what do we want to achieve here?

[willrowe]

First of all, we should determine, what should count(), min(), max() and other aggregate functions do? I feel like there are two major options.

I completely agree that this should be clearly decided on and then either updated accordingly or documented. It is currently confusing, causing the issue to come up over and over again. See 1892, 2761, 3105, 4306, 6985, 7372, 9567, 10632, 14123, 16320, 17406, 22883, 28931.

I lean towards A., since the current implementation only uses the first returned row, inferring that the count method is supposed to return a total row count of what would be returned if the query was run without the count aggregate applied. In order to accomplish this, the query would need to be wrapped into a subquery and the count aggregate could then be applied in a similar fashion.

To me B. does not make much sense, once again, since the current implementation only uses the first returned row. In addition, if you truly wanted to get the count of each group, you would most likely add the count aggregate yourself to the select and not by calling count.
Calling count immediately runs the query and returns the result which, again, very strongly implies that a count of the rows that would be returned by the query in its current state is what should be returned.

A quick note, Taylor references possible performance issues in 10632 and 6985 if using a subquery to do counts, so it may be worth looking into specifics on that and ways to work around it before submitting a PR.

[Skullbock]

Hi, i got here thanks to @staudenmeir from #32592

I totally agree that this should be decided once and for all, and properly documented.
IMHO the current implementation doesn't make sense for queries including groupBy statements.
Why should be N_1 be the "right" result?

I also agree that A. is probably the best solution for consistency's sake, and it's also what everyone expects it to do. It would also solves a lot of issues with pagination, etc.

Since @taylorotwell rightly pointed out some performance issues with counting the subquery results, why don't we fix that on the php side, by checking if there are any group statements, and if so, performing a N_1 + N_2 + .... + N_M ?

Otherwise, maybe introducing a new method / parameter to allow for subquery counting?