php - Laravel 5.6 Query Builder nested sub queries
Get the solution ↓↓↓I have an app that has been coming under some performance pressure. Mainly due to fairly complex DB queries and frequency. I am making ajax calls to an endpoint which queries the DB and the ajax call fires every time a user updates a map position or changes a filter parameter. Each time filtering through 1000's of records.
I have established that the main bottleneck is not the speed of the DB query but the instantiating or hydrating of the eloquent models. To get around this I am caching the models in a redis datastore and the calling the model by the model ID after the DB query.
In principle (providing the model instance is cached) the retrieval is much quicker and takes a lot of pressure of the back end. Great. However, Eloquent provided some great methods for querying nested relationships which I am now struggling to re-write with the "query builder" methods.
The 1 dimensional stuff is fine, comparing values directly on the primary table but relations and nested relations are tricky for me, not having an in depth SQL background.
My initial query looks like this:
// Request values are simple arrays of id's from the front end
$productTypes = $request->productTypes;
$inclusiveItems = $request->inclusiveItems;
$amenities = $request->amenities;
$range = $request->range;
$bounds = $request->mapBounds;
$providers = $request->providers;
$spaces = Space::
with('provider:id,name,logo')->
with('address:id,locality,dependent_locality')->
where('published', '=', 2)->
whereHas('provider', function ($query) {
$query->where('authorised', '=', 1);
})->
when(count($bounds), function($query) use ($bounds){
return $query->whereBetween('lng', [$bounds["west"], $bounds["east"]])
->whereBetween('lat', [$bounds["south"], $bounds["north"]]);
})->
// So far so good. Now we hit some problems
when(count($inclusiveItems), function($query) use ($inclusiveItems){
return $query->whereHas('products.inclusiveItems', function ($query) use ($inclusiveItems) {
$query->whereIn('id', $inclusiveItems);
}, '=', count($inclusiveItems));
})->
when(count($providers), function($query) use ($providers){
return $query->whereHas('provider', function ($query) use ($providers) {
$query->whereIn('id', $providers);
});
})->
when(count($amenities), function($query) use ($amenities){
return $query->whereHas('amenities', function ($query) use ($amenities) {
$query->whereIn('id', $amenities);
}, '=', count($amenities));
})->
when($productTypes, function($query) use ($productTypes){
return $query->whereHas('products', function ($query) use ($productTypes) {
$query->whereIn('product_type_id', $productTypes);
});
})->
orderBy('enhanced', 'desc')
// We have to get the results before we can call a custom callback on the reject() method
->get()
->reject(function ($space) use ($productTypes, $range) {
return $space->productsInRange($productTypes, $range);
})
// Reset the array keys otherwise you'll get an object and it will mess up JS array.map functions
->values();
Representative of one dimension I need to query. I am essentially looking for an efficient way to query the nested relationships. So each Space has many Products and each Product has many InclusiveItems. I need to test whether the Space->Products have InclusiveItems matching the $inclusiveItems request array.
Previously achieved like this:
->when(count($inclusiveItems), function($query) use ($inclusiveItems){
return $query->whereHas('products.inclusiveItems', function ($query) use ($inclusiveItems) {
$query->whereIn('id', $inclusiveItems);
}, '=', count($inclusiveItems));
})
If I can resolve this problem then the same approach will be applied to others.
I guess you can't have your cake and eat it too and those eloquent methods were doing some heavy lifting and I certainly don't want to get into foreach loops from the results as I'll be back to n+1 and a slowdown.
My other thought was to return and initial subset to the front end and manage the filtering there but this could lead to large dataset being handed to the browser.
Any useful thoughts out there?
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: zsh: command not found: php
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.