Chain filter and exclude from the django model with field searches that span relationships

Question

I have the following models:

class Order_type(models.Model): description = models.CharField() class Order(models.Model): type= models.ForeignKey(Order_type) order_date = models.DateField(default=datetime.date.today) status = models.CharField() processed_time= models.TimeField() 

I need a list of order types that have orders that meet these criteria: ( order_date <= today AND processed_time empty And status not empty)

I tried:

 qs = Order_type.objects.filter(order__order_date__lte=datetime.date.today(),\ order__processed_time__isnull=True).exclude(order__status='') 

This works for the original list of orders:

 orders_qs = Order.objects.filter(order_date__lte=datetime.date.today(), processed_time__isnull=True) orders_qs = orders_qs.exclude(status='') 

But qs not the right query choice. I think that it actually returns a more narrowed filter (since there are no entries), but I'm not sure what. Accordingly ( django reference ), because I am referring to a related model, I think exclude works on the original request (not the filter), but I don’t understand how exactly.

Ok, I just thought about it, and I think it works, but it feels messy (is there a better way?):

 qs = Order_type.objects.filter(order__id__in=[o.id for o in orders_qs]) 

Answer 1

What happens is that the exclude () request is used for you. Basically, it excludes any Order_type that has at least one Order without status, which is almost certainly not what you want.

The simplest solution in your case is to use order__status__gt='' in your filter () arguments. However, you also need to add distinct() to the end of your query, because otherwise you will get a QuerySet with multiple instances of the same Order_type if it has multiple orders matching the query. This should work:

 qs = Order_type.objects.filter( order__order_date__lte=datetime.date.today(), order__processed_time__isnull=True, order__status__gt='').distinct() 

On the side of the note, in the qs request that you gave at the end of the question, you do not need to say order__id__in=[o.id for o in orders_qs] , you can just use order__in=orders_qs (you will also need distinct() ). This will also work:

 qs = Order_type.objects.filter(order__in=Order.objects.filter( order_date__lte=datetime.date.today(), processed_time__isnull=True).exclude(status='')).distinct() 

Adding (change):

Here is the actual SQL that Django produces for the above queries:

 SELECT DISTINCT "testapp_order_type"."id", "testapp_order_type"."description" FROM "testapp_order_type" LEFT OUTER JOIN "testapp_order" ON ("testapp_order_type"."id" = "testapp_order"."type_id") WHERE ("testapp_order"."order_date" <= E'2010-07-18' AND "testapp_order"."processed_time" IS NULL AND "testapp_order"."status" > E'' ); SELECT DISTINCT "testapp_order_type"."id", "testapp_order_type"."description" FROM "testapp_order_type" INNER JOIN "testapp_order" ON ("testapp_order_type"."id" = "testapp_order"."type_id") WHERE "testapp_order"."id" IN (SELECT U0."id" FROM "testapp_order" U0 WHERE (U0."order_date" <= E'2010-07-18' AND U0."processed_time" IS NULL AND NOT (U0."status" = E'' ))); 

EXPLAIN shows that the second query is always a bit more expensive (cost 28.99 versus 28.64 with a very small data set).