Search code examples
pythondjangodjango-annotate

Django annotate model and filter for specific value

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)

Solution

  • 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.