使用聚合结果筛选查询

时间：2020-12-22 00:09:42

In a Django app, I have four models -- 'Proposal', 'ProposalRevision', 'Opinion', and 'User'. Here are simplified definitions:

在Django应用程序中,我有四个模型 - “Proposal”,“ProposalRevision”,“Opinion”和“User”。以下是简化的定义:

class User(models.Model):
    pass

class Proposal(models.Model):
    pass

class ProposalRevision(models.Model):
    proposal = models.ForeignKey(Proposal)
    created = models.DateTimeField()

class Opinion(models.Model):
    user = models.ForeignKey(User)
    proposal = models.ForeignKey(Proposal)
    created = models.DateTimeField()

Each proposal may have many revisions; and a user may have many opinions for a given proposal. The query I'd like to make is:

每个提案可能有很多修改;并且用户可能对给定提议有很多意见。我想做的查询是:

Retrieve all the proposals where a given user has not left an opinion since the last proposal revision was created.

检索自上次创建提案修订后给定用户未留下意见的所有提案。

The application use-case is that I want to show the user the proposals that they need to leave an opinion on.

应用程序用例是我想向用户显示他们需要留下意见的提议。

Naively, I thought to do the query this way:

天真的,我想这样做的查询:

Proposal.objects.annotate(
        latest_revision=Max('proposalrevision__created')
    ).exclude(
        opinion__user=user,
        opinion__created__lte=F('latest_revision')
    )

However, this doesn't work because F expressions don't work with aggregations (yet).

但是,这不起作用,因为F表达式不适用于聚合(尚未)。

In order to work around this with an "extra" query, I started to try to write vanilla SQL that would do the query I wanted, but I got stuck with using the aggregate in the 'where' clause. The SQL I'm working with looks like this:

为了通过“额外”查询解决这个问题,我开始尝试编写可以执行我想要的查询的vanilla SQL,但是我仍然坚持在'where'子句中使用聚合。我正在使用的SQL看起来像这样:

SELECT ...snip..., MAX("proposal_proposalrevision"."created") as "latest_revision"
FROM "proposal_proposal"
LEFT OUTER JOIN "proposal_proposalrevision" ON
    ("proposal_proposal"."id" = "proposal_proposalrevision"."proposal_id")
WHERE "proposal_proposal"."id" NOT IN (
    SELECT (U1."proposal_id") FROM "proposal_opinion" U1
    WHERE (
        U1."user_id" = 1 AND U1."proposal_id" IS NOT NULL AND
        U1."created" > "latest_proposal"
    )
)
GROUP BY ...snip...

SQLite gives me the error "misuse of aggregate: MAX()". This holds whether I use the the variable name "latest_proposal", or replace it with a repeat of the aggregate function MAX("proposal_proposalrevision"."created").

SQLite给我错误“滥用聚合:MAX()”。这表明我是使用变量名“latest_proposal”,还是将其替换为聚合函数MAX(“proposal_proposalrevision”。“created”)的重复。

How else could I build this query to filter the returned set using the aggregate?

我怎样才能构建此查询以使用聚合过滤返回的集合?

1 个解决方案

#1

1  

Try this solution:

试试这个解决方案

SELECT a.*, b.maxrevisiondate AS latest_revision
FROM proposal_proposal a
LEFT JOIN
(
    SELECT proposal_id, MAX(created) AS maxrevisiondate
    FROM proposal_proposalrevision
    GROUP BY proposal_id
) b ON a.id = b.proposal_id
LEFT JOIN proposal_opinion c ON
    a.id = c.proposal_id AND
    (b.maxrevisiondate IS NULL OR c.created > b.maxrevisiondate) AND
    c.user_id = <user_id here>
WHERE c.proposal_id IS NULL

#1

1  

Try this solution:

试试这个解决方案

SELECT a.*, b.maxrevisiondate AS latest_revision
FROM proposal_proposal a
LEFT JOIN
(
    SELECT proposal_id, MAX(created) AS maxrevisiondate
    FROM proposal_proposalrevision
    GROUP BY proposal_id
) b ON a.id = b.proposal_id
LEFT JOIN proposal_opinion c ON
    a.id = c.proposal_id AND
    (b.maxrevisiondate IS NULL OR c.created > b.maxrevisiondate) AND
    c.user_id = <user_id here>
WHERE c.proposal_id IS NULL
标签：

相关文章