Django annotate model and filter for specific value

Question

I'm trying to get into djangos annotate, but can't quite figure out how it works exactly.

I've got a function where I'd like to annotate a queryset of customers, filter them and return the number of customers

def my_func(self):
    received_signatures = self.customer_set.annotate(Count('registrations').filter().count()

Now for the filter part, thats where I have a problem figuring out how to do that. The thing I'd like to filter for is my received_signatures, which is a function that is being called in my customer.py

def received_signatures(self):
    signatures = [reg.brought_signature for reg in self.registrations.all() if reg.status == '1_YES']
    if len(signatures): 
        return all(signatures)
    else:
       return None

brough_signature is a DB Field

So how can I annotate the queryset, filter for the received_signatures and then return a number?

Relevant Model Information:

class Customer(models.Model):
    brought_signature = models.BooleanField(u'Brought Signature', default=False)


class Registration(models.Model):
    brought_signature = models.BooleanField(u'Brought Signature', default=False)
    status = models.CharField(u'Status', max_length=10, choices=STATUS_CHOICES, default='4_RECEIVED')

Note: A participant and a registration can have brought_signature. I have a setting in my program which allows me to either A) mark only brought_signature at my participant (which mean he brought the signature for ALL his registrations) or B) mark brought_signature for every registration he has

For this case Option B) is relevant. With my received_signatures I check if the customer has brought every signature for every registration where his status is "1_YES" and I want to count all the customers who did so and return a number (which I then use in another function for a pygal chart)

But if you *count* the number of `Customer`s, then what is the use of *annotating* these first? — Willem Van Onsem, Aug 06 '18 at 13:23
If I understand it correctly, you want to `Count` the number of `Customer`s that have at least one `Registration`, where `status == '1_YES'` right, and check if all have `brough_signature = 1`? — Willem Van Onsem, Aug 06 '18 at 13:24
Is it possible to (a) share the (relevant) parts of the models, and (b) describe *what* you want, not *how* to do it (the XY problem). — Willem Van Onsem, Aug 06 '18 at 13:27
@WillemVanOnsem Exactly, count every customer with at least one registration, where the status is 1_YES and if they brought all their signatures (some customers can have multiple signatures) I'll update my question — NakedPython, Aug 06 '18 at 13:32
@WillemVanOnsem I added some more info, hope thats better to understand now — NakedPython, Aug 06 '18 at 13:41

score 2 · Accepted Answer · answered Aug 06 '18 at 13:49

If I understand it correctly, you want to check if all the Registrations for a given Customer with status == '1_YES should have as attribute .brought_signature = True, and there should be at least such value. There are several approaches for this.

We can do this by writing it like:

received_signatures = self.customer_set.filter(
    registration__status='1_YES'
).annotate(
    minb=Min('registration__brought_signature')
).filter(
    minb__gt=0
).count()

So what we here do is first .filter(..) on the registrations that have as status 1_YES, next we calculate for every customer a value minb that is the minimum of brought_signature of these Registrations. So in case one of the brought_signatures of the related Registrations is False (in a database that is usually 0), then Min(..) is False as well. In case all brought_signatures are True (in a database that is usually 1), then the result is 1, we can then filter on the fact that minb should thus be greater than 0.

So Customers with no Registration will not be counted, Customers with no Registration with status 1_YES, will not be counted, Customers with Registrations for which there is a Registration with status 1_YES, but with brough_signature will not be counted. Only Customers for which all Registrations that have status 1_YES (not per se all Registrations) have brough_signature = True are counted.

Django annotate model and filter for specific value

1 Answers1