Level 39
What makes you say that it's sub-optimal? Is it slow?
I'm a little confused about the fact that your Blade file is using the Listings model and you're asking about optimizing a query that is structured around a Projects model.
1 like
Jan 8, 2022
16
Level 5
How to optimise query ?
I need following totals and I am using in blade as
@php
$activelistings = App\Models\Listings::where('project_id',$item->id)
->where('status',1)->latest()->count();
$totalinprogress = App\Models\Listings::where('project_id',$item->id)->where('status',1)
->where('dealstatus','inprogress')->latest()->count();
$totalclosed = App\Models\Listings::where('project_id',$item->id)
->where('status',1)->where('dealstatus','closed')->latest()->count();
@endphp
My query in the controller is
$projects = Projects::withCount('listings')
->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);
My projects model with listing relation is :
public function listings(){
return $this->hasMany(Listings::class, 'project_id', 'id');
}
How can I optimise this query ?
Level 5
@lbecket project can have many listings. I am calculating the totals in a loop. So if I could do it in a model or in the controller , the number of queries will be reduced.
Level 39
@FounderStartup In that case, eliminate the loop and calculate the totals in the query. Instead of calculating the totals in blade, do it in your controller.
Level 5
@lbecket That's is what I have requested.
Level 39
@FounderStartup Here is another post about how to sum a field on a related model: https://laracasts.com/discuss/channels/eloquent/sum-on-a-related-model-field
Level 5
@lbecket Kindly understand what I am doing. Projects has one to many relation with listings. But I need sum of three types of listings for a project.
Level 102
Why do you need to optimize anything? The totals queries should be quite fast and there are only 3? Yes you can make them into 1 query, but I doubt it will improve speed that much.
But you should move them to the controller
1 like
Level 5
OK. Actually its in a loop and on a project search page it increases the number of queries. I simply asked for help in writing the query for including these counts. I don't know how to add a count which need to have a 'where' in a query :)
Level 102
@FounderStartup oh so $item is really a project?
Example then. Just add one for each count
$projects = Projects::withCount(['listings as active_listings_count' => function ($query) {
$query->where('status', 1);
}])
Edit : just noticed that I picked the only one without a project id. So maybe you only want this for the others?
1 like
Level 5
Yes $item is project.
Level 5
@Sinnbeck Thanks a ton. Let me try to do to myself. Actually the whole idea is to eager load the query.
Level 102
@FounderStartup my example will get all counts and add them to the respective project
1 like
Level 5
@Sinnbeck I have shared my controller query code. Can you add your code in that for better understanding ? It will be a great help .
Level 102
@FounderStartup I actually did. I just removed the stuff that wasn't new so you could better see what I did. Here I added "in progress". Just add the rest in the same way
$projects = Projects::withCount('listings')
->withCount(['listings as in_progress_count' => function ($query) {
$query->where('status', 1);
}])
->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);
1 like
Level 5
@Sinnbeck Thanks. Let me try.
Level 102
@FounderStartup it should be available in blade as $item->in_progress_count
1 like
Please or to participate in this conversation.