I have a query script for Laravel that does multiple search filtering algorithm. The search filters all work perfectly on local, but on production, when a keyword is entered, it correctly checks for model relationships, but does not check for data on the listings table directly. The same code works perfectly on local, at first, I thought it was a code issue, but am not sure anymore.
The production server has the following configurations:
- Server version: 10.5.20-MariaDB-cll-lve - MariaDB Server
- Protocol version: 10
- PHP Version: 8.0
While the PHPMyAdmin reports the global server PHP settings which is different from my domain: the global PHP version is 7.4.27.
On my local, the versions are:
- Server version: 8.0.32 - MySQL Community Server - GPL
- Protocol version: 10
Here is the code that searches.
public static function listings(Request $request)
{
try{
$initialData = Listing::query()
->select(
'listings.*',
'states.name as state_name',
'sub_categories.sub_category_name',
'sub_categories.is_land'
)
->leftJoin('states', 'listings.state_id', 'states.id')
->leftJoin('sub_categories', 'listings.sub_category_id', 'sub_categories.id')
->when($request->has('brand_id'))->where('brand_id', $request->brand)
->when($request->has('brand'), function ($query) use ($request) {
$query->join('brands', 'listings.brand_id', '=', 'brands.id')
->where('brands.brand_name', 'LIKE', "%{$request->brand}%");
})
->when($request->has('listing_type'))->listingType(\strtolower($request->listing_type))
->withCasts(['is_land' => 'boolean']);
$result = $initialData
->when($request->has('subcategory'), function ($query) use ($request) {
$subcategories = Str::of($request->subcategory)->explode(',');
$query->where(function ($subquery) use ($subcategories) {
/** @var string[] $subcategories */
foreach ($subcategories as $subcategory) {
$subquery->orWhere('sub_categories.sub_category_name', 'LIKE', "%{$subcategory}%");
}
});
})
->when($request->has('payment_plan'), function ($query) use ($request) {
$requestedPaymentPlans = Str::of($request->payment_plan)->explode(',')->toArray();
$query->where(function ($subquery) use ($requestedPaymentPlans) {
foreach ($requestedPaymentPlans as $paymentPlan) {
$subquery->orWhereJsonContains('payment_plan', $paymentPlan);
}
});
})
->when($request->has('space_type'), function ($query) use ($request) {
$space_type = Str::of($request->space_type)->explode(',');
$query->where(function ($subquery) use ($space_type) {
/** @var string[] $space_type */
foreach ($space_type as $space) {
$subquery->orWhere('listings.space_type', 'LIKE', "%{$space}%");
}
});
})
->when($request->has(['min_price', 'max_price']))->where(function ($query) use ($request) {
$query->whereBetween('listings.price', [$request->query('min_price'), $request->query('max_price')]);
})
->when($request->has('location'), function ($query) use ($request) {
/** @var string[] $location */
$location = Str::of($request->location)->explode(' ');
$query->where(function ($subquery) use ($location) {
foreach ($location as $loc) {
$subquery->orWhereHas('state', function ($q) use ($loc) {
$q->where('states.name', 'LIKE', "%{$loc}%");
})
->orWhereHas('country', function ($q) use ($loc) {
$q->where('countries.name', 'LIKE', "%{$loc}%");
})
->orWhere('listings.full_address', 'LIKE', "%{$loc}%")
->orWhere('listings.city', 'LIKE', "%{$loc}%");
}
});
})
->when($request->has('keyword'), function ($query) use ($request) {
/** @var string[] $keywords */
$keywords = Str::of($request->keyword)->explode(' ');
$query->where(function ($subquery) use ($keywords) {
foreach ($keywords as $keyword) {
$subquery->orWhere('listings.listing_caption', 'LIKE', "%{$keyword}%")
->orWhere('listings.listing_description', 'LIKE', "%{$keyword}%")
->orWhereHas('state', function ($q) use ($keyword) {
$q->where('states.name', 'LIKE', "%{$keyword}%");
})
->orWhereHas('country', function ($q) use ($keyword) {
$q->where('countries.name', 'LIKE', "%{$keyword}%");
})
->orWhere('listings.city', 'LIKE', "%{$keyword}%")
->orWhere('listings.full_address', 'LIKE', "%{$keyword}%")
->orWhereHas('sub_category', function ($q) use ($keyword) {
$q->where('sub_categories.sub_category_name', 'LIKE', "%{$keyword}%");
});
}
});
})
->get();
$landResults = $result->where('is_land', true);
$nonLandResults = $result->where('is_land', false);
$finalResult = collect([]);
$filteredNonLandResults = collect([]);
if ($nonLandResults->isEmpty()) {
if ($landResults->isEmpty()) {
$finalResult = $initialData->get();
}
}else {
$filteredNonLandResults = $nonLandResults->when($request->has('bathrooms'), function ($query) use ($request) {
$query->where('listings.bathrooms', $request->bathrooms);
})
->when($request->has('bedrooms'), function ($query) use ($request) {
$query->where('listings.bedrooms', $request->bedrooms);
})
->when($request->has('condition'), function ($query) use ($request) {
$condition = Str::of($request->condition)->explode(',');
$query->where(function ($subquery) use ($condition) {
/** @var string[] $condition */
foreach ($condition as $cond) {
$subquery->orWhere('listings.condition', 'LIKE', "%{$cond}%");
}
});
})
->when($request->has('furnishing'), function ($query) use ($request) {
$furnishings = Str::of($request->furnishing)->explode(',');
$query->where(function ($subquery) use ($furnishings) {
/** @var string[] $furnishings */
foreach ($furnishings as $furnishing) {
$subquery->orWhere('listings.furnishing', 'LIKE', "%{$furnishing}%");
}
});
});
$finalResult = $landResults->concat($filteredNonLandResults)->values()->all();
}
$queryLog = DB::getQueryLog();
Log::info($queryLog);
return $finalResult;
}catch(\Exception $e) {
return $e->getMessage();
}
}
I also echoed out the query in the log, and it looks really fine, in fact, running it directly on the database engine in production, it brings the desired result, but the endpoint returns very unstable results, it works in weird ways.
Imagine searching space_type using the value 'private' I get results, then change it to 'shared' which also exists, it returns empty array.
Edited from here
On the production, the query log sample for when only the keyword parameter is supplied is:
[2023-12-07 14:28:19] production.INFO: array (
0 =>
array (
'query' => 'select `listings`.*, `states`.`name` as `state_name`, `sub_categories`.`sub_category_name`, `sub_categories`.`is_land` from `listings` left join `states` on `listings`.`state_id` = `states`.`id` left join `sub_categories` on `listings`.`sub_category_id` = `sub_categories`.`id` where (`listings`.`listing_caption` LIKE ? or `listings`.`listing_description` LIKE ? or exists (select * from `states` where `listings`.`state_id` = `states`.`id` and `states`.`name` LIKE ?) or exists (select * from `countries` where `listings`.`country_id` = `countries`.`id` and `countries`.`name` LIKE ?) or `listings`.`city` LIKE ? or `listings`.`full_address` LIKE ? or exists (select * from `sub_categories` where `listings`.`sub_category_id` = `sub_categories`.`id` and `sub_categories`.`sub_category_name` LIKE ?))',
'bindings' =>
array (
0 => '%near%',
1 => '%near%',
2 => '%near%',
3 => '%near%',
4 => '%near%',
5 => '%near%',
6 => '%near%',
),
'time' => 6.28,
),
)
while on local, this is the query log:
[2023-12-07 14:38:50] local.INFO: array (
0 =>
array (
'query' => 'select `listings`.*, `states`.`name` as `state_name`, `sub_categories`.`sub_category_name`, `sub_categories`.`is_land` from `listings` left join `states` on `listings`.`state_id` = `states`.`id` left join `sub_categories` on `listings`.`sub_category_id` = `sub_categories`.`id` where (`listings`.`listing_caption` LIKE ? or `listings`.`listing_description` LIKE ? or exists (select * from `states` where `listings`.`state_id` = `states`.`id` and `states`.`name` LIKE ?) or exists (select * from `countries` where `listings`.`country_id` = `countries`.`id` and `countries`.`name` LIKE ?) or `listings`.`city` LIKE ? or `listings`.`full_address` LIKE ? or exists (select * from `sub_categories` where `listings`.`sub_category_id` = `sub_categories`.`id` and `sub_categories`.`sub_category_name` LIKE ?))',
'bindings' =>
array (
0 => '%near%',
1 => '%near%',
2 => '%near%',
3 => '%near%',
4 => '%near%',
5 => '%near%',
6 => '%near%',
),
'time' => 152.35,
),
)
After some good hints from Kris, I understood that it was the code logic that had the challenge. At some point in the lifecycle of the logic, the results fetched were overwritten. So here is the code fix that works for all search params.