Annotate number of identical items in manager

Question

Let's say I have following simplified model:

class CurrentInvoices(models.Manager):

    def get_queryset(self):
        qs = super(CurrentInvoices, self).get_queryset()
        current_invoices = qs.order_by('person', '-created_on').distinct('person').values('pk')
        return qs.annotate(invoice_count=models.Count('number')).filter(id__in=current_invoices).order_by('person__last_name')

class Invoice(models.Model):
    created_on = models.DateField()
    person = models.ForeignKey(Person)
    total_amount = models.DecimalField()
    number = models.PositiveSmallIntegerField()

    objects = models.Manager()
    current_invoices = CurrentEDCInvoices()

A Person can have an Invoice with the same number if for some reason the previously generated invoice was wrong. The latest one (highest created_on) is the one that counts.

The trick with .filter(id__in) in the manager is needed to get the results listed by persons last name; this cannot be removed.

Now I'd like to annotate the total count of number. My try annotate(invoice_count=models.Count('number')) always returns 1 even though there are multiple.

What am I doing wrong? Any pointers on how to properly achieve this without hacking around too much and without hitting the DB for every invoice?

atn · Answer 1 · 2016-08-16T13:33:22.150

0

Seems your problem in distinct('person'), which removes duplicates by person field.

Update

To complete your task you should

current_invoices = qs.order_by('person', '-created_on').distinct('person').values('number')
return qs.annotate(invoice_count=models.Count('number')).filter(number__in=current_invoices).order_by('person__last_name')

edited Aug 16 '16 at 13:33

answered Aug 15 '16 at 12:30

atn

904
9
17

But all invoices with the same number are linked to the same patient? – SaeX Aug 15 '16 at 12:38
If you need to exclude invoice duplicates, you should distinct by `pk` – atn Aug 15 '16 at 12:46
Hmm, I want to distinct by person, since I want to have "one line" per person. Also, distinct by pk doesn't make sense since pk is unique? – SaeX Aug 15 '16 at 13:07
Your invoice have unique `pk` and duplicating `number`. Am I right? You want to count duplicating numbers. Your query set for `current_invoices` will return `pk`s of last invoices for each person, i.e. for only one invoice for each person. So the annotate query will go only through this `pk`s and return only `1` count for each number. – atn Aug 15 '16 at 13:46
Correct: unique `pk`, duplicating `number`. Indeed, the problem is that I only get one count. But how to solve this? By removing `distinct('person')` the problem might be gone, but I need only one result per person. – SaeX Aug 15 '16 at 19:04
`I need only one result per person.` what do you mean? You want to calculate the number of invoices (with or w/o duplicates?) per person? Or for each invoice of person the number of duplicating `number`s? – atn Aug 16 '16 at 08:00
My queryset should consist out of 1 result per `person`. So if I loop over all objects, I'd just get the `latest('created_on')` per `person`. However each result should be annotated with the total number of invoices available with the same number for that patient. – SaeX Aug 16 '16 at 08:25
Further more only one `latest('created_on'‌)` per `person`, i.e. if `person` have two different invoices with different `number`, you will get only last one. Well to complete your task you should `current_invoices = ...values('number')` and `...filter(number__in=current_invoices)...` – atn Aug 16 '16 at 13:30

Annotate number of identical items in manager

1 Answers1