php - Laravel eloquent average score on foreign table
Get the solution ↓↓↓I'm trying to fetch some records if a certain condition is met, but am having trouble building the correct query.
Here's my example code:
// $stars_selected = "4"
$brokers = Broker::query()
->with('avgScore'); // example will returns 3.5
->paginate(10)
->withQueryString();
Now, let's say I have another variable present$stars_selected = "4"
, and I only want to fetch records that have aavgScore
greater that 4.0. How would I accomplish this?
I've tried writing it like this:
$brokers = Broker::query()
if ($stars_selected) {
$brokers->with(['avgScore' => function ($query) {
$query->where('avgScore', '>=', floatval($_GET['stars']));
}]);
}
$brokers = $brokers
->paginate(10)
->withQueryString();
But I'm getting an error:Column not found: 1054 Unknown column 'avgScore' in 'where clause'
I also have this i my Broker model.
public function avgScore()
{
return $this->reviews()
->selectRaw('avg(score) as aggregate, broker_id')
->groupBy('broker_id');
}
public function getAvgScoreAttribute()
{
if (!array_key_exists('avgScore', $this->relations)) {
$this->load('avgScore');
}
$relation = $this->getRelation('avgScore')->first();
return ($relation) ? $relation->aggregate : null;
}
Answer
Solution:
I reviewed your code there's nothing wrong. check the name of the column In foreign table I think it should be (aggregate) because you're return it as (aggregate)
$brokers->with(['avgScore' => function ($query) {
$query->where('aggregate', '>=', floatval($_GET['stars']));
}]);
Answer
Solution:
If you're on Laravel 8 you can use thewithAvg
aggregate method, which was introduced recently.
Broker::query()
->withAvg(relation: 'reviews', column: 'score') // results in 'reviews_avg_score' column
->where('reviews_avg_score', '>=', floatval($_GET['stars']))
->paginate()
->withQueryString();
Docs: https://laravel.com/docs/8.x/eloquent-relationships#other-aggregate-functions
Answer
Solution:
It's been awhile since I've coded in Laravel, but does this fix your problem?
$brokers = Broker::query()
->with('avgScore')
->where('avgScore', '>=', floatval($_GET['stars']))
->paginate(10)
->withQueryString();
I'm assuming theBroker::query()
returns a query object, that you can then chain on additional stipulations, so you should be able to include the 'where' clause directly.
Or, for your 'full' example, something like this:
$brokers = Broker::query()->with('avgScore');
if ($stars_selected) {
$brokers = $brokers->where('avgScore', '>=', floatval($_GET['stars']));
}
$brokers = $brokers
->paginate(10)
->withQueryString();
Answer
Solution:
Assuming you have a one to many relationship between brokers and reviews table, What you are trying to achieve can simply be done by using joins:
$stars_selected = '4';
$avg_reviews = DB::table('reviews')
->select(DB::raw("avg(score) as aggregate,broker_id"))
->groupBy('broker_id');
$broker_reviews = Broker::leftJoinSub($avg_reviews,'broker_avg_reviews', function($join){
$join->on('brokers.id','=','broker_avg_reviews.broker_id');
})->where('aggregate','>=',$stars_selected);
$brokers = $broker_reviews->paginate(10);
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: to enable extensions, verify that they are enabled in your .ini files
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.