sql - Ruby on Rails - 具有连接和顺序的范围

Question

我有 2 个类:

class Solution
  has_many :solution_votes

class SolutionVote
  belongs_to :solution

我的观点是这样的:

    Proposed Solution A - 2 votes up - 5 votes down
    Proposed Solution B - 1 vote up  - 0 votes down
    Proposed Solution C - 0 votes up - 0 votes down
    Proposed Solution D - 7 votes up - 2 votes down
    Proposed Solution E - 3 votes up - 1 vote down
     

我想要的是根据最多的 UP 投票对其进行排序，以便它看起来像这样:

    Proposed Solution D - 7 votes up - 2 votes down
    Proposed Solution E - 3 votes up - 1 vote down
    Proposed Solution A - 2 votes up - 5 votes down
    Proposed Solution B - 1 vote up  - 0 votes down
    Proposed Solution C - 0 votes up - 0 votes down
    

到目前为止我有这个:

 scope :most_votes_up,
  select("solutions.*, count(solution_votes.id) AS votes_count").
      where(['solutions.state = ?', 'Proposed']).
      joins(:solution_votes).
      group("solutions.id").
      order("votes_count DESC")

产生这个输出:

    Proposed Solution D - 7 votes up - 2 votes down
    Proposed Solution A - 2 votes up - 5 votes down
    Proposed Solution E - 3 votes up - 1 vote down
    Proposed Solution B - 1 vote up  - 0 votes down
    

但是...我仍然遇到的问题是:
1. 没有投票的提议方案缺失(上例中的提议方案C在显示列表中缺失)
2. 我如何指定仅对赞成票的计数(现在，它是根据哪个提议的解决方案获得最多票数(赞成票和反对票)来排序的，而不仅仅是赞成票)？

我正在使用 PostGRESQL

Answer 1

1. 使用左联接(而不是默认的内部联接)来包含具有 0 个关联 solution_votes 的解决方案。

2. 您可以只将赞成票计入计数。

这是我修改范围的方式:

select("solutions.*, count(solution_votes.id) AS votes_count").
  joins("left join solution_votes on solution_votes.solution_id = solutions.id").
  where(['solutions.state = ? and solution_votes.direction = ?', 'Proposed', 'up']).
  group("solutions.id").
  order("votes_count DESC")

这对您的列名做出了一些假设，但您应该能够毫不费力地根据您的实际模式对其进行调整。我还在 where 之前放置了 joins - 这在技术上没有什么不同，但这是 SQL 要求的顺序，对我来说它更符合逻辑。

编辑:听起来好像您想将 votes_count 保留为所有选票的计数，但仍按赞成票数排序。我不确定为什么会这样，除非您在返回的 Solution 对象上调用 .votes_count，但这是可能的。为此，您从 count 聚合切换到 sum，然后执行一些操作，将符合条件的记录视为 1，不匹配的记录为 0。两种方式呈现自己，一个 case 表达式，如 sum(case when solution_votes.direction = 'up' then 1 else 0 end)，或进行一些创意转换将 bool 值转换为整数，例如sum(cast(solution_votes.direction = 'up' as integer))。这两个都会起作用——总和将是赞成票的总数，然后您可以在 order 子句中使用它。选择第一个，没有特别的原因，我们最终得到以下修订范围:

select("solutions.*, count(solution_votes.id) AS votes_count, sum(case when solution_votes.direction = 'up' then 1 else 0 end) as up_votes").
  joins("left join solution_votes on solution_votes.solution_id = solutions.id").
  where(['solutions.state = ? and solution_votes.direction = ?', 'Proposed', 'up']).
  group("solutions.id").
  order("up_votes DESC")