CODEWITHSUNDEEP
pythondjangopostgresqlorm
N.Balauro
N.Balauro

Reputation: 151

DJANGO: FK field lookups through QuerySet exclude()

Im trying to exclude all objects from a django queryset that have a foreign key in another queryset. I am not having success using exclude() however. Please help.

Here is a snippet from the python shell:

>>> shipped = shipment_detail.objects.all()
>>> shipped
[<shipment_detail: 4>]
>>> fo = fill_order.objects.exclude(product_order__in=shipped)
>>> fo
[<fill_order: 2>]
>>> for x in shipped:
...  x.product_order.id
...
1
>>> fo
[<fill_order: 2>]
>>> for x in fo:
...  x.product_order.id
...
1

I am using django 1.7, python 2.7.7, postgresql 9.3, on debian wheezy.

UPDATE: Realizing i can see the sql django is using. Here it is. Obviously not what im trying to achieve. Looks like i need to explicitly tell django that i mean to reference FK id and not the record id.

SELECT "box_inv_fill_order"."id", "box_inv_fill_order"."product_order_id", 
"box_ inv_fill_order" ."date" 
FROM "box_inv_fill_order" 
WHERE NOT ("box_inv_fill_order"."product_order_id" 
IN (SELECT "box_inv_shipment_detail"."id" FROM "box_inv_shipment_detail"))

Upvotes: 2

Views: 88

Answers (1)

N.Balauro
N.Balauro

Reputation: 151

fo = fill_order.objects.exclude(product_order__in=shipped)

Produces:

SELECT "box_inv_fill_order"."id", "box_inv_fill_order"."product_order_id", 
"box_ inv_fill_order" ."date" 
FROM "box_inv_fill_order" 
WHERE NOT ("box_inv_fill_order"."product_order_id" 
IN (SELECT "box_inv_shipment_detail"."id" FROM "box_inv_shipment_detail"))

This compares product_order_id against shipment_detail_id. Not what i intend!

Solution:

Utlizing @alecxe suggestion of .filter(...__in=[r.product_order for r in shipped]), creates a nested sql SELECT as the IN parameter. 

SELECT "box_inv_fill_order"."id", "box_inv_fill_order"."product_order_id",   "box_inv_fill_order"."date" 
FROM "box_inv_fill_order" 
WHERE NOT ("box_inv_fill_order"."product_order_id" 
IN (4))

Upvotes: 2

Related Questions