DB-> count()返回与count(DB-> get())不同的值 [英] DB->count() returning different value from count(DB->get())

问题描述

我要运行的查询最简单

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

但是它返回了错误的数字8.

But it's returning the wrong number, 8.

如果我将其更改为此:

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

然后它返回正确的数字34.为什么这些值不相同?

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

这是我的表结构

user_visits( user_id, date_visited, num_clicks )

推荐答案

调试说明
这两种不同方法生成的查询完全不同，这就是您正在发生这种情况的原因.每当遇到数据库问题时，查看底层查询日志始终是一个好主意，因此您可以通过执行以下操作来查看正在执行的操作:

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());

将会打印出查询日志，如果您在有问题的查询之后立即执行此查询，则可以转到最新查询的日志末尾(即，如果您将其放置在第二次查询之后，则是最后一个查询)日志将是包装好的计数器，最后一个查询将是count方法).

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

这将返回每个组中的条目数. mysql正在做的是按照user_id列对所有行进行分组，然后每组返回一个包含计数的行.如果我们将"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       |
----------------------

您的第二个查询不同

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

这是在做的只是选择所有条目，将它们分组并返回它们.结果是，每个用户ID返回一行，并且该行包含该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).

您的count()然后计算返回的行数，这将是唯一的user_id的计数.

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

因此，您的第一个查询正在计算每个组中有多少个user_id(当您尝试打印结果时，laravel将返回第一条记录，这将显示结果集中第一个user_id的条目数)，您的第二个查询(加上应用的计数功能)将返回找到的组数(即，唯一的user_id的数量).

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

查询1:将对结果集中的第一个条目返回"8"，即计数.
查询2:将对结果集中的行数返回"3"(即计数)

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的轻量级，较少网络负载的单个整数响应，则可以执行以下操作:

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

其结果是:

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

这篇关于DB-> count()返回与count(DB-> get())不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！