Filter query with result of aggregate

Question

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

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).

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:

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").

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

Answer 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