laravel - Sort by average value of an one to many related table column -

i have 2 models; post , rating

the rating model contains amount column specifies how high has been rated. based on 5 star rating amount can value 1-5

the post model has 1 many relation rating model , function called ratings returns hasmany.

i'd 5 latest posts based on average rating. average rating i've created function can seen below

note: plural(ratings) returns hasmany relation singular(rating) returns value average rating

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

is possible retrieve posts ordered avg rating using eloquent querybuilder?

currently i'm retrieving posts , using sortby method on collection object in order ones highest average rating. way i'm doing can seen below.

$posts = post::all();  $posts = $posts->sortbydesc(function ($post, $key) {     return $post->rating(); }); 

now if i'd want show 5 still have retrieve , sort doesn't seem resource friendly(in eyes. don't have proof of or true).

so question following: doable using eloquent instead of sorting full collection.

sub question: doing eloquent instead of sorting collection have impact on efficiency?

you may use query builder

db::table('post')     ->select('post.id', 'avg(rating.amount)')     ->join('rating', 'post.id', '=', 'rating.post_id')     ->groupby('post.id')     ->orderbyraw('avg(rating.amount) desc');