DB-> count () returns another value from count (DB-> get ())

Question

I have the easiest of the requests I'm trying to run

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

But it returns the wrong number, 8.

If I changed it to this:

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

Then it returns the correct number, 34. Why is this not the same value?

Here is my table structure

 user_visits( user_id, date_visited, num_clicks ) 

Answer 1

Debugging Note
The queries created by these two different approaches are completely different, and that is why this happens to you. Whenever you experience database problems, it is always a good idea to take a look at the main query log so that you can see what is running:

 dd(DB::getQueryLog()); 

which will print a query log, if you do it immediately after an abusive search, you can simply go to the end of the log for your last query (i.e. if you placed it after the second search, the last query in the log will be your certified counter, and the last one except for one, there will be a count method).

Your specific problem
In any case, to explain your specific problem. Two generated queries will look like

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

This will return the number of records in each group. What mysql does is group all rows by user_id column and then return one row for each group with counters. If we add "user_id" to the columns for selection and run the query manually against your database, you may 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 | ---------------------- 

The second request is different

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

What is it, just selecting all the records, grouping them and returning. As a result, this is one line for each returned user ID, and this line contains all the information for ONE of the entries for this user_id (this may be the first record for this user, it may be the last, it may be random, it does not matter, although )

Then your count() counts how many rows were returned, this will be an account of unique user_ids.

So, your first query counts how many user identifiers for each group (and laravel will return the first record when trying to print the result, which will display the number of records for the first user_id in the result set), and your second query (plus the used count function) returns the number groups found (i.e. the number of unique user_ids).

Using my table above to illustrate this ...

Query 1: will return “8”, since this is the counter, for the first record in the result set Query 2: will return “3”, since this is the number, for the number of rows in the result set

Want the right number without downloading all the data?
If you want to get the correct result according to your second request, but want to get a light, less network strong integer response from request 1, you can do this:

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

that leads to:

 SELECT COUNT(DISTINCT user_id) FROM user_visits; 

Hope everything makes sense, it is a little confusing to lower your head. I sure that

Answer 2

You can try this because the group by statement executes after count ( Error # 26209 ):

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

Another mySql answer .