Level 15
Cache the query results if hitting the database very frequently and the data does not change very much.
Also you could create a database view for the results and get the results from a RAW sql query.
1 like
Oct 8, 2019
5
Level 7
Improve ->whereHas() query performance
I have a scope for querying the models that have a relationship with the passed category id. This scope returns all the models that have that category or subcategory relation.
public function scopeCategory($query, $category)
{
return $query->whereHas('categories', function ($q) use ($category) {
$q->where('categories.id', $category)
->orWhere('categories.category_id', $category);
});
}
Its working, but its too slow. Any tips on improving this query permormance?
Level 122
Create an index for the category_id column.
2 likes
Level 7
I think i can not cache. I'm passing the ::query() for the Laravel DataTables package and I can not serialize the query.
public function datatable(Category $category = null)
{
$query = isset($category)
? Provider::category($category->id)->with('categories.children', 'tags', 'distributors')
: Provider::with('categories.children', 'tags', 'distributors');
return DataTables::eloquent($query)
...
Level 7
Its used in a many to many polymorph table, i think i can not do that
table: categorizables
id|category_id(not unique)|categorizable_id|categorizable_Type
Level 7
For anyone having the same performance issue, i post here the solution i found in とも-san article.
// Old
public function scopeCategoryOld($query, $category)
{
return $query->whereHas('categories', function ($q) use ($category) {
$q->where('categories.id', $category);
});
}
// New
public function scopeCategoryNew($query, $category)
{
return $query->join('categorizables', 'providers.id', 'categorizables.categorizable_id')
->where('categorizables.category_id', $category)
->select('providers.*')
->distinct();
}
Basically its joining from the polymorph table and keeping the distinct records.
1 like
Please or to participate in this conversation.