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.
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.
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.
I recently realized that the problem in a similar case was not as such the formsets, the problem was the {{formset.errors}} in the template, since each formset generated one query for this.