DB->count() returning different value from count(DB->get())

Question

I have the simplest of queries that I'm trying to run

DB::table('user_visits')->groupBy('user_id')->count();

But it's returning the wrong number, 8.

If I change it to this:

count(DB::table('user_visits')->groupBy('user_id')->get());

Then it returns the correct number, 34. Why are these not the same value?

Here's my table structure

user_visits( user_id, date_visited, num_clicks )

Cropdusting

Answer 1

A note on debugging
The queries generated by those two different approaches are completely different, and is why this is happening to you. Whenever you experience DB issues, its always a good idea to look at the underlying query log, so you can see what is being executed by doing:

dd(DB::getQueryLog());

that will print out the query log, if you do it right after the offending lookups, you can just go to the end of the log for your latest query (i.e. if you placed it after the second lookup, the last query in the log would be your wrapped counter, and the last but one query would be the count method).

Your specific issue
Anyway, to explain your specific issue. The two queries that are being generated will be like

DB::table('user_visits')->groupBy('user_id')->count();
// SELECT COUNT(*) from user_visits GROUP BY user_id

This will return the count of entries in each group. What mysql is doing is grouping all the rows by the user_id column, then returning one row per group with the counts. If we added the "user_id" into the columns for selection, and run the query manually against your database, you might see something like this as a result

// SELECT user_id, COUNT(*) FROM user_visits GROUP BY user_id

----------------------
| user_id | COUNT(*) |
----------------------
| 1       | 8        |
| 2       | 4        |
| 5       | 11       |
----------------------

Your second query is different

DB::table('user_visits')->groupBy('user_id')->get()
// SELECT * FROM user_visits GROUP BY user_id

What this is doing, is just selecting all entries, grouping them and returning them. What this results in, is one row per user id being returned, and that row contains all the information for ONE of the entries for that user_id (it might be the first entry for that user, it might be the last, it might be random, it doesn't matter though).

Your count() then counts how many rows were returned, which will be the count of unique user_ids.

So your first query is counting how many user_ids per group are there (and laravel will return the first record when you try to print the result, which results in displaying the number of entries for the first user_id in the result set), and your second query (plus the count function applied) is returning how many groups were found (i.e number of unique user_ids).

Using my table from above, to illustrate this...

Query 1: would return "8", as that is the count, for the first entry in the result set
Query 2: would return "3", as that is the count, for the number of rows in the result set

Want the correct number, without loading all the data?
If you want the correct result as per your second query, but want the lightweight, less network heavy single integer response of query 1, you can do this:

DB::table('user_invites')->count(DB::raw('DISTINCT user_id'))

which results in:

SELECT COUNT(DISTINCT user_id) FROM user_visits;

Hopefully that all makes sense, it's a little confusing to get your head around i'm sure

Hegemony

Does DB::table('user_invites')->select('user_id')->distinct()->count() not work? – Balneal

Thank you Lee, that was very comprehensive. I follow your explanation perfectly, I'm just really perplexed at why they coded it to behave that way. There are still 34 rows being returned, and I think it's really bizarre that ->count() returns the COUNT() value of the first row, rather than the number of rows. To get the former I would expect to do this ->select(DB::raw('COUNT(1) AS total'))->first() instead... oh well. – Cropdusting

@Cropdusting If you consider that the order in which you build the query doesn't matter, it makes more sense. You could have written DB::table('user_visits')->count()->groupBy('user_id'); and then it would be slightly more clear what it's doing. I'm not a big fan of the Laravel query builder though; definitely has some weirdness. – Balneal

So what if you need to use a group by, because you also have a having requirement that you need to filter the count() and results by. Seems like you are SOL then, you have to just query the full results and count them, or write a raw query yourself – Cropdusting

Answer 2

You may try this because the group by statement is executed after the count(Bug #26209):

DB::table('user_visits')->distinct('user_id')->count('user_id');

Another mySql answer here.

Abstriction