在Laravel ORM中通过投票排序

Question

I have 3 Models... Category, Post, Vote

When viewing a category, I am showing an index of all Posts in that category. So I'm doing something like foreach ($category->posts as $post) in my view.

The question I have now is how can I order the posts based on the sum of votes they have?

I have standard relationships setup, so that a post hasMany votes.

Answer 1

You can do it either by defining a helper relation on the Post model and sorting the collection after the relation is loaded OR simply by joining the votes and ordering in the query.

1 RELATION

// Post model
public function votesSum()
{
  return $this->hasOne('Vote')->selectRaw('post_id, sum(votes) as aggregate')->groupBy('post_id');
}

// then
$category->posts->load('votesSum'); // load relation on the collection

$category->posts->sortByDesc(function ($post) {
    return $post->votesSum->aggregate;
});

// access votes sum like this:
$category->posts->first()->votesSum->aggregate;

---

2 JOIN

$category->load(['posts' => function ($q) {
   $q->leftJoin('votes', 'votes.post_id', '=', 'posts.id')
       ->selectRaw('posts.*, sum(votes.votes) as votesSum')
       ->groupBy('posts.id')
       ->orderBy('votesSum', 'desc');
}]);

// then access votes sum:
$category->posts->first()->votesSum;

You can use scope for that:

// Post model
public function scopeOrderByVotes($query)
{
    $query->leftJoin('comments','comments.post_id','=','posts.id')
      ->selectRaw('posts.*,sum(comments.id) as commentsSum')
      ->groupBy('posts.id')
      ->orderBy('commentsSum','desc');
}

// then
$category = Category::with(['posts' => function ($q) {
    $q->orderByVotes();
}])->whereSlug($slug)->firstOrFail();