Django queryset - Adding HAVING constraint

django, django-queryset, group-by, having, sum

Solution

I figured this out by adding two binary fields in `DealerContact`: `is_ref` and `is_customer`.

If `type='ref'` then `is_ref=1` and `is_customer=0`. Else if `type='customer'` then `is_ref=0` and `is_customer=1`.

Thus, I am now able to use `annotate(nb_customers=Sum('is_customer'))` and then use `filter(nb_customers=0)`.

The final queryset consists in:

Contact.objects.filter(dealercontact__dealer__pk__in=(265,),  
                       dealercontact__activity='gardening', 
                       date_data_collected__gte=datetime.date(2012,10,1),
                       date_data_collected__lt=datetime.date(2013,10,1))\
               .annotate(nb_customers=Sum('dealercontact__is_customer'))\
               .filter(nb_customers=0)

Problem

I have been using Django for a couple of years now but I am struggling today with adding a `HAVING` constraint to a `GROUP BY`. My queryset is the following: ``` crm_models.Contact.objects\ .filter(dealercontact__dealer__pk__in=(265,), dealercontact__activity='gardening', date_data_collected__gte=datetime.date(2012,10,1), date_data_collected__lt=datetime.date(2013,10,1))\ .annotate(nb_rels=Count('dealercontact')) ``` which gives me the following MySQL query: ``` SELECT * FROM `contact` LEFT OUTER JOIN `dealer_contact` ON (`contact`.`id_contact` = `dealer_contact`.`id_contact`) WHERE (`dealer_contact`.`active` = True AND `dealer_contact`.`activity` = 'gardening' AND `contact`.`date_data_collected` >= '2012-10-01' AND `contact`.`date_data_collected` < '2013-10-01' AND `dealer_contact`.`id_dealer` IN (265)) GROUP BY `contact`.`id_contact` ORDER BY NULL; ``` I would get exactly what I need with this `HAVING` constraint: ``` HAVING SUM(IF(`dealer_contact`.`type`='customer', 1, 0)) = 0 ``` How can I get this fixed with a Django Queryset? I need a queryset in this instance. Here I am using annotate only in order to get the `GROUP BY` on `contact.id_contact`. Edit: My goal is to get the Contacts who have no "customer" relation in dealercontact but have "ref" relation(s) (according to the WHERE clause of course). Models ``` class Contact(models.Model): id_contact = models.AutoField(primary_key=True) title = models.CharField(max_length=255L, blank=True, choices=choices_custom_sort(TITLE_CHOICES)) last_name = models.CharField(max_length=255L, blank=True) first_name = models.CharField(max_length=255L, blank=True) [...] date_data_collected = models.DateField(null=True, db_index=True) class Dealer(models.Model): id_dealer = models.AutoField(primary_key=True) address1 = models.CharField(max_length=45L, blank=True) [...] class DealerContact(Auditable): id_dealer_contact = models.AutoField(primary_key=True) contact = models.ForeignKey(Contact, db_column='id_contact') dealer = models.ForeignKey(Dealer, db_column='id_dealer') activity = models.CharField(max_length=32, choices=choices_custom_sort(ACTIVITIES), db_index=True) type = models.CharField(max_length=32, choices=choices_custom_sort(DEALER_CONTACT_TYPE), db_index=True) ```

Original source