Use DISTINCT in Laravel's withCount() method

Issue

With Laravel/Lumen eloquent I can get count relations like this:

User::withCount('views')->get();

this will use SQL

select `users`.*, (select count(*) from `views` where `users`.`id` = `views`.`user_id`) as `views_count` from `users`

and return all models with views_count attribute, great.

But I need these views counted with unique IP column. In this query I could simple replace count(*) with count(DISTINCT ip) like this:

select `users`.*, (select count(DISTINCT ip) from `views` where `users`.`id` = `views`.`user_id`) as `views_count` from `users`

and in phpMyAdmin it returns good results. But how achieve this in Laravel eloquent? I cannot find any way to use custom column for count. I can pass array with query function like this:

User::withCount(['views' => function ($q) { // what there? }])->get();

but now I can pass only where conditions, not use Distinct in any way.

I know I can first get models, and then foreach with distinct and count or groupby but I need to keep this single query, fast and simple. And if I can easy achieve this in raw SQL then I also should do this in Laravel somehow.
I can use some custom eloquent method if required because I will use this unique count in many places in applications.

So short question – how to combine withCount and Distinct?

PS I also tried to use distinct or groupBy on on model’s relation level (on hasMany) but this does not work.

Solution

User::withCount('views', function($query) {
    $query->select(DB::raw('count(distinct(ip))'));
})->get();

Answered By – HTMHell

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0