Get count of unique values with the column name in Laravel 5.2 Collection

I am trying to get the number of unique Brands from my Products Table with their count from a Laravel collection.

I was able to do that using a specific query for the products but the reason i’m using a collection now is because i also want to get the Product Origins (Country), Conditions (Used / New) of the products and i thought it would be much better to use a collection from one query rather than have three separate queries for each data.

The code below works but it doesn’t show the count for each unique Brand.

Here is the Table

Here is my Controller

$products = DB::table('products')
 ->select('products.*')
 ->whereNull('products.deleted_at')
 ->get();

$BrandCollection = collect($products);
$Brands = $BrandCollection->unique('Brand')->sortBy('Brand')->keyBy('Brand')->pluck('Brand');

So, the result i’m looking for is

HP 3
Toshiba 2
Lenovo 1

I thought it could be done using concat for the collection but since i’m on Laravel 5.2, i’m looking for other solutions.

Answer

If you really want to use collections (not Eloquent) you can do it like this:

$brandsWithCount = $BrandCollection->groupBy('Brand')->map(function($values) {
    return $values->count();
})->sort()->reverse();

For example if you set $brandCollection like this:

$BrandCollection = collect([
    ['Brand' => 'HP'],
    ['Brand' => 'HP'],
    ['Brand' => 'HP'],
    ['Brand' => 'Toshiba'],
    ['Brand' => 'Toshiba'],
    ['Brand' => 'Lenovo'],
]);

result will be:

Collection {#372
  #items: array:3 [
    "HP" => 3
    "Toshiba" => 2
    "Lenovo" => 1
  ]
}

as expected.