Rails ActiveRecord按联接表关联计数排序

问题描述：

我有一个资源模型，该模型可以使用使行为成为现实宝石（ Github页面）。投票系统运行良好，但是我试图显示按每个资源拥有多少个投票排序的页面。

I have a Resource model that can be voted on using the "Acts As Votable" gem (Github page). The voting system works perfectly but I am trying to display pages ordered by how many votes each Resource has.

当前，我的控制器根据标签提取资源，但未排序：

Currently my controller pulls Resources based on tags and aren't ordered:

@resources = Resource.where(language_id: "ruby")

如果我使用单个资源并致电 @ resource.votes.size将返回它具有多少票。但是，投票是另一张桌子，因此我认为需要进行某种形式的加入，但我不确定如何去做。我需要的是一个很好排序的 ActiveRecord 集合，我可以这样显示吗？

If I take an individual resource and call "@resource.votes.size" it will return how many votes it has. However, votes is another table so I think some sort of join needs to be done but I have not sure how to do it. What I need is a nice ordered ActiveRecord collection I can display like this?

书名-19票

Book name - 19 votes

书名-15票

书名-9票

书名-8票

答

尝试以下：

@resources = Resouce.select("resources.*, COUNT(votes.id) vote_count")
                    .joins(:votes)
                    .where(language_id: "ruby")
                    .group("resources.id")
                    .order("vote_count DESC")

@resources.each { |r| puts "#{r.whatever}  #{r.vote_count}" }

包含0的资源投票，使用外部联接。如果下面的示例不起作用，则必须更改joins语句以跨正确的关系进行联接。

To include resources with 0 votes, use an outer join. If the example below doesn't work as is you'll have to alter the joins statement to join across the correct relations.

@resources = Resource.select("resources.*, COUNT(votes.id) vote_count")
                     .joins("LEFT OUTER JOIN votes ON votes.votable_id = resources.id AND votes.votable_type = 'Resource'")
                     .where(language_id: "ruby")
                     .group("resources.id")
                     .order("vote_count DESC")

Rails ActiveRecord按联接表关联计数排序

相关推荐