jeudi 5 décembre 2019

How to eager load (one to many) average and sort

I need to get the average rating from 'Reviews' which is one to many with 'Recipes' and be able to sort with this. These are the following that I have tried so far but have had no success with.

// IN RECIPES
public function recipes() {
    return $this->hasMany('reviews');
}

public function avgRating() {
    return $this->reviews()
                ->selectRaw('avg(rating) as rating, recipe_id')
                ->groupBy('recipe_id');
}

public function avgRating() { // ALWAYS RETURNS EAGER LOAD ERROR AS IT'S A FLOAT, SO NOT USED
    return $this->reviews->avg('rating');
}

// IN CONTROLLER
$query = Recipe::select();
$query->withCount('reviews'); // GIVES ME COUNT OF REVIEWS WHICH I NEED

// PROVIDES avgRating: {recipeId, avgRating}, 
// WHICH I DON'T KNOW HOW TO SORT, AND WOULD PREFER AS avgRating: avgRating;
$query->with('avgRating');

// PROVIDES reviews: [{recipe_id, rating}]
// WHICH ISN'T AVERAGE SO NOT EASILY SORTABLE
$query->with('reviews:recipe_id,rating');
$query->paginate(8);

This would be my preferred output (simplified from paginate)

{
   recipes: {
       otherdata: x,
       avgRating: y,
       reviews_count: z,
   }
}

I don't necessarily want to have to append avgRating to this recipe all the time. Would it be possible to have a dynamic append?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire