Laravel get unique views

I have this code on controller:

$shops = Shop::withCount(['views' => function ($query) {
        $query->distinct('visitor');
}])->get();

I use this package: https://github.com/cyrildewit/eloquent-viewable In table views I have column visitor. Why distinct not working?

I get this query on result:

select `shops`.*, (select distinct count(*) from `views` where `shops`.`id` = `views`.`viewable_id` and `views`.`viewable_type` = 'App\Shop') as `views_count` from `shops` where `user_id` = 1 order by `id` asc limit 10 offset 0

Why distinct is not working?

I need get: count(distinct visitor), but not distinct count(*)

realrandyallen

7 years ago

Best Answer

Level 44

distinct actually doesn't except a column, it will insert whatever column you're trying to count automatically. However, I think this is going to generate two queries, this should work:

$shops = DB::table('shops')
        ->selectRaw('shops.*, count(distinct visitor) as visitor_count')
        ->leftJoin('views', 'shops.id', '=', 'views.viewable_id')
        ->where('viewable_type', 'App\Shop')
        ->groupBy('shops.id')
        ->get();

Consider making this scope in your Shop model so it's reusable

1 like

Please or to participate in this conversation.