Chris, If I understand your problem, its that you want to do an "and" across multiple rows, which is not a simple query (and I'm not sure how to do in the model API). To be more clear, your Sub_Item table relates through a many-to-many relationship to ObjectItem so the SQL to get all Sub_Items with a "color" of "blue" would look something like this
select * from app_sub_item si join app_sub_item_optionitems mtm on si.id = mtm.sub_item_id join app_optionitem oi on mtm.optionitem_id = oi.id where oi.name = 'color' and oi.value = 'blue' This would return one set of Sub_Items. Likewise, the same query with oi.name = 'size' and oi.value = 'small' would return a different set. You want the intersection of those two sets (intersecting on app_sub_item.id). But I think your query ends up forming a where like this: where oi.name = 'color' and oi.value = 'blue' and oi.name = 'size' and oi.value = 'small' which will always be empty. You can't simply change the middle AND to an OR because the OR could be true for different Sub_Items. You really need something like this: select * from app_sub_item si join app_sub_item_optionitems mtm1 on si.id = mtm1.sub_item_id join app_optionitem oi1 on mtm1.optionitem_id = oi1.id join app_sub_item_optionitems mtm2 on si.id = mtm2.sub_item_id join app_optionitem oi2 on mtm2.optionitem_id = oi2.id where oi1.name = 'color' and oi1.value = 'blue' and oi2.name = 'size' and oi2.value = 'small' Which joins Sub_Item back to OptionItems twice and constrains one join by the color clause and one by the size clause resulting in the intersecation (AND) of the two separate clauses. Its complicated. Its ugly. In a nutshell, its why I hate SQL. And I'm not sure the best way to map it back to Django. This should probably work: s1 = set([obj.id for obj in item1.sub_item_set.filter(options__name__exact="Small")]) s2 = set([obj.id for obj in item1.sub_item_set.filter(options__name__exact="Blue")]) smallAndBlueIDs = s1.intersection(s2) smallAndBlueSubItems = Sub_Item.objects.in_bulk(list(smallAndBlueIDs)) You are just doing some of the work in SQL (the two filter clauses) and then manually intersecting the IDs of the results and reloading the objects by the resulting IDs. But, yuck! And it hits the DB 3 times instead of only once. -Dave --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~----------~----~----~----~------~----~------~--~---
