[FIXED] Relationship laravel : count data

Issue

Model Category:

public function product()
{
    return $this->hasMany(products::class);
}

Model product:

public function category()
{
    return $this->belongsTo(Category::class);
}

I handle in controller:

$result = Category::select(['id', 'name'])
    ->with(['product:category_id, status'])
    ->where('order', 1)
    ->get();

Result when I print out raw data :

[
  'id' => 1,
  'name' => 'Hot'
  'product' => [
     'status' => 1,
     'category_id' => 1
   ]
]
[
   'id' => 2,
   'name' => 'New'
   'product' => [
      'status' => 2,
      'category_id' => 2
   ]
]
..........

I got the list of category id and name, and got the product data array based on relationship. In my product table, There is a status column with values ​​equal to 1,2,3.

Now I want to count how many status = 1 and how many status = [2, 3] through the product array I get using that relationship?

Solution

you can use withCount for counting related data. lets say, you want total products, status1 products and status23 products.

$result = Category::select(['id', 'name'])
    ->with(['product:category_id, status'])
    ->withCount([
        'product', 
        'product as status_one_product' => function ($query) {
            $query->where('status', 1);
         },
         'product as status_other_product' => function ($query) {
              $query->whereIn('status', [2, 3]);
          }
     ])
     ->where('order', 1)
     ->get();

now you can get counted data like

echo $result[0]->product_count; // total product
echo $result[0]->status_one_product; // total product with status 1
echo $result[0]->status_other_product; // total product with status 2 and 3

Answered By - zahid hasan emon