How to select many to one to many without hundreds of queries using Django ORM?

Question

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 Tags, and a Product has many AlternatePartNumbers. Tags are a collection of attributes of the Product.

Given the three attributes in a Tag, I want to select the associated Products that match (could be more than one), as well as all of the AlternatePartNumbers 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.

Answer 1

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.