I have a database that contains skus schemas, kits, kit_contents and checklists. Here is the query for "Give me all SKUs defined for kitcontent entries defined for set entries defined in checklist 1":
SELECT DISTINCT s.* FROM skus s JOIN kit_contents kc ON kc.sku_id = s.id JOIN kits k ON k.id = kc.kit_id JOIN checklists c ON k.checklist_id = 1;
I use Django and I mostly like ORM because I can express this request:
skus = SKU.objects.filter(kitcontent__kit__checklist_id=1).distinct()
which is such a smooth way to navigate all of these foreign keys. Django ORM produces basically the same thing as SQL written above. The problem is that it is not clear to me how to get all SKUs that are not defined for checklist 1. In the above SQL query, I will do this by replacing "=" with "! =". But Django models do not have an operator is not equal. You should use the exclude () method, which, as you might expect, would look like this:
skus = SKU.objects.filter().exclude(kitcontent__kit__checklist_id=1).distinct()
but Django produces this request, which is not the same:
SELECT distinct s.* FROM skus s WHERE NOT ((skus.id IN (SELECT kc.sku_id FROM kit_contents kc INNER JOIN kits k ON (kc.kit_id = k.id) WHERE (k.checklist_id = 1 AND kc.sku_id IS NOT NULL)) AND skus.id IS NOT NULL))
(I cleared the request to simplify reading and comparing).
I am new to ORM Django and I would like to use it whenever possible. Is there any way to get what I want here?
EDIT:
karthikr gave an answer that does not work for the same reason as the original ORM.exclude () solution does not work: SKU can be in kit_contents in the sets that exist for both checklist_id = 1 and checklist_id = 2. Using a side query , I opened my post using "checklist_id = 1", displayed 34 results using "checklist_id = 2", produces 53 results, and the following query gives 26 results:
SELECT DISTINCT s.* FROM skus s JOIN kit_contents kc ON kc.sku_id = s.id JOIN kits k ON k.id = kc.kit_id JOIN checklists c ON k.checklist_id = 1 JOIN kit_contents kc2 ON kc2.sku_id = s.id JOIN kits k2 ON k2.id = kc2.kit_id JOIN checklists c2 ON k2.checklist_id = 2;
I think this is one of the reasons why people do not seem to find the .exclude () solution to be a reasonable replacement for some kind of not_equals filter - the latter allows you to briefly say what you mean. Presumably, the former may also allow expressing a request, but I am increasingly despairing of such a simple solution.