Django ORM, MPTT - Getting the amount of the sum of the number of children?

Question

Django

Good afternoon!
There was such a problem that it is necessary to hide those categories in which there are no blogs in the categories. But when creating a category, I use the MPTT tree structure model. The problem is that the ancestor category will not have blog posts, but its descendants will, and in this case the ancestor category entry should be shown. And if it has no descendants, then hide it, or if the descendants have no blog entries, also hide it. That is, you need to find out at each stage the sum of the blogs of the descendants and the category itself, if it turns out to be less than zero, then do not show it, and apply this rule to all descendants. That is, I want to achieve something that would hide the tree if it has no blog entries at all, and there are no blog entries in its descendants.
I am attaching the code:
models.py

class BlogCategories(MPTTModel):
    title = models.CharField(max_length=18, unique=True)
    slug = models.SlugField(max_length=18, unique=True)
    parent = TreeForeignKey('self', on_delete=models.CASCADE, null=True, blank=True, related_name='children')

class Blog(models.Model):
    general = 'Общее'
    CHOICES_SHORTCUTS = (
        ('general', general),
        ('about_site_updates', 'Об обновлениях сайта'),
        ('about_the_author', 'Об авторе')
    )
    title = models.CharField(max_length=150, unique=True)
    slug = models.SlugField(max_length=150, unique=True)
    category = TreeForeignKey('BlogCategories', on_delete=models.CASCADE)
    shortcuts = models.CharField(max_length=150, choices=CHOICES_SHORTCUTS, default=general)
    content = models.TextField(blank=True)
    created_at = models.DateField(auto_now_add=True)
    posted_by = models.BooleanField(default=True)

inclusion_tag or views

Import ...

register = template.Library()


@register.inclusion_tag('blog/include_blog/header_container_blog.html')
def get_blog_categories(active_category = None):
    category_list = BlogCategories.objects.annotate(cnt=Count('blog', filter=Q(blog__shortcuts='general', blog__posted_by=True)))

- EVERYTHING I COULD DO, BUT IT IS NECESSARY TO STORE IN CNT THE SUMMER VALUE OF BLOGS OF THE CATEGORY MOST FOR THE NUMBER OF BLOGS OF ITS DESCENDANTS, TO FURTHER JUST REMOVE WHAT WILL BE LESS THAN 0.

return {'category_list': category_list, 'active_category': active_category}

Template

{% load static%}
<nav class="header-blog">
    {% load mptt_tags %}
        <ul>
            {% recursetree category_list %}
            {% if node in active_category.get_ancestors or node == active_category %}
            <li><a class = 'header-blog-link header-blog-active-category' href="{{node.get_absolute_url}}">{% if node.level == 0 %}<i></i>{% endif %}{{ node.title }}</a>
            {% else %}
            <li><a class = 'header-blog-link' href="{{node.get_absolute_url}}">{% if node.level == 0 %}<i></i>{% endif %}{{ node.title }}
            
            {% comment %} 
            - {{node.cnt}} - 
            {%for child in node.get_children %}
                {{child.cnt}} 
            {% endfor %} 
            {% endcomment %}
        
            </a>
            {% endif %}
                {% if not node.is_leaf_node %}
                <ul>
                    {{ children }}
                </ul>
                {% endif %}
            </li>
            {% endrecursetree %}
        </ul>
</nav>

An example of what I want to get:

If there are 0 blogs in self-development, and 3 blogs in financial literacy, then the self-development category must be left.
If there are 0 blogs in self-development and 0 blogs in financial literacy, then the branch should be excluded from displaying to the user, since it makes no sense.
If there are 3 blogs in self-development, and 0 blogs in financial literacy, then remove only financial literacy.

But due to the use of mptt, descendant categories have no restrictions on the number and nesting.

Answer 1

Since the Blog model is not included in the MPTT hierarchy, the task will inevitably lead to an N+1 problem. So either keep a counter of publications in the category, or go down a level and fence something like that .