Prefetch alternative vs Filter directly in template

Question

I am displaying data with following for loops in my template

 {% for soproduct in list_soproduct %}
        {% for bomversion in soproduct.product.material.default_active_bomversions %}
            {% for bom in bomversion.bom_set.all %}
                 {% for production_order in bom.production_order_set.all %}
                  {% endfor %}
             {% endfor %}
         {% endfor %}
    {% endfor %}

Data is coming from following models

class SOproduct(models.Model):
    product = models.ForeignKey(Product, on_delete=models.PROTECT)
    so = models.ForeignKey(SO, on_delete=models.PROTECT)
    quantity = models.DecimalField(max_digits=13, decimal_places=3, default = 0)



class Product(models.Model):
    version = IntegerVersionField( )
    GTIN = models.CharField(max_length=30)
    name = models.CharField(max_length=30)
    description = models.TextField(null=True, blank=True)
    creation_time = models.DateTimeField(auto_now_add=True, blank=True)
    material = models.ForeignKey(Material, on_delete=models.PROTECT)

class BOMVersion(models.Model): 
    name = models.CharField(max_length=200,null=True, blank=True)
    description = models.TextField(null=True, blank=True)
    material =  models.ForeignKey(Material)
    is_active = models.BooleanField(default=False)
    is_default = models.BooleanField(default=False)


class BOMVersion_default_active_Manager(models.Manager):
    def get_queryset(self):
        return super(BOMVersion_default_active, self).get_queryset().filter(is_default=True,is_active=True)

class BOMVersionQuerySet(models.QuerySet):
    def active(self):
        return self.filter(is_active=True)

    def default(self):
        return self.filter(is_default=True)


class BOM(models.Model): 
    bomversion =  models.ForeignKey(BOMVersion)
    material =  models.ForeignKey(Material)
    quantity = models.DecimalField(default =0 ,max_digits=19, decimal_places=3)

class Production_order(models.Model):
    BOM = models.ForeignKey(BOM, on_delete=models.PROTECT)
    soproduct = models.ForeignKey(SOproduct, on_delete=models.PROTECT)
    quantity_order = models.DecimalField(max_digits=19, decimal_places=3)

In view I use following prefetch object

soproduct = SOproduct.objects.select_related('product__material').prefetch_related(
    Prefetch(
        'product__material__bomversion_set',
        queryset=BOMVersion.objects.default().active(),
        to_attr='default_active_bomversions'
    )
)

My problem is that in deepest for loop

for production_order in bom.production_order_set.all

I want to filter by soproduct so i was wondering how can I modify existing prefetch to accommodate this requirement and if it is possible? Or my only option is to filter directly in the template>

(I dont really understand the Prefetch and what I have tried is not working)

Answer 1

Honestly, I'm not sure if this is going to work, but give it a try:

soproduct = SOproduct.objects.select_related('product__material').prefetch_related(
    Prefetch(
        'product__material__bomversion_set',
        queryset=BOMVersion.objects.default().active().prefetch_related(
            Prefetch('bom_set', queryset=BOM.objects.all().prefetch_related('production_order_set'))
        ),
        to_attr='default_active_bomversions'
    )
)

And then in the template, you do a simple if-check.

{% for soproduct in list_soproduct %}
    {% for bomversion in soproduct.product.material.default_active_bomversions %}
        {% for bom in bomversion.bom_set.all %}
             {% for production_order in bom.production_order_set.all %}
                {% if production_order.soproduct == soproduct %}
                    do my staff
                {% endif %}
             {% endfor %}
        {% endfor %}
    {% endfor %}
{% endfor %}

Basically what prefetch_related do is to fetch related data not via SQL JOINs (this is what select_related do), but by doing separate query and then merging the results in python. I think the docs do a pretty good job explaining this.

And by using a custom Prefetch object we can do two things

1) Alter the default queryset which django will use doing prefetch_related (usefull to filter results even more) and

2) Change the attribute into which django will store the prefetched data (by defaults its into the myobject.related_set.all() this is useful when we do additional filtering which changes the contex of the results and related_set.all() won't be appropriate or will be a misleading).