Laravel : Unique count instead of total

I have the following in my controller :

public function countries($id)
{
    
    $country_visit = DB::table('profile_visits')
    ->where('profile_id','=', $id)
    ->select('country', DB::raw('count(*) as total'))
    ->groupBy('ip')
    ->get();

  //  $new =  $country_visit->count();
 
    return compact('data','country_visit');
}

The output :

{        
    "country_visit": [
        {
            "country": "Netherlands",
            "total": 1
        },
        {
            "country": "Morocco",
            "total": 10
        },
        {
            "country": "Netherlands",
            "total": 1
        }
    ]
}

When I do $new = $country_visit->count(); it returns a count of 3 which is true, but I want to return a count of each country. Meaning Morocco : 1 and Netherlands : 2. How to do so ?

I tried to group by 'name' but it didn't work.

Edit : Table content

enter image description here

3 answers

  • answered 2021-05-15 18:19 Peppermintology

    You can use the groupBy collection helper then the map collection helper to get the country count:

    $country_visit->groupBy('country')->map(function ($item, $key) {
        return $item->count();
    });
    

    Alternatively you can provide multiple arguments to the groupBy query.

    Update

    You are actually wanting a count of distinct ip addresses grouped by country, so:

    $country_visit = DB::table('profile_visits')
        ->where('profile_id','=', $id)
        ->select('country', DB::raw('count(distinct ip) as total'))
        ->groupBy('country')
        ->get();
    

  • answered 2021-05-15 18:33 HijenHEK

    You basically want to group by country but without repeating those with the same ip then you use distinct on count of ip .

    public function countries($id)
    {
        $country_visit = DB::table('profile_visits')
        ->where('profile_id','=', $id)
        ->select('country', DB::raw('count(DISTINCT ip) as total'))
        ->groupBy('country')
        ->get();
    
        return compact('data','country_visit');
    }
    

    SQL équivalent

    SELECT `country` , COUNT( DISTINCT `ip`) FROM `profile_visits` where profile_id = 5 GROUP BY  `country`
    

    Example

    sample table query result

  • answered 2021-05-15 19:15 MHIdea

    Use pluck method. check it here,

    you just need to add:

    ...->pluck('total','country');
    

    update

        $country_visit = DB::table('profile_visits')
            ->where('profile_id', $id)
            ->select('country', DB::raw('count(DISTINCT ip) as total'))
            ->groupBy('country')
            ->pluck('total', 'country');