Laravel Eloquent concat and groupBy() AND return one title of each group

I have a table that contains, amongst other columns, a column of browser versions. And I simply want to know from the record-set, how many of each type of browser there are.

my code is :

$whereBrowser = [['owner', '=', 12]];
        $browser = ProfileTracker::where($whereBrowser)->whereBetween('created_at', [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()])->select([
            DB::raw('browser'),
            DB::raw('COUNT(browser) as value')
        ])->groupBy("browser")->orderBy("value", "desc")->take(10)->get();

output :

          "browser": [
              {
                  "browser": "Chrome Mobile 84",
                  "value": 144
              },
              {
                  "browser": "Chrome Mobile 80",
                  "value": 84
              },
              {
                  "browser": "Chrome 84",
                  "value": 27
              },
              {
                  "browser": "Chrome 30",
                  "value": 22
              },
              {
                  "browser": "Chrome Mobile 83",
                  "value": 19
              },
              {
                  "browser": "Chrome Mobile 47",
                  "value": 10
              },
              {
                  "browser": "Firefox 79",
                  "value": 8
              },
              {
                  "browser": "Mobile Safari",
                  "value": 8
              },
              {
                  "browser": "Mobile Safari 13.1.2",
                  "value": 6
              }
          ],
         
      }

I want to group all browsers, without the browser version like this:

            "browser": [
                {
                    "browser": "Chrome",
                    "value": 306
                },
                {
                    "browser": "Safari",
                    "value": 14
                },
                {
                    "browser": "Firefox",
                    "value": 8
                },
            ],
           
        }

Answer

One way you can pull this off is to look for browser names in strings and parse them to just the browser name in the query. So something like Mobile Safari 15.1.2 will be parsed to Safari:

ProfileTracker::selectRaw('
    CASE 
        WHEN browser LIKE '%firefox%' THEN 'Firefox'
        WHEN browser LIKE '%chrome%' THEN 'Chrome'
        WHEN browser LIKE '%safari%' THEN 'Safari'
    END AS browser_name,
    count(1) AS count
')->groupBy('browser_name')->get();

Please note that I have removed parts of the query builder, to make this example a little more readable. You can add back whatever you need yourself.

This will give you:

Chrome  1
Firefox 1
Safari  2

My test data is:

"Chrome Mobile"
"Firefox 79"
"Mobile Safari 13.1.2"
"Mobile Safari 15.1.2"