Jeremy Banks Jeremy Banks - 2 months ago 5
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

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.

Comments