I’m trying to get a count of listings in regions under a category.

The menu for regions on the home page shows a count of all listings in their regions from every category. On the categories page I’m trying to show a count for regions, that have listings in a selected category.

In my Listing Model

 public function scopeInCategory($query, Category $category)
 {
    return $query->whereIn('category_id', [$category->id]);
 }

 public function region()
 {
    return $this->belongsTo(Region::class);
 }

In my Region model

 public function listings()
 {
    return $this->hasMany(Listing::class);
 }

In my CategoryController

class CategoryController extends Controller
{

    public function index(Category $category)
    {

        $regions = Region::with(['listings' => function($query) use ($category) {
        
            $query->inCategory($category);
        }])->first('region')->get();

       return view('categories.index', compact('regions'));
   }
}

and in my region_dropdown.blade.php

 @foreach($regions as $region)
   
     <a  href="#">{{ $region->name }} 
    ( {{ $region->listings->count() }} )</a>
     
 @endforeach

But this is not working the region menu still shows a count of all listings in every category on the categories page.

CodePudding user response：

You can use Eloquent's withCount method to get a count of Listings under each Region by a specific Category and then access the counted value on each Region by accessing the listings_count attribute that Eloquent will initialize for you.

class CategoryController extends Controller
{
    public function index(Category $category)
    {
       $regions = Region::withCount([
           'listings' => fn($q) => $q->where('category_id', $category->id)
       ])->get();
       return view('categories.index', compact('regions'));
   }
}

And in your blade file:

@foreach($regions as $region)
    <a  href="#">
        {{ sprintf('%s (%d)', $region->name, $region->listings_count) }}
    </a>
@endforeach

Feel free to ask for any clarification.