Good day! Tell me pliz. There are sections of the catalog, 3 levels of nesting, goods are attached to sections, it is necessary for each section to output the number of goods located in it, taking into account the subsidiary subsections. The number of products varies depending on the filter, for example, if a specific brand or price range is indicated.

I read about the aggregation but did not understand how to apply it to MPTT

How can this be done by means of Django orm, and in the future there will be about a million products in the database, how to do it optimally with a reserve of such quantity.

The base is used by Postgresql-9.5, it all works on Centos 7, Python 3, Django 1.10.2

Below are the models of sections and products, as well as the code from the template

class Category(MPTTModel): name = models.CharField('Π Π°Π·Π΄Π΅Π»', max_length=100) parent = TreeForeignKey('self', null=True, blank=True, related_name='children', db_index=True) class MPTTMeta: order_insertion_by = ['sort'] class Meta: verbose_name = 'Π Π°Π·Π΄Π΅Π»' verbose_name_plural = 'Π Π°Π·Π΄Π΅Π»Ρ‹' def __str__(self): return '%s%s' % ('- ' * self.level, self.name) class Product(models.Model): parent = models.ForeignKey(Category) name = models.CharField('НазваниС', max_length=100) price = models.IntegerField('Π¦Π΅Π½Π°') size = models.ManyToManyField(Size) color = models.ManyToManyField(Color) vendor = models.ForeignKey(Brand, blank=True) shop = models.ForeignKey(Company, blank=True) class Meta: verbose_name = 'Π’ΠΎΠ²Π°Ρ€' verbose_name_plural = 'Π’ΠΎΠ²Π°Ρ€Ρ‹' def __str__(self): return self.name def product_list(request, category_slug=False): category = None object_list = Product.objects.all() if category_slug: category = get_object_or_404(Category, slug=category_slug) object_list = object_list.filter(parent__in=category.get_descendants(include_self=True)) paginator = Paginator(object_list, 3) page = request.GET.get('page') sections = Category.objects.all() try: products = paginator.page(page) except PageNotAnInteger: products = paginator.page(1) except EmptyPage: products = paginator.page(paginator.num_pages) return render( request, 'catalog/product_list.html', { 'page': page, 'category': category, 'sections': sections, 'products': products } ) {% load mptt_tags %} <ul> {% recursetree sections %} <li{% if node == category %} class="active"{% endif %}> <a href="/catalog/{{ node.slug }}/">{{ node.name }}</a> {% if not node.is_leaf_node %} <ul> {{ children }} </ul> {% endif %} </li> {% endrecursetree %} </ul>

I want to get something like

 <a href="/catalog/{{ node.slug }}/">{{ node.name }} ({{ node.cnt }})</a>

    1 answer 1

     #ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌ список ΠΈΠ΄Π΅Π½Ρ‚ΠΈΡ„ΠΈΠΊΡ‚ΠΎΡ€ΠΎΠ² всСх Π½ΠΈΠ·Π»Π΅ΠΆΠ°Ρ‰ΠΈΡ… ΠΊΠ°Ρ‚Π΅Π³ΠΎΡ€ΠΈΠΉ, Π²ΠΊΠ»ΡŽΡ‡Π°Ρ ΠΈΠ½Ρ‚Π΅Ρ€Π΅ΡΡƒΡŽΡ‰ΡƒΡŽ нас ids = Category.objects.get(pk=category_id).get_descendants(include_self=True).values_list('id') #ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌ количСство Ρ‚ΠΎΠ²Π°Ρ€ΠΎΠ², ΠΈΠΌΠ΅ΡŽΡ‰ΠΈΡ… Ρ€ΠΎΠ΄ΠΈΡ‚Π΅Π»Π΅ΠΌ ΠΊΠ°Ρ‚Π΅Π³ΠΎΡ€ΠΈΡŽ с ΠΈΠ΄Π΅Π½Ρ‚ΠΈΡ„ΠΈΠΊΠ°Ρ‚ΠΎΡ€ΠΎΠΌ входящим Π² список ΠΏΠΎΠ»ΡƒΡ‡Π΅Π½Π½Ρ‹ΠΉ строкой Π²Ρ‹ΡˆΠ΅ product_count = Product.objects.filter(parent_id__in=ids).count()

    UPDATE:

    In the previous month I had to work closely with SQL, which allowed me to pump my knowledge, and I was able to write a query returning a list of categories with the number of products belonging to this category or any of its subsidiaries.

     SELECT name, cp.p_count FROM catalog_category AS cc INNER JOIN LATERAL ( SELECT cc.id AS id, SUM(products) AS p_count FROM ( SELECT cc.id AS parent_id, category_id, COUNT(id) AS products FROM catalog_product WHERE category_id IN ( SELECT id FROM catalog_category WHERE lft <= cc.rght AND lft >= cc.lft AND tree_id = cc.tree_id) GROUP BY category_id ) AS sub_cс GROUP BY parent_id ) AS cp USING(id) ORDER BY name;
    • Add comments to the code, what you did, how and why. - Kromster
    • @Kromster but it's elementary. - Sergey Gornostaev
    • Ok .. so I can get the quantity of goods for a specific section, in views.py, I select all categories sections = Category.objects.all () Do I need to put the above example into a chtoli cycle? for s in sections: ... - wedoca
    • Two requests for the number of categories are not very optimal. But I am afraid that there is no method to make such a sample more optimal. If you only transfer the load to the step of writing data to the database: add a field to the Category for storing the quantity of goods in the child groups and recalculate this field when adding new products to the database. - Sergey Gornostaev
    • It would work if it were necessary to simply display the number of products for each section as static data, and in my task it is assumed that this figure (the number of products with all subsections) will vary from the applied filter. Considering all the above, is it reasonable for these purposes to use pure Sql query (s) to get the desired result and ensure maximum performance? - wedoca