Laravel Eloquent - distinct() 和 count() 不能正常工作

Laravel Eloquent - distinct() and count() not working properly together

提问by Inigo EC

So I'm trying to get the number of distinct pids on a query, but the returned value is wrong.

所以我试图获取查询中不同 pid 的数量，但返回的值是错误的。

This is what I try to do:

这就是我尝试做的：

$ad->getcodes()->groupby('pid')->distinct()->count()

what returns the value "2", while the value it should return, should be "1".

what 返回值“2”，而它应该返回的值应该是“1”。

As a workaround, I'm doing this:

作为一种解决方法，我这样做：

count($ad->getcodes()->groupby('pid')->distinct()->get())

what works fine and returns "1"

什么工作正常并返回“1”

Is there any rule where count and distinct cannot be on the same query? I find the workaround kind of "heavy", I would like to make the original query work :(

是否有任何规则使 count 和 distinct 不能出现在同一个查询中？我发现解决方法有点“重”，我想让原始查询工作:(

回答by Suresh Bala

The following should work

以下应该工作

$ad->getcodes()->distinct('pid')->count('pid');

回答by Andrew

A more generic answer that would have saved me time, and hopefully others:

一个更通用的答案可以节省我的时间，希望其他人可以：

Does not work (returns count of all rows):

不起作用（返回所有行的计数）：

DB::table('users')
            ->select('first_name')
            ->distinct()
            ->count();

The fix:

修复：

DB::table('users')
            ->distinct()
            ->count('first_name');

回答by Zoon

Anyone else come across this post, and not finding the other suggestions to work?

其他人遇到过这篇文章，却没有找到其他可行的建议吗？

Depending on the specific query, a different approach may be needed. In my case, I needed either count the results of a GROUP BY, e.g.

根据特定的查询，可能需要不同的方法。就我而言，我需要计算 a 的结果GROUP BY，例如

SELECT COUNT(*) FROM (SELECT * FROM a GROUP BY b)

or use COUNT(DISTINCT b):

或使用COUNT(DISTINCT b)：

SELECT COUNT(DISTINCT b) FROM a

After some puzzling around, I realised there was no built-in Laravel function for either of these. So the simplest solution was to use use DB::rawwith the countmethod.

经过一番折腾，我意识到这些都没有内置的 Laravel 函数。因此最简单的解决方案是使用使用DB::raw与count方法。

$count = $builder->count(DB::raw('DISTINCT b'));

Remember, don't use groupBybefore calling count. You can apply groupBylater, if you need it for getting rows.

请记住，groupBy在调用之前不要使用count。groupBy如果您需要它来获取行，您可以稍后申请。

回答by Matt McDonald

I had a similar problem, and found a way to work around it.

我遇到了类似的问题，并找到了解决方法。

The problem is the way Laravel's query builder handles aggregates. It takes the first result returned and then returns the 'aggregate' value. This is usually fine, but when you combine count with groupBy you're returning a count per grouped item. So the first row's aggregate is just a count of the first group (so something low like 1 or 2 is likely).

问题在于 Laravel 的查询构建器处理聚合的方式。它获取返回的第一个结果，然后返回“聚合”值。这通常很好，但是当您将 count 与 groupBy 结合使用时，您将返回每个分组项目的计数。因此，第一行的聚合只是第一组的计数（因此可能会低于 1 或 2）。

So Laravel's count is out, but I combined the Laravel query builder with some raw SQL to get an accurate count of my grouped results.

所以 Laravel 的计数已经结束，但我将 Laravel 查询构建器与一些原始 SQL 结合起来，以获得我分组结果的准确计数。

For your example, I expect the following should work (and let you avoid the get):

对于您的示例，我希望以下内容应该有效（并让您避免获取）：

$query = $ad->getcodes()->groupby('pid')->distinct();
$count = count(\DB::select($query->toSql(), $query->getBindings()));

If you want to make sure you're not wasting time selecting all the columns, you can avoid that when building your query:

如果您想确保不会浪费时间选择所有列，则可以在构建查询时避免这种情况：

 $query = $ad->select(DB::raw(1))->getcodes()->groupby('pid')->distinct();

回答by Shahrukh Anwar

You can use the following way to get the unique data as per your need as follows,

您可以使用以下方式根据您的需要获取唯一数据，如下所示，

$data = $ad->getcodes()->get()->unique('email');

$count = $data->count();

Hope this will work.

希望这会奏效。

回答by Brett

I came across the same problem.

我遇到了同样的问题。

If you install laravel debug bar you can see the queries and often see the problem

如果安装laravel debug bar就可以看到查询，经常看到问题

$ad->getcodes()->groupby('pid')->distinct()->count()

change to

改成

$ad->getcodes()->distinct()->select('pid')->count()

You need to set the values to return as distinct. If you don't set the select fields it will return all the columns in the database and all will be unique. So set the query to distinct and only select the columns that make up your 'distinct' value you might want to add more. ->select('pid','date')to get all the unique values for a user in a day

您需要将值设置为不同的返回值。如果您不设置选择字段，它将返回数据库中的所有列，并且所有列都是唯一的。因此，将查询设置为 distinct 并仅选择构成您可能想要添加更多“distinct”值的列。->select('pid','date')在一天内获取用户的所有唯一值

回答by JonnyFoley

Wouldn't this work?

这行不通？

$ad->getcodes()->distinct()->get(['pid'])->count();

See herefor discussion..

见这里讨论..

回答by Daniel Santos

$solution = $query->distinct()
            ->groupBy
            (
                [
                    'array',
                    'of',
                    'columns',
                ]
            )
            ->addSelect(
                [
                    'columns',
                    'from',
                    'the',
                    'groupby',
                ]
            )
            ->get();

Remember the group by is optional,this should work in most cases when you want a count group by to exclude duplicated select values, the addSelect is a querybuilder instance method.

请记住 group by 是可选的，当您希望计数 group by 排除重复的选择值时，这应该在大多数情况下起作用，addSelect 是一个 querybuilder 实例方法。

回答by jc_anchor

Based on Laravel docs for raw queriesI was able to get count for a select field to work with this code in the product model.

基于用于原始查询的 Laravel 文档，我能够获得选择字段的计数，以在产品模型中使用此代码。

public function scopeShowProductCount($query)
{
    $query->select(DB::raw('DISTINCT pid, COUNT(*) AS count_pid'))
          ->groupBy('pid')
          ->orderBy('count_pid', 'desc');
}

This facade worked to get the same result in the controller:

这个外观用于在控制器中获得相同的结果：

$products = DB::table('products')->select(DB::raw('DISTINCT pid, COUNT(*) AS count_pid'))->groupBy('pid')->orderBy('count_pid', 'desc')->get();

The resulting dump for both queries was as follows:

两个查询的结果转储如下：

#attributes: array:2 [
  "pid" => "1271"
  "count_pid" => 19
],
#attributes: array:2 [
  "pid" => "1273"
  "count_pid" => 12
],
#attributes: array:2 [
  "pid" => "1275"
  "count_pid" => 7
]

回答by Karl Anthony Baluyot

Distinct do not take arguments as it adds DISTINCT in your sql query, however, you MAY need to define the column name that you'd want to select distinct with. Thus, if you have Flight->select('project_id')->distinct()->get()is equialent to SELECT DISTINCT 'project_id' FROM flightsand you may now add other modifiers like count() or even raw eloquent queries.

Distinct 不接受参数，因为它会在您的 sql 查询中添加 DISTINCT，但是，您可能需要定义要选择不同的列名。因此，如果你有 Flight->select('project_id')->distinct()->get()is 等价于SELECT DISTINCT 'project_id' FROM flights并且你现在可以添加其他修饰符，如 count() 甚至原始的 eloquent 查询。