My database has the following schema:

class Product(models.Model):passclass 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.

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.