How to select many to one to many without hundreds of queries using Django ORM?
django, django-models, django-orm, python, query-optimization
Solution
The relationship between Product and AlternatePartNumber is a reverse ForeignKey relationship, so `select_related()` won't work. You need `prefetch_related()`, which is a little less aggressive than `select_related()` but can handle many-to-one relationships.
I haven't used prefetch_related() myself before but if I'm reading the documentation correctly, you need something like `Tag.objects.prefetch_related('product__alternatepartnumber_set').filter...`. If that doesn't work, specify a related_name on the AlternatePartNumber model and use that instead of `alternatepartnumber_set`.
Problem
My database has the following schema: ``` class Product(models.Model): pass class Tag(models.Model): product = models.ForeignKey(Product) attr1 = models.CharField() attr2 = models.CharField() attr3 = models.CharField() class AlternatePartNumber(models.Model): product = models.ForeignKey(Product) ``` In other words, a `Product` has many `Tag`s, and a `Product` has many `AlternatePartNumber`s. `Tag`s are a collection of attributes of the `Product`. Given the three attributes in a `Tag`, I want to select the associated `Product`s that match (could be more than one), as well as all of the `AlternatePartNumber`s of each product. Currently I do this: ``` # views.py results = Tag.objects. filter(attr1=attr1). filter(attr2=attr2). filter(attr3=attr3) # a template {% for result in results %} {% for alternate in result.product.alternatepartnumber_set.all %} {{ alternate.property }} {% endfor %} {% endfor %} ``` This can run thousands of queries, depending on the number of matches. Is there a good way to optimize this? I tried using `Tag.objects.select_related().filter...` and that helped some, but it didn't help enough.