chain filter and exclude on django model with field lookups 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 want a list of the order types that have orders that meet this criteria: (order_date <= today AND processed_time is empty AND status is not blank)

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 isn't the right queryset. I think its actually returning a more narrowed filter (since no records are present) but I'm not sure what. According to this (django reference), because I'm referencing a related model I think the exclude works on the original queryset (not the one from the filter), but I don't get exactly how.

OK, I just thought of this, which I think works, but feels sloppy (Is there a better way?):

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

Answer 1

What's happening is that the exclude() query is messing things up for you. Basically, it's excluding any Order_type that has at least one Order without a status, which is almost certainly not what you want to happen.

The simplest solution in your case is to use order__status__gt='' in you filter() arguments. However, you will also need to append distinct() to the end of your query, because otherwise you'd get a QuerySet with multiple instances of the same Order_type if it has more than one Order that matches 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 a side note, in the qs query you gave at the end of the question, you don't have to say order__id__in=[o.id for o in orders_qs], you can simply use order__in=orders_qs (you still also need the distinct()). So 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()

Addendum (edit):

Here's the actual SQL that Django issues for the above querysets:

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 reveals that the second query is ever so slightly more expensive (cost of 28.99 versus 28.64 with a very small dataset).