phpmyadmin Question posted in
The official documentation can be found here.

Use DISTINCT in Laravel's withCount() method – Phpmyadmin

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.

Tags:

2

Answers


  1. 0 
    User::withCount('views', function($query) {
    $query->select(DB::raw('count(distinct(ip))'));
})->get();
    Login or Signup to reply.
  2. 0

    Solution for Laravel 8.x

    User::withCount(['views as views_count' => function($query) {
    $query->select(DB::raw('count(distinct(ip))'));
}])->get();

    Note: Do not use selectRaw.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search