Django queryset - Adding HAVING constraint

Question

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)

I am not sure about this, but maybe .extra() can be useful here https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.extra — Jingo, Dec 02 '13 at 14:57
I tried but the annotate + extra gave me the following error: DatabaseError: (1111, 'Invalid use of group function'). The Queryset adds the "SUM(...)" both into the SELECT clause and GROUP BY clause, which is weired to me. Maybe I can use extra without the annotate and still preserve get the GROUP BY? — Q Caron, Dec 02 '13 at 15:20
When you use Django ORM, you are not supposed to think in terms of SQL statements. That is not always possible, hence `.extra()` and `.raw()`, but it would be much easier to give a good answer to your question if you explicitly sated your desired effect. — Ludwik Trammer, Dec 02 '13 at 15:30
Well I do not try to think in terms of SQL but I am trying to adapt what seems to be a weakness of Django Querysets, but I hope I can do it simply and that I am wrong. I got the MySQL query above from a simple Django Queryset built first.I edited my question: My goal is to get the Contacts who have no "customer" relation in dealercontact but have "ref" relation(s). Thanks :) — Q Caron, Dec 02 '13 at 15:45
I edited the original message to add my models. Thank you very much for helping Ludwik!! — Q Caron, Dec 02 '13 at 16:06
I mean dealercontact(s) exist with type="ref" and dealercontact.id_contact = contact.id_contact. — Q Caron, Dec 02 '13 at 16:34

score 9 · Accepted Answer · edited Nov 07 '14 at 22:45

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)

darklow · Answer 2 · 2014-02-25T19:34:57.507

Actually there is a way you can add your own custom HAVING and GROUP BY clauses if you need.

Just use my example with caution - if Django ORM code/paths will change in future Django versions, you will have to update your code too.

Image you have Book and Edition models, where for each book there can be multiple editions and you want to select first US edition date within Book queryset.

Adding custom HAVING and GROUP BY clauses in Django 1.5+:

from django.db.models import Min
from django.db.models.sql.where import ExtraWhere, AND

qs = Book.objects.all()

# Standard annotate
qs = qs.annotate(first_edition_date=Min("edition__date"))

# Custom HAVING clause, to limit annotation by US country only
qs.query.having.add(ExtraWhere(['"app_edition"."country"=%s'], ["US"]), AND)

# Custom GROUP BY clause will be needed too
qs.query.group_by.append(("app_edition", "country"))

ExtraWhere can contain not just fields, but any raw sql conditions and functions too.

It just gives error: 'Query' object has no attribute 'having' — TJL, Feb 01 '17 at 19:22

Ludwik Trammer · Answer 3 · 2013-12-03T11:00:23.600

0

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

This simple query fulfills this requirement:

Contact.objects.filter(dealercontact__type="ref").exclude(dealercontact__type="customer")

Is this enough, or do you need it to do something more?

UPDATE: if your requirement is

Contacts that have a "ref" relations, but do not have "customer" relations with the same dealer

you can do this:

from django.db.models import Q
Contact.objects.filter(Q(dealercontact__type="ref") & ~Q(dealercontact__type="customer"))

edited Dec 03 '13 at 11:00

answered Dec 02 '13 at 19:13

Ludwik Trammer

24,602
6
66
90

Unfortunately no. It seems that Django does not take the filter parameters into account when excluding, so contacts having a dealercontact type='customer' with other dealers for example are excluded. I do not get what I need here. – Q Caron Dec 03 '13 at 08:44
I have a hard time understanding your requirements. That works exactly the way I understood them. This returns contacts that doesn't have even a single "customer" relation with anyone, and at least one "ref" relation with someone. If that's not what you want please clarify. – Ludwik Trammer Dec 03 '13 at 09:10
Is it 'Contacts that have a "ref" relations, but do not have "customer" relations **with the same dealer**'? – Ludwik Trammer Dec 03 '13 at 09:13
Yes, indeed, no customer rel with the given dealer, as for the other parameters given in the filter. – Q Caron Dec 03 '13 at 09:47
I'm afraid I'm still not able to understand what you aim for, so I won't be able to help you with this. Sorry. – Ludwik Trammer Dec 03 '13 at 19:34

score 0 · Answer 4 · answered Dec 02 '13 at 20:00

0

Are you not using raw query just because you want orm object? Using Contact.objects.raw() generate instances similar filter. Refer to https://docs.djangoproject.com/en/dev/topics/db/sql/ for more help.

answered Dec 02 '13 at 20:00

Arpit

953
7
11

Using `.raw()` should be the very last resort. It's defeats the purpose of having an ORM in the first place. – Ludwik Trammer Dec 02 '13 at 21:30

Django queryset - Adding HAVING constraint

Models

4 Answers4

Linked