Jeremy Banks Jeremy Banks - 1 year ago 43
Python Question

Filtering for multiple ForeignKey matches using Q objects

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

main
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")
c.save()

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


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

Without using
Q()
objects, I can use the
.filter()
method twice to get the result I want. As you can see, it uses two
JOIN
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
Q()
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
Q()
objects are handled differently. How can I filter an object by multiple foreign key references using
Q()
objects, instead of calling
.filter()
multiple times?

Answer Source

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.