laravel DB->count() 从 count(DB->get()) 返回不同的值

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

提问by andrewtweber

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.

但它返回了错误的数字，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?

然后它返回正确的数字，34。为什么这些不是相同的值？

Here's my table structure

这是我的表结构

user_visits( user_id, date_visited, num_clicks )

回答by Lee

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).

这将打印出查询日志，如果您在有问题的查找之后立即执行此操作，您可以直接转到最新查询的日志末尾（即，如果您将其放置在第二次查找之后，则日志中的最后一个查询将是您的包装计数器，最后一个查询将是 count 方法）。

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

这将返回每个组中的条目数。mysql 正在做的是按 user_id 列对所有行进行分组，然后每组返回一行和计数。如果我们将“user_id”添加到用于选择的列中，并针对您的数据库手动运行查询，您可能会看到类似这样的结果

// 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).

这是做什么，只是选择所有条目，将它们分组并返回它们。这导致每个用户 id 返回一行，并且该行包含该 user_id 的一个条目的所有信息（它可能是该用户的第一个条目，也可能是最后一个，也可能是随机的，不过没关系）。

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

你count()然后计算有多少行被返回，这将是唯一的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).

因此，您的第一个查询是计算每个组有多少个 user_id（当您尝试打印结果时，laravel 将返回第一条记录，这将导致显示结果集中第一个 user_id 的条目数），以及您的第二个查询（加上应用的计数函数）返回找到了多少组（即唯一 user_id 的数量）。

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

查询 1：将返回“8”，因为这是结果集中的第一个条目的计数
查询 2：将返回“3”，因为这是计数，作为结果集中的行数

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:

想要正确的数字，而不需要加载所有数据？
如果您想要根据第二个查询获得正确的结果，但想要查询 1 的轻量级、网络重量较轻的单个整数响应，您可以执行以下操作：

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

希望一切都说得通，我敢肯定，让您的头脑有点混乱

回答by The Alpha

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

您可以尝试这样做，因为该group by语句是在count( Bug #26209)之后执行的：

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

Another mySql answerhere.

另一个mySql 答案在这里。