How to optimize queries in Django Admin?

Question

Django

Hello!
The situation is this. I have an online store project in Django.
models.py

from django.db import models
from mptt.models import MPTTModel, TreeForeignKey

class ProductCategory(MPTTModel):

    title = models.CharField(verbose_name=u'Название категории', max_length=100)
    parent = TreeForeignKey('self', verbose_name=u'Родительская категория', on_delete=models.CASCADE, null=True,
                            blank=True,
                            related_name='product_category_children')
    



class Product(models.Model):
    
    title = models.CharField(verbose_name=u'Название товара', max_length=100, )
    description = models.TextField(verbose_name=u'Подробное описание товара', null=True, blank=True)
    price = models.DecimalField(verbose_name=u'Цена товара', max_digits=10, decimal_places=5)
    category = models.ForeignKey(ProductCategory, verbose_name=u'Категория товара', related_name='product_category', on_delete=models.CASCADE, default=None)



class Attribute(models.Model):

    label = models.CharField(verbose_name=u'Название атрибута', max_length=50)

    def __str__(self):
        return self.label


class AttributeValue(models.Model):

    attribute = models.ForeignKey(Attribute, verbose_name=u'Название атрибута', on_delete=models.CASCADE, related_name='attribute_values')
    value = models.CharField(verbose_name=u'Значение атрибута', max_length=255)


    def __str__(self):
        return '{}: {}'.format(self.attribute.label, self.value)



class CategoryAttributeValue(models.Model):

    category = models.ForeignKey(ProductCategory, verbose_name=u'Категория', on_delete=models.PROTECT, null=True, blank=True)
    attribute_value = models.ForeignKey(AttributeValue, verbose_name=u'Атрибут', on_delete=models.PROTECT, null=True, blank=True)


    def __str__(self):
        return '{}: {}'.format(self.attribute_value.attribute.label, self.attribute_value.value)


class ProductAttributeValue(models.Model):

    product_type = models.ForeignKey(Product, verbose_name=u'Товар', on_delete=models.PROTECT, null=True, blank=True, related_name='product_attributes')
    attribute_value = models.ForeignKey(AttributeValue, verbose_name = u'Атрибут товара', on_delete=models.PROTECT, null=True, blank=True, related_name='product_attributes')

    def __str__(self):
        return '{}: {}'.format(self.attribute_value.attribute.label, self.attribute_value.value)

admin.py

from django.contrib import admin
from mptt.admin import DraggableMPTTAdmin
from .models import *
from django.contrib.admin.options import BaseModelAdmin
from django.db.models.constants import LOOKUP_SEP

class AdminBaseWithSelectRelated(BaseModelAdmin):

    list_select_related = []

    def get_queryset(self, request):
        return super(AdminBaseWithSelectRelated, self).get_queryset(request).select_related(*self.list_select_related)

    def form_apply_select_related(self, form):
        for related_field in self.list_select_related:
            splitted = related_field.split(LOOKUP_SEP)

            if len(splitted) > 1:
                field = splitted[0]
                related = LOOKUP_SEP.join(splitted[1:])
                form.base_fields[field].queryset = form.base_fields[field].queryset.select_related(related)



class AdminInlineWithSelectRelated(admin.TabularInline, AdminBaseWithSelectRelated):


    def get_formset(self, request, obj=None, **kwargs):
        formset = super(AdminInlineWithSelectRelated, self).get_formset(request, obj, **kwargs)
        self.form_apply_select_related(formset.form)
        return formset


class AdminWithSelectRelated(admin.ModelAdmin, AdminBaseWithSelectRelated):

    def get_form(self, request, obj=None, **kwargs):
        form = super(AdminWithSelectRelated, self).get_form(request, obj, **kwargs)
        self.form_apply_select_related(form)
        return form




class CategoryAttributeValueInline(AdminInlineWithSelectRelated):
    model = CategoryAttributeValue
    extra = 0
    list_select_related = ['attribute_value__attribute']
    autocomplete_fields = ('attribute_value',)




class ProductAttributeValueInline(AdminInlineWithSelectRelated):
    model = ProductAttributeValue
    extra = 0
    list_select_related = ['attribute_value__attribute']
    autocomplete_fields = ('attribute_value',)



class AttributeValueAdmin(AdminWithSelectRelated):

    list_select_related = ('attribute',)
    search_fields = ('value',)



class ProductCategoryAdmin(DraggableMPTTAdmin):

    list_select_related = ('parent',)
    inlines = [CategoryAttributeValueInline]




class ProductAdmin(admin.ModelAdmin):
    
    inlines = [ProductAttributeValueInline]


admin.site.register(ProductCategory, ProductCategoryAdmin)
admin.site.register(Product, ProductAdmin)
admin.site.register(Attribute)
admin.site.register(AttributeValue, AttributeValueAdmin)

ProductCategory - product categories.
Product - product model, with some general parameters (name, description, price, etc.). Also has a link to the category.
Attribute model - product feature names (size, color, weight, etc.).
AttributeValue - the values ​​of these characteristics (black, white, 5 kg, 10 kg, etc.). Has a relationship with Attribute.
The CategoryAttributeValue model stores references to ProductCategory and AttributeValue. Needed for the filter. So that when you go to the page of any category, the filter displays the correct characteristics. Products may vary. And their characteristics are also different. For example, t-shirts - color, size. Smartphones have memory, diagonal, etc.
ProductAttributeValue - references to Product and AttributeValue. To select the products themselves by filter.
For editing convenience, CategoryAttributeValue is added to ProductCategory as inline. When requesting a category model for editing, Silk shows unnecessary sql queries to related models.

For good, there should be only 1 request (which is the 4th one from the bottom)

SELECT `catalogue_categoryattributevalue`.`id`,
       `catalogue_categoryattributevalue`.`category_id`,
       `catalogue_categoryattributevalue`.`attribute_value_id`,
       `catalogue_attributevalue`.`id`,
       `catalogue_attributevalue`.`attribute_id`,
       `catalogue_attributevalue`.`value`,
       `catalogue_attribute`.`id`,
       `catalogue_attribute`.`label`
FROM `catalogue_categoryattributevalue`
LEFT OUTER JOIN `catalogue_attributevalue` ON (`catalogue_categoryattributevalue`.`attribute_value_id` = `catalogue_attributevalue`.`id`)
LEFT OUTER JOIN `catalogue_attribute` ON (`catalogue_attributevalue`.`attribute_id` = `catalogue_attribute`.`id`)
WHERE `catalogue_categoryattributevalue`.`category_id` = 2
ORDER BY `catalogue_categoryattributevalue`.`id` ASC

But he makes 6 more separate requests with different IDs (now there are just 6 related objects)

SELECT `catalogue_attributevalue`.`id`,
       `catalogue_attributevalue`.`attribute_id`,
       `catalogue_attributevalue`.`value`,
       `catalogue_attribute`.`id`,
       `catalogue_attribute`.`label`
FROM `catalogue_attributevalue`
INNER JOIN `catalogue_attribute` ON (`catalogue_attributevalue`.`attribute_id` = `catalogue_attribute`.`id`)
WHERE `catalogue_attributevalue`.`id` IN (5)

Classes AdminBaseWithSelectRelated, AdminInlineWithSelectRelated, AdminWithSelectRelated is the solution I found here . Without them, the number of queries could reach up to 100.
When saving the model, there are also many queries

. There are no such problems in the front-end via select_related (). I can assume that in order to reduce requests on save (POST), you need to override the save () method. But from whom, from the model or form ... But where these 6 requests are made is not clear.

Answer 1

ModelAdmin has a get_queryset method, you need to create a child class and you can add an argument there to specify a custom manager, and write managers for the desired models, respectively