samedi 28 mai 2016

Sort by average value of a one to many related table column

I have 2 models; Post and Rating

The Rating model contains an amount column which specifies how high something has been rated. This is based on 5 star rating so the amount can be a value from 1-5

The Post model has a one to many relation with the rating model and function called Ratings that returns the hasMany.

I'd like to get the 5 latest posts based on the average rating. For the average rating I've created a function that can be seen below

Note: the plural(Ratings) returns the hasMany relation where as the singular(Rating) returns a value which is the average rating

public function Rating(){
    return floor($this->Ratings()->avg('rating'));
}

Is it possible to retrieve posts ordered by the avg rating using the Eloquent QueryBuilder?

Currently I'm retrieving all posts and then using the sortBy method on the collection object in order get the ones with the highest average rating. The way I'm doing this can be seen below.

$posts = Post::all();

$posts = $posts->sortByDesc(function ($post, $key) {
    return $post->Rating();
});

Now if I'd only want to show 5 I still have to retrieve and sort everything which doesn't seem very resource friendly(In my eyes. I don't have any proof of this or say it is true).

So my question is the following: Is this doable using Eloquent instead of sorting the FULL collection.

Sub question: Will doing this with Eloquent instead of sorting the collection have any impact on efficiency?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire