3 ms·
One question on avoiding giant IN clauses with Django? Say I have a class called Fridge, and a classes called Vegetables and Condiments. Both of these have Ma
by antihero 14y ago
One question on avoiding giant IN clauses with Django?
Say I have a class called Fridge, and a classes called Vegetables and Condiments.
Both of these have ManyToMany relationships between themselves and Fridge.
So something like:
class Fridge(models.Model):
condiments = models.ManyToManyField(Condiments)
vegetables = models.ManyToManyField(Vegetables)
And here we have a QuerySet that represents our white fridges:
qs = Fridges.objects.filter(color='white')
First query:
"Given a list of condiment IDs, get me all the fridges that have ANY of those condiments in them (modifying the original QuerySet).""
Second query:
"Given a list of vegetable IDs, get me all the fridges that have ALL of those vegetables in them (modifying the original QuerySet)."
How on earth would I do that without building a list of fridge IDs and adding an IN clause to my queryset?
Here are solutions that do it with IN clauses:
First query:
condiment_ids = [...] # list of condiment IDs
condiments = Condiment.objects.filter(
id__in=condiment_ids).all()
condiment_fridges = None
for condiment in condiments:
qs = condiment.fridge_set.all()
if not condiment_fridges:
condiment_fridges = qs
else:
condiment_fridges = condiment_fridges | qs
qs = qs.filter(id__in=[l.id for l in condiment_fridges])
Second query:
vegetable_ids = [...] # list of vegetable IDs
vegetables = vegetable.objects.filter(id__in=vegetable_ids).all()
vegetable_fridges = None
for vegetable in vegetables:
qs = vegetable.location_set.all()
if not vegetable_fridges:
vegetable_fridges = qs
else:
vegetable_fridges = vegetable_fridges & qs
qs = qs.filter(id__in=[l.id for l in vegetable_fridges])
These solutions seem horrible and hackish and I was wondering if there was a better way to do them with Django. Something like object HAS these objects or object HAS ALL of these objects.
Should I just post this on StackOverflow instead?
- ashray 14y agoMight be a good idea to take that to stackoverflow. My suggestion though would be to use that little sauce that we can't achieve programmatically yet. Human intelligence. How often do you think you're going to need this data ? How often does it change ? Cache it based on that. Whatever way you do it, queries like this are expensive. If you need it often enough, throw it into memory and invalidate as necessary. More often than not, your cache invalidation will use less lines than what you wrote up there. Also, don't be afraid to drop into raw sql if you really want to do something a certain way. The example you posted above clearly looks like the ORM working against you.
- antihero 14y agoOk, here's my question on StackOverflow: http://stackoverflow.com/questions/10930169/how-do-i-get-djangos-orm-to-check-if-model-has-or-has-all-of-a-certain-relation http://stackoverflow.com/questions/10930169/how-do-i-get-dja...