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

Question

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

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

As a workaround, I'm doing this:

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

what works fine and returns "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 :(

What you do you have in your sample table in database? And what do you want to achieve? Now you should probably get number of distinct values in `pid` column, so if you have in your table 2 records - one with pid 1, second with pid 2, count should return 2. — Marcin Nabiałek, Feb 22 '15 at 18:23
you can simply replace get with count in this way: `$count = DB::table('tablename')->count(DB::raw('DISTINCT pid'));` also can do: `DB::table('tablename')->distinct('pid')->count('pid');` — bharat, May 10 '18 at 06:04

score 188 · Accepted Answer · edited Feb 11 '21 at 02:08

188

The following should work

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

edited Feb 11 '21 at 02:08

Mike Ross

2,942
5
49
101

answered Apr 19 '15 at 09:34

Suresh Bala

2,294
1
13
7

2

Had a similar issue and it seems just leaving out the `groupBy` does the trick. – jeteon Jun 03 '15 at 00:30
8

Distinct doesn't take any arguments. Calling distinct() when building your query just sets the protected boolean to true, the argument is ignored. – Matt McDonald Feb 05 '16 at 12:28
On L5.1 and this is still not working. Using `count()` seems to disable or drop the `distinct()`. Use `groupBy()` as described throughout the question. Edit: I'm finding even `groupBy()` is providing a different `count()` compared to `get()` followed by counting the resulting array. – Jason Mar 02 '16 at 12:43
@Jason I made the same observation as you. See my answer for a solution. – Zoon Mar 20 '16 at 10:39
23

The `distinct()` function takes no arguments. You can change it to `$ad->getcodes()->distinct()->count('pid');` with the same result. – Trevor Gehman Apr 18 '17 at 23:31
What is getcodes() here? `{{$sales->getcodes()->distinct()->count('customer_id')}}` i wanted to find distinct customer_id from $sales variable but it shows `Method Illuminate\Support\Collection::getcodes does not exist.` – Hola Apr 18 '21 at 05:45
1

@trevor-gehman that was probably the case in 2017, but at least now in Laravel 9, you can (and probably should) pass the column name in the distinct() function – Inigo EC Aug 16 '22 at 15:48

score 47 · Answer 2 · answered Mar 04 '18 at 18:43

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

score 24 · Answer 3 · edited May 29 '19 at 23:03

24

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.

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

or use 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::raw with the count method.

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

Remember, don't use groupBy before calling count. You can apply groupBy later, if you need it for getting rows.

edited May 29 '19 at 23:03

Alexis Wilke

19,179
10
84
156

answered Mar 20 '16 at 10:38

Zoon

1,068
2
11
26

Where did $builder come from? – Andrew Mar 04 '18 at 18:31
1

@Andrew The Laravel query builder you are using for the query. For example, an Eloquent object `$books = Book::where(...)->count(...)` – Zoon Mar 05 '18 at 06:34
2

`->count(DB::raw('DISTINCT b'))` generate the same SQL query as `->distinct()->count('b')` – Trevor Gehman Apr 27 '20 at 20:43

score 14 · Answer 4 · answered Sep 11 '19 at 10:27

14

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.

answered Sep 11 '19 at 10:27

Shahrukh Anwar

2,544
1
24
24

this will pull records from db and then perform counting. That is not efficient. In the other hand, $ad->getcodes()->distinct('pid')->count('pid'); now we avoid having to actually select the records and instead can just count them which is more efficient – Vladd Feb 08 '21 at 19:16

Matt McDonald · Answer 5 · 2016-12-21T09:20:01.263

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

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.

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

score 4 · Answer 6 · answered Feb 07 '17 at 00:47

I came across the same problem.

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

$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

jc_anchor · Answer 7 · 2020-02-10T06:17:16.020

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

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
]

score 2 · Answer 8 · answered Nov 28 '18 at 15:53

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

score 1 · Answer 9 · answered Feb 13 '17 at 10:50

1

Wouldn't this work?

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

See here for discussion..

answered Feb 13 '17 at 10:50

J Foley

1,038
1
17
30

3

This is not a good solution, because the `get()` call will execute the query and return the results from the database, and then the `count()` runs on the Collection. – Trevor Gehman Apr 18 '17 at 23:21

score 1 · Answer 10 · answered Nov 21 '18 at 17:56

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 flights and you may now add other modifiers like count() or even raw eloquent queries.

score 1 · Answer 11 · edited Oct 31 '22 at 11:20

1

Use something like this

DB::table('user_products')->select('user_id')->distinct()->pluck('user_id')->toArray();

edited Oct 31 '22 at 11:20

Leena Patel

2,423
1
14
28

answered Sep 19 '22 at 12:28

ulas korpe

39
2

Always give explanation to your answers. – Leena Patel Oct 31 '22 at 11:20

score 0 · Answer 12 · answered Jul 19 '23 at 22:46

In Laravel 9 i was able to get this working with using unique so it only pulls the latest times with unique IP addresses from my database.

This returns a number for me.

PageHistory::where('updated_at', '>=', Carbon::now()->subMinutes(5)->toDateTimeString())->get()->unique('ip_address')->count();

Sushant Yadav · Answer 13 · 2016-02-04T11:35:32.710

-2

This was working for me so Try This: $ad->getcodes()->distinct('pid')->count()

edited Feb 04 '16 at 11:35

answered Feb 04 '16 at 10:02

Sushant Yadav

726
1
13
28

Hi welcome to SO. When answering a question please provide additional information on the code you supply. Contributions like this are welcome but others in future may benefit from a shot explanation – Deepend Feb 04 '16 at 10:27

score -3 · Answer 14 · answered May 29 '19 at 13:59

-3

try this

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

answered May 29 '19 at 13:59

xiaoxiao

1
1

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

14 Answers14

Linked