Jeremy Banks Jeremy Banks - 1 month ago 3x
Python Question

Filtering for multiple ForeignKey matches using Q objects

I've initialized these models with the following data in an app named

under Django 1.3:

from django.db.models import Model, FloatField, CharField, ForeignKey, Q

class Customer(Model):
name = CharField(max_length=64)

class Order(Model):
customer = ForeignKey(Customer)
order_total = FloatField()

c = Customer(name="John Smith")

Order(customer=c, order_id=9).save()
Order(customer=c, order_id=13).save()

How can I construct a query using
objects to find the Customer that owns order 9 and order 13?

Without using
objects, I can use the
method twice to get the result I want. As you can see, it uses two
s to look up the two foreign keys:

queryset = Customer.objects.filter(order__order_id=9).filter(order__order_id=13)

return HttpResponse("%s\n\n%s" % (queryset, queryset.query), content_type="text/plain")

[<Customer: Customer object>]

SELECT "main_customer"."id", "main_customer"."name"
FROM "main_customer"
INNER JOIN "main_order" ON ("main_customer"."id" = "main_order"."customer_id")
INNER JOIN "main_order" T3 ON ("main_customer"."id" = T3."customer_id")
WHERE ("main_order"."order_id" = 9 AND T3."order_id" = 13 )

I tried to do the same thing using
objects as follows. Instead of understanding that I was referring to two different Orders, one with id 9 and one with id 13, it thinks that I'm looking for a single order with both ids 9 and 13. This is obviously impossible, so it returns no results:

q = Q(order__order_id=9) & Q(order__order_id=13)
queryset = Customer.objects.filter(q)

return HttpResponse("%s\n\n%s" % (queryset, queryset.query), content_type="text/plain")


SELECT "main_customer"."id", "main_customer"."name"
FROM "main_customer"
INNER JOIN "main_order" ON ("main_customer"."id" = "main_order"."customer_id")
WHERE ("main_order"."order_id" = 9 AND "main_order"."order_id" = 13 )

I expected Django's engine to interpret both queries equivalently, but apparently
objects are handled differently. How can I filter an object by multiple foreign key references using
objects, instead of calling
multiple times?


I've found an explanation for this in the Django documentation. The behaviour I observed is the expected behaviour; query terms for multi-valued relationships are treated differently if they're applied within the same call to .filter()/.exclude() than if they're applied within different calls.

If I apply the two query terms within the same .filter() call...

queryset = Customer.objects.filter(Q(order__order_id=9) & Q(order__order_id=13))
# or equivalently: .objects.filter(Q(order__order_id=9), Q(order__order_id=13))

...then it only returns Customers that own any orders which satisfy both constraints, order_id=9 AND order_id=13. The terms must refer to the same Order at any given time.

On the other hand, if I apply the query terms using two different .filter() calls...

queryset = Customer.objects.filter(order__order_id=9).filter(order__order_id=13)

...they do not need to refer to the same Order. This could be seen as a two-step operation: the set of all Customers is filtered down to those owning any Orders satisfying order_id=9. This result set then is furthered filtered to those Customers also owning any Order satisfying order_id=13. It could be the same Order in both cases. It doesn't matter; the steps are isolated from each other.

There may be some way to get this behaviour from Q() objects with a single .filter() call, but it doesn't look like that's the way Django's ORM is meant to be used.