Django mptt number of products in category and subcategories?

Question

Django

How can I get the number of products in a certain category, including products in child categories?

class Category(MPTTModel):
    name = models.CharField(
        verbose_name='Наименование',
        max_length=128,
        db_index=True
    )
    slug = models.SlugField(
        verbose_name='Человекопонятный URL',
        max_length=255
    )
    a_slug = models.TextField(
        verbose_name='[ Полный ] Человекопонятный URL',
        null=True,
        blank=True,
        db_index=True
    )
    parent = TreeForeignKey(
        'self',
        verbose_name='Родительская категория',
        on_delete=models.CASCADE,
        null=True,
        blank=True,
        related_name='children'
    )
    icon = models.ImageField(
        verbose_name='Иконка',
        upload_to='categories/icons/%Y/%m/%d/',
        validators=[FileExtensionValidator(['png', 'svg'])],
        null=True,
        blank=True,
    )
    output_in_the_footer = models.BooleanField(
        verbose_name='Отображать категорию в подвале сайта?',
        default=False
    )
    updated_at = models.DateTimeField(
        verbose_name='Дата последнего обновления',
        auto_now=True
    )
    created_at = models.DateTimeField(
        verbose_name='Дата создания',
        auto_now_add=True
    )

    _original_name = None
    _original_slug = None

    _is_change_the_a_slug = False

    class Meta:
        verbose_name = 'Категория'
        verbose_name_plural = 'Категории'
        ordering = ('-id',)

        constraints = (
            models.UniqueConstraint(
                fields=('name', 'parent'),
                name='unique_name_by_parent'
            ),
            models.UniqueConstraint(
                fields=('slug', 'parent'),
                name='unique_slug_by_parent'
            ),
        )

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

        self._original_name = self.name
        self._original_slug = self.slug

    def __str__(self):
        return self.name

    def get_absolute_url(self):
        return reverse(viewname='base:catalog', kwargs={'a_slug': self.a_slug})

    def save(self, *args, **kwargs):
        if(
            self._original_name != self.name or
            self._original_slug != self.slug or not self.slug
        ):
            self.slug = slugify(self.name)
            self._original_name = self.name
            self._original_slug = self.slug
            self._is_change_the_a_slug = True

        super().save(*args, **kwargs)

        if self._is_change_the_a_slug:
            if self.is_root_node():
                self.a_slug = self.slug
            else:
                self.a_slug = f'{self.parent.a_slug}/{self.slug}'

            self._is_change_the_a_slug = False
            self.save()

            for child in self.get_children():
                child._is_change_the_a_slug = True
                child.save()

class Product(models.Model):
    name = models.CharField(
        verbose_name='Наименование',
        max_length=128,
        db_index=True
    )
    slug = models.SlugField(
        verbose_name='Человекопонятный URL',
        max_length=255,
        db_index=True
    )
    category = models.ForeignKey(
        'base.Category',
        verbose_name='Категория',
        on_delete=models.CASCADE,
        related_name='products'
    )
    barcode = models.CharField(
        verbose_name='Штрих-код',
        max_length=255,
        unique=True,
        validators=[
            validators.RegexValidator(**settings.REGEX['BARCODE'])]
    )
    price = models.DecimalField(
        verbose_name='Стоимость',
        max_digits=12,
        decimal_places=2,
        validators=[
            validators.MinValueValidator(0)]
    )
    discount = models.PositiveSmallIntegerField(
        verbose_name='Скидка',
        default=0,
        validators=[
            validators.MinValueValidator(0), validators.MaxValueValidator(100)]
    )
    image = models.ImageField(
        verbose_name='Изображение',
        upload_to='products/images/%Y/%m/%d/',
        validators=[
            validators.FileExtensionValidator(['jpg', 'jpeg', 'png'])]
    )
    description = models.TextField(
        verbose_name='Описание',
        max_length=32768
    )
    is_this_a_special_offer = models.BooleanField(
        verbose_name='Это специальное предложение?'
    )
    the_product_is_new = models.BooleanField(
        verbose_name='Товар является новинкой?'
    )
    published_at = models.DateTimeField(
        verbose_name='Дата публикации',
        default=datetime.now
    )
    updated_at = models.DateTimeField(
        verbose_name='Дата последнего обновления',
        auto_now=True
    )
    created_at = models.DateTimeField(
        verbose_name='Дата создания',
        auto_now_add=True
    )

    _original_name = None
    _original_slug = None
    _original_barcode = None

    class Meta:
        verbose_name = 'Товар'
        verbose_name_plural = 'Товары'
        ordering = ('-id',)

        constraints = (
            models.UniqueConstraint(
                fields=('slug', 'category'),
                name='unique_slug_by_category'
            ),
        )

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

        self._original_name = self.name
        self._original_slug = self.slug
        self._original_barcode = self.barcode

    def __str__(self):
        return self.name

    def get_absolute_url(self):
        return reverse(viewname='base:product', kwargs={'slug': self.slug})

Of course, you can do it like this (Example):

category = Category.objects.all().first()

products = Product.objects.filter(
    category__in=category.get_descendants(
        include_self=True
    ).values_list('id', flat=True)
).count()

But in this case, you need to make +1 additional request. For example, 10 categories are displayed on the main page, it turns out that 10 additional requests must be made. Can this be optimized somehow? And how logical would it be if we recalculate the number of goods every N hours and write it to the database?

Answer 1

views:

def get_recursive_product_count(self):
    return Product.objects.filter(category__in=self.get_descendants(include_self=True)).count()

html

<ul>
  {% for child in category.get_children %}
    <li class='subcategory'><a href="/shop/category/{{ child.slug }}">{{ child.name }} ({{ child.get_recursive_product_count }})</a></li>
  {% endfor %}
</ul>