Modern methods for filtering a Django annotation?

Question

I'd like to filter an annotation using the Django ORM. A lot of the articles I've found here at SO are fairly dated, targeting Django back in the 1.2 to 1.4 days:

Filtering only on Annotations in Django - This question from 2010 suggests using an extra clause, which isn't recommended by the official Django docs
Django annotation with nested filter - Similar suggestions are provided in this question from 2011.

Django 1.8 adds conditional aggregation, which seems like what I might want, but I can't quite figure out the syntax that I'll eventually need. Here are my models and the scenario I'm trying to reach (I've simplified the models for brevity's sake):

class Project(models.Model):
    name = models.CharField()
    ... snip ...

class Milestone_meta(models.Model):
    name = models.CharField()
    is_cycle = models.BooleanField()

class Milestone(models.Model):
    project = models.ForeignKey('Project')
    meta = models.ForeignKey('Milestone_meta')
    entry_date = models.DateField()

I want to get each Project (with all its fields), along with the Max(entry_date) and Min(entry_date) for each associated Milestone, but only for those Milestone records whose associated Milestone_meta has the is_cycle flag set to True. In other words:

For every Project record, give me the maximum and minimum Milestone entry_dates, but only when the associated Milestone_meta has a given flag set to True.

At the moment, I'm getting a list of projects, then getting the Max and Min Milestones in a loop, resulting in N+1 database hits (which gets slow, as you'd expect):

pqs = Projects.objects.all()

for p in pqs:
    (theMin, theMax) = getMilestoneBounds(p)
    # Use values from p and theMin and theMax

...

def getMilestoneBounds(pid):
    mqs = Milestone.objects.filter(meta__is_cycle=True)
    theData = mqs.aggregate(min_entry=Min('entry_date'),max_entry=Max('entry_date'))

    return (theData['min_entry'], theData['max_entry'])

How can I reduce this to one or two queries?

I would argue that this is definitely a case where extra or even raw sql really should be used. — e4c5, Sep 14 '15 at 04:24
The docs indicate that `extra` will eventually be deprecated. I'd ideally like to use something that's future-proof to some degree. — Jonah Bishop, Sep 14 '15 at 15:34
well then use the more powerfull and and even better raw sql — e4c5, Sep 14 '15 at 23:49

score 0 · Answer 1 · answered Sep 14 '15 at 03:56

As far as I know, you can not get all required project objects in one query.

However, if you don't need the objects and can work with just their id, one way would be-

Milestone.objects.filter(meta__is_cycle=True).values('project').annotate(min_entry=Min('entry_date')).annotate(max_entry=Max('entry_date'))

It will give a list of dicts having data of distinct projects, you can then use their 'id' to lookup the objects when needed.

Modern methods for filtering a Django annotation?

1 Answers1