SQL查询得到 "vote score"

Question

我的 votes 表如下所示:

id: integer
vote: boolean
voteable_id: integer
voteable_type: string
voter_id: integer
voter_type: string

vote 列确定该行表示“赞成票”(vote = true) 还是“反对票”(vote = false).

voteable_type 是被投票对象的类，voteable_id 是被投票对象的id，voter_type 是类选民的身份，voter_id 是选民的 id。

我需要的是一个查询，以按“投票得分”降序获取前 n 个帖子，其中“投票得分”定义为(帖子的赞成票数)-(帖子的反对票数)。

如果您的解决方案不需要我求助于 find_by_sql()(我在 Rails 中工作)，则加分

如果您的解决方案在 SQLite 和 PostgreSQL 中的工作方式相同(尽管它在 PostgreSQL 中工作更重要)，则会获得更多奖励。

Answer 1

通常，您可以使用 case 来完成此操作带有 sum 的语句:

select
    voteable_id,
    sum(case
        when vote then 1
        else -1
    end) as vote_score
from
    votes
group by
    voteable_id

请注意，这是 ANSI SQL，因此适用于 SQLite、MySQL、Postgres、Oracle、SQL Server、DB2 等。

要获得前 N 个帖子，您只需将其附加到上述查询即可:

order by
    vote_score desc
limit 10

limit 由 Postgres 和 SQLite(在 MySQL 中略有不同)使用，而不是在 Oracle 或 SQL Server 中用作 FYI。

因此，要获取与此相关的帖子信息:

select
    p.title,
    p.author,
    p.createdate,
    sum(case
            when v.vote then 1
            else -1
        end) as vote_score
from
    posts p
    inner join votes v on
        p.post_id = v.voteable_id
group by
    p.title,
    p.author,
    p.createdate
order by
    vote_score desc
limit 10