How can I make a collection in Laravel, where same keys will combine?

Using Laravel 8, I want to count travelers who visit my tourist information center. Traveler comes to our info center, asks anything, our employee gives necessary information and then we ask for travelers nationality to count for the statistic reasons.

For example, if the traveler is from Germany, an employee adds him into our stats table submitting a form. If any country repeats, I want to combine them with their counts.

For all of this, I have countries table and counts table. countries table has only string name, counts table has integer country_id and integer count.

This is StatController where I want to return stats:

return view('stats.counter', [
    'stats' => Stat::groupBy('country_id', 'count')
        ->selectRaw('group_concat(country_id) as country, group_concat(count) as visitor')
        ->whereDate('created_at', Carbon::today())
        ->get()
]);

This is how I get the stats:

[
    {
        "country": "1,1",
        "visitor": "1,1"
    },
    {
        "country": "1",
        "visitor": "2"
    },
    {
        "country": "1",
        "visitor": "4"
    },
    {
        "country": "1",
        "visitor": "6"
    },
    {
        "country": "2",
        "visitor": "5"
    },
    {
        "country": "2",
        "visitor": "8"
    },
    {
        "country": "3",
        "visitor": "1"
    },
    {
        "country": "5",
        "visitor": "4"
    },
    {
        "country": "8",
        "visitor": "4"
    },
    {
        "country": "9",
        "visitor": "5"
    },
    {
        "country": "11",
        "visitor": "3"
    }
]

I am doing something wrong, but don’t know what, can you help me?

Answer

This was a solution for me:

return view('stats.counter', [
    'stats' => Stat::select([
           'country_id',
           'count' => DB::raw('SUM(count) as count')
        ])
        ->groupBy('country_id')
        ->orderBy('count', 'DESC')
        ->whereDate('created_at', Carbon::today())
        ->get()
]);

Advertisement

Answer