Why distinct is not working With Laravel + Postgresql?

Asked by: Adarsh Sojitra

I want to count the number of rows having distinct values on ONLY ONE COLUMN. For example, If I have activities table in which there is email column, I want to count the number of columns having distinct value in email column.

I am using Laravel + PostgreSQL. I tried the following code but it's counting all the rows!

$emailsCount = DB::table('activities')
    ->where('user_id',$UserId)
    ->distinct('email')
    ->count();

Later, I came to know that distinct() will not take any arguments, I tried to find a solution to my problem and someone(Other Stackoverflow Questions) suggested me to use groupBy just like the following code.

$emailsCount = DB::table('activities')
    ->where('user_id',$UserId)
    ->groupBy('email')
    ->count();

But it's returning wrong information. For example, In my case, It's returning 1 but I can see lots and lots of different emails in email column. I don't know what's wrong. How can I do this in the right way?

Answers

Answered by: Maraboc at 2017-07-11 01:09PM Accepted

As you said distinct() don't take any arguments, instead you can passe that parameter to the countmethod like this :

$emailsCount = DB::table('activities') 
                   ->where('user_id',$UserId) 
                   ->distinct() 
                   ->count(["email"]);

Source