In controller
$all_cities = Item::leftJoin('states', 'items.state_id', '=', 'states.id')->leftJoin('cities', 'items.city_id', '=', 'cities.id')->orderBy('cities.city_name', 'ASC')
->select(
'items.city_id',
'states.state_slug',
'states.state_abbr',
'cities.city_name',
'cities.city_slug',
'cities.city_state'
)->groupby('items.city_id')->take(45)->get();
$citiesWithExtraValue = $all_cities->map(function ($city) {
$count = DB::table('items')
->where('city_id', '=', $city->city_id)
->count();
$city->count = $count;
return $city;
});
output of $all_cities
[
{
"city_id": 1,
"state_slug": "ny",
"state_abbr": "NY",
"city_name": "New York",
"city_slug": "new-york",
"city_state": "NY",
"count": 10 // The count of items associated with New York
},
{
"city_id": 2,
"state_slug": "ca",
"state_abbr": "CA",
"city_name": "Los Angeles",
"city_slug": "los-angeles",
"city_state": "CA",
"count": 5 // The count of items associated with Los Angeles
},
// ... and so on for other cities
]
In Blade file
<section class="states bg-warning bg-opacity-10 py-5" style="background-color:#fff9eb !important;">
<div class="container py-sm-5">
<div class="top_title mb-5 pb-5">
<h2 class="main_title text-center text-warning position-relative"> Business for sale by <span class="text-dark">Top Cities</span> </h2>
</div>
@if($all_cities->count() > 0)
<div class="row row-cols-md-5 row-cols-sm-2 row-cols-1">
@php
foreach($all_cities as $all_city_key => $city)
{
@endphp
<div class="col-sm-12 col-md-6 col-lg-4 mb-3">
@if ($city->city_state)
<a href="{{ route('page.city', ['state_slug'=>$city->city_state, 'city_slug'=>$city->city_slug]) }}">
{{ $city->city_name.", ".$city->state_abbr }} ({{ $city->count }})</a>
@endif
</div>
@php
}
@endphp
</div>
@endif
</div>
</section>
Output
Top comments (0)