Django formset , queries for relational field for every form

Question

Models.py

class Material(BaseModelClass):
    material = models.CharField(max_length=25, verbose_name='Material')
    def __str__(self):
        return self.material

class PurOrder(BaseModelClass):
    order_number = models.CharField(max_length=25)

class PurOrderItem(BaseModelClass):
    order = models.ForeignKey(PurOrder, on_delete=models.CASCADE)
    material = models.ForeignKey(Material, on_delete=models.PROTECT)

I created a PurOrder form and PurOrderItem formset

PurOrderForm = modelform_factory(PurOrder, fields=('order_number',))
PurOrderFormset = inlineformset_factory(PurOrder, PurOrderItem,fields=('material',))

Initialized them as follows.

form = PurOrderForm(instance=order_instance)
queryset = order_instance.purorderitem_set.all().select_related('material',)
formset = PurOrderFormset(instance=order_instance, queryset=queryset)

This setup costs me 22 queries if there is 20 PurOrderItem for selected purorder.

• 1 for PurOrder instance,
• 1 for PurOrderItem instance
• 20 for selected materials for those PurOrderItem's.

Think of it, if there is 1000 PurOrderItem

With the provided select_related, it add's material to PurOrderItemselect, but when it comes to display it I think, it query again.

I use django-autocomplete-light, so it saves me from querying all material instances, but it keeps querying selected material, to display it even though I select_related material.

Ideally, I would select PurOrder instance with prefetched purorderitem and related materials, these means 3 queries. Prefetched purorderitem's and material's will be used, when it's their turn.

Please advice me a way to avoid selected choices query.

Note: I try to avoid caching here.

UPDATE

Long time after I created this question and I tried provided solutions. Problem is, formset's forms are not aware of each other. Therefor, provided queryset's selected_related or prefetch_related lookups aren't passed to the formset forms.

Answer 1

You are fine. This code will cost you only 3 queries. As you can see in select_related() documentation:

Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query. This is a performance booster which results in a single more complex query but means later use of foreign-key relationships won’t require database queries.

It means that your code will preform a mysql join and will result a dataset with all of the data. So by that, I can see that your code is pretty good.

I suggest you use some kind of profiling like django-silk to see how many queries are being generated.

Footnote:

As you can see in prefetch_related() documentation, the difference between prefetch_related() and select_related() is in the way they preform the join:

This (prefetch_related) has a similar purpose to select_related, in that both are designed to stop the deluge of database queries that is caused by accessing related objects, but the strategy is quite different.

...

select_related works by creating an SQL join and including the fields of the related object in the SELECT statement. For this reason, select_related gets the related objects in the same database query.

...

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python.

So as long as you need one-to-one relationship, select_related is the most efficient way to query the relationship.