Django queries: Count number of objects with FK to model instance

Question

This should be easy but for some reason I'm having trouble finding it. I have the following:

App(models.Model):
    ...

Release(models.Model):
    date = models.DateTimeField()
    App = models.ForeignKey(App)
    ...

How can I query for all App objects that have at least one Release?

I started typing:

App.objects.all().annotate(release_count=Count('??????')).filter(release_count__gt=0)

Which won't work because Count doesn't span relationships, at least as far as I can tell.

BONUS:

Ultimately, I'd also like to be able to sort Apps by latest release date. I'm thinking of caching the latest release date in the app to make this a little easier (and cheaper), and updating it in the Release model's save method, unless of course there is a better way.

Edit:

I'm using Django 1.1 - not averse to migrating to dev in anticipation of 1.2 if there is a compelling reason though.

Ben James · Accepted Answer · 2010-03-18 16:15:44Z

4

You should be able to use:

App.objects.annotate(release_count=Count('release')).filter(release_count__gt=0)\
    .order_by('-release__date')

edited Mar 18, 2010 at 16:15

answered Mar 18, 2010 at 16:07

Ben James

126k27 gold badges196 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Lawlor Over a year ago

Huh, that's exactly what I was going to do, but I was so SURE it wouldn't work that I never tried it in the interpreter.

Dmitry Shevchenko · Accepted Answer · 2010-03-18 16:14:12Z

2

App.objects \
    .annotate(release_count=Count('release')) \
    .(release_count__gt=0) \
    .order_by('-release_count')

For the bonus part, denormalizing date field looks like the only solution at the moment. And it's pretty fast too.

answered Mar 18, 2010 at 16:14

Dmitry Shevchenko

32.5k10 gold badges60 silver badges63 bronze badges

1 Comment

Ben James Over a year ago

It's quite simple to order by the release date without denormalizing. See my answer.

Collectives™ on Stack Overflow

Django queries: Count number of objects with FK to model instance

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related