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- gt getcodes  - gt groupby  pid  - gt distinct  - gt count     what returns the value  2   while the value it should return  should be  1    As a workaround  I m doing this   count  ad- gt getcodes  - gt groupby  pid  - gt distinct  - gt 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

User · Accepted Answer

The following should work  ad- gt getcodes  - gt distinct  - gt count  pid

User · Answer

Wouldn t this work    ad- gt getcodes  - gt distinct  - gt get   pid   - gt count      See here for discussion

User · Answer

solution    query- gt distinct               - gt groupBy                                                      array                        of                        columns                                               - gt addSelect                                         columns                        from                        the                        groupby                                               - gt 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

User · Answer

A more generic answer that would have saved me time  and hopefully others   Does not work  returns count of all rows    DB  table  users               - gt select  first name               - gt distinct               - gt count      The fix   DB  table  users               - gt distinct               - gt count  first name

User · Answer

I came across the same problem    If you install laravel debug bar you can see the queries and often see the problem   ad- gt getcodes  - gt groupby  pid  - gt distinct  - gt count     change to    ad- gt getcodes  - gt distinct  - gt select  pid  - gt 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  - gt select  pid   date   to get all the unique values for a user in a day

User · Answer

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- gt getcodes  - gt groupby  pid  - gt distinct     count   count  DB  select  query- gt toSql     query- gt 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- gt select DB  raw 1  - gt getcodes  - gt groupby  pid  - gt distinct

User · Answer

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- gt select  project id  - gt distinct  - gt get   is equialent to SELECT DISTINCT  project id  FROM flights and you may now add other modifiers like count   or even raw eloquent queries

User · Answer

This was working for me so Try This   ad- getcodes  - distinct  pid  - count

User · Answer

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

User · Answer

You can use the following way to get the unique data as per your need as follows    data    ad- gt getcodes  - gt get  - gt unique  email      count    data- gt count      Hope this will work

User · Answer

try this   ad- gt getcodes  - gt groupby  pid  - gt distinct  - gt count  pid

User · Answer

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- gt select DB  raw  DISTINCT pid  COUNT    AS count pid              - gt groupBy  pid             - gt orderBy  count pid    desc        This facade worked to get the same result in the controller    products   DB  table  products  - gt select DB  raw  DISTINCT pid  COUNT    AS count pid   - gt groupBy  pid  - gt orderBy  count pid    desc  - gt get      The resulting dump for both queries was as follows    attributes  array 2      pid    gt   1271     count pid    gt  19     attributes  array 2      pid    gt   1273     count pid    gt  12     attributes  array 2      pid    gt   1275     count pid    gt  7

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

Examples related to laravel

Examples related to count

Examples related to eloquent

Examples related to distinct