38

I've got a query...

packages = Package.objects.annotate(bid_count=Count('items__bids'))

Which is supposed to give me a list of packages with the number of bids each. It works great if there's only one item in the package, but if there's more it double counts.

Each package consists of 1 or more items. Each bid is placed on 1 or more items within a package. I want to retrieve the number of bids placed on the items within that package.

If there is 1 bid placed on 2 items within a package, presently this will count as 2, I want it to return 1.

I tried Count('items__bids__distinct') but that didn't work. How can I do this?

mpen
  • 272,448
  • 266
  • 850
  • 1,236

1 Answers1

99

I had the same problem and I found the resolution:

packages = Package.objects.annotate(bid_count=Count('items__bids', distinct = True))
Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219
Hey Teacher
  • 1,215
  • 13
  • 17
  • 9
    There's a `distinct` argument?! I've long since forgotten what I was trying to do with this question, I think that project is abandoned...but if it ever comes up again! This will be handy. Thanks! – mpen Dec 31 '11 at 00:36
  • 12
    I wish I could give you +1000 points. Nobody on IRC even had a clue how to answer this question, these kwargs appear to be completely undocumented and the only way to find out they exist is from the source code. – John Feb 08 '12 at 23:04
  • I spent an hour trying different possible solutions to this! I checked the Aggregate definition in `django.db.models.aggregates.py` and saw the signature `__init__(self, lookup, **extra)`, but then I could not find any docs on how to use those named extra arguments, and of course this is the answer! Why the hell is not this documented!? – AJJ Jun 03 '12 at 12:12
  • 2
    what if we have `distinct('id')` then what will we do? – Faiz Hameed Jul 30 '20 at 11:21
  • Is it only me who occasionally gets `NotImplementedError('annotate() + distinct(fields) is not implemented.')` with similar query? – Aleksandr Mikheev Oct 16 '20 at 15:46