Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
It's a typical search/filter page for a real estate site. CITY is dropdown and LOCALITY is dependent dropdown of CITY. ACTIVELISTING is a checkbox for showing only projects with active property listings. I am unable to optimise this query. The following code is working but looks ugly :) Kindly help me to optimise it.
// If city is selected for query
if ($city)
{
if ($locality == null)
{
if ($activelisting == 1)
{
$projects = projects::with('builder','city','locality')
->where('project_city', $city)
->withCount('reviews')
->whereHas('listings', function ($q) {$q->where('dealstatus', 'open');})
->orderByDesc('reviews_count')
->paginate(10)
->withQueryString();;
$projectscount = projects::where('project_city', $city)
->whereHas('listings', function ($q) {$q->where('dealstatus', 'open');})
->count();
}
else
{
$projects = projects::with('builder','city','locality')
->where('project_city', $city)
->withCount('reviews')
->orderByDesc('reviews_count')
->paginate(10)
->withQueryString();;
$projectscount = projects::where('project_city', $city)->count();
}
$cityname = Cities::find($city);
$cityname = $cityname->city_name;
$countstring = "Societies found in". " ".$cityname;
$headerstring = "Societies in". " ".$cityname;
}
if ($locality <> null)
{
if ($activelisting == 1)
{
$projects = projects::with('builder','city','locality')
->where('project_city', $city)
->whereHas('listings', function ($q) {$q->where('dealstatus', 'open');})
->where('project_locality', $locality)
->withCount('reviews')->orderByDesc('reviews_count')
->paginate(10)
->withQueryString();;
$projectscount = projects::where('project_city', $city)
->where('project_locality', $locality)
->whereHas('listings', function ($q) {$q->where('dealstatus', 'open');})
->count();
}
else
{
$projects = projects::with('builder','city','locality')
->where('project_city', $city)
->where('project_locality', $locality)
->withCount('reviews')->orderByDesc('reviews_count')
->paginate(10)
->withQueryString();;
$projectscount = projects::where('project_city', $city)->where('project_locality', $locality)->count();
}
$cityname = Cities::find($city);
$cityname = $cityname->city_name;
$localityname = Localities::find($locality);
$localityname = $localityname->locality_name;
$countstring = "Societies found in". " ".$cityname." | ".$localityname;
$headerstring = "Societies in". " ".$cityname." | ".$localityname;
}
}
else
{
if ($activelisting == 1)
{
$projects = Projects::with('builder','city','locality')
->withCount('listings')
->withCount('reviews')
->withAvg('reviews', 'rating')
->whereHas('city', function ($query) {$query->where('status', 1);})
->whereHas('listings', function ($q) {$q->where('dealstatus', 'open');})
->leftJoin('reviews', function ($join) {$join->on('reviews.project_id', 'projects.id')->where('status', 1);})
->groupBy('projects.id')
->orderByDesc('reviews_count')
->paginate(15)
->withQueryString();
$projectscount = projects::whereHas('city', function ($query) {$query->where('status', 1);})
->whereHas('listings', function ($q) {$q->where('dealstatus', 'open');})
->count();
}
else
{
$projects = Projects::with('builder','city','locality')
->withCount('reviews')
->withAvg('reviews', 'rating')
->whereHas('city', function ($query) {
$query->where('status', 1);})
->leftJoin('reviews', function ($join) {
$join->on('reviews.project_id', 'projects.id')->where('status', 1);})
->groupBy('projects.id')
->orderByDesc('reviews_count')
->paginate(15)
->withQueryString();
$projectscount = projects::whereHas('city', function ($query) {$query->where('status', 1);})
->count();
}
$countstring = "Societies found";
$headerstring = "Search Societies";
}
Yes get rid of all the ifs. And use when’s
https://laravel.com/docs/8.x/queries#conditional-clauses
Then you can get rid of all the repeated code. Just condition the query, note there is a place in the when parameters for a default too.
idk .. here is a first pass, as an example... not sure I love it yet but you get the idea...
$projects = projects::query()
->with('builder','city','locality')
->when($city, fn ($q) => $q->where('project_city', $city),
// else when no city
function ($q) {
$q->leftJoin('reviews', function ($join) {
$join->on('reviews.project_id', 'projects.id')
->where('status', 1);
})
->groupBy('projects.id');
})
->when($locality, fn ($q) => $q->where('project_locality', $locality))
->when($activelisting == 1, fn ($query) =>
$query->whereHas('listings', fn ($q) => $q->where('dealstatus', 'open'))
->withCount('listings')
)
->orderByDesc('reviews_count')
->withCount('reviews')
->paginate(10);
$projectscount = projects::query()
->when($city,
fn ($q) => $q->where('project_city', $city),
fn ($q) => $q->whereHas('city', fn ($q) => $q->where('status', 1))
)
->when($locality, fn ($q) => $q->where('project_locality', $locality))
->when($activelisting == 1, function ($query) {
$query->whereHas('listings', fn($q) => $q->where('dealstatus', 'open'));
})
->count();
$cityname = $city ? Cities::find($city)->city_name : '';
$localityname = $locality ? Localities::find($locality)->locality_name : '';
$countstring = "Societies found";
$headerstring = "Search Societies";
if ($cityname) {
$countstring .= " in " . implode(' | ', array_filter([$cityname, $localityname]));
$headerstring = "Societies in " . implode(' | ', array_filter([$cityname, $localityname]));
}
@webrobert Thanks bro. Will check.
@webrobert That's still a considerable amount of code to me, even after adding when methods. Maybe we can extract the conditions to a number of query scopes.
@kevinbui yeah, the op had another reply about being stuck on the when’s. So this was a first pass on refactoring.
@kevinbui I am feeling like a kid among adults lolzzz...... can you share any link for 'query scopes' ?
@webrobert Thanks for putting so much effort... :)
Please or to participate in this conversation.