在Laravel ORM中通过投票排序 [英] Order by Votes in Laravel ORM

问题描述

我有3个模型...类别，帖子，投票

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

查看类别时，我显示该类别中所有帖子的索引。所以在我看来，我正在做一些类似 foreach（$ category-> post as $ post）。

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.

推荐答案

您可以通过在 Post

1关系

// 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

---

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;

您可以使用范围：

// 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();

这篇关于在Laravel ORM中通过投票排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！