python - Get count on several filtered subqueries in template

Question

I recently asked for how to get count on filetered subqueries in a template and got an answer here: Get count on filtered subqueries in template

Now I realised that I need this extended as following (which I think is a lot mroe than my first question, therefor creating a new one here):

1. Show articles that doesn't match the tag, but then with 0 as count.
2. Extend to allow counting on different tags.

With the following model:

class Category(models.Model):
    ...

class SubCategory(models.Model):
    category = models.ForeignKey(Category)
    ....

class Tag(models.Model):
    ....

class Article(models.Model)
    sub_category = models.ForeignKey(SubCategory)
    tag = models.ForeignKey(Tag)

I got the following answer (views.py), which will create a num_articles variable that holds the count of number of articles with tag='xyz':

def my_view(request, other, arguments):
    ...
    subcategories = category.sub_category_set.filter(tag__tagname='xyz')  
                            .annotate(num_articles=Count('article__id'))

And the template:

{% for subcategory in subcategories %}
    {{ subcategory.name }} -- {{ subcategory.num_articles }}
{% endfor %}

But now, I want to be able to do something like this, make independent counts on several different tags, so that the count of 'xyz' is not dependent on the count on 'abc', and vice versa:

def my_view(request, other, arguments):
    ...
    subcategories = category.sub_category_set. 
        filter(tag__tagname='xyz').annotate(num_articles_xyz=Count('article__id')) 
        filter(tag__tagname='abc').annotate(num_articles_abc=Count('article__id')) 
        filter(tag__tagname='def').annotate(num_articles_def=Count('article__id')) 
        ...

To use in the template as:

{% for subcategory in subcategories %}
    {{ subcategory.name }} -- {{ subcategory.num_articles_xyz }}<br />
    {{ subcategory.num_articles_abc }} -- {{ subcategory.num_articles_def }}
    ...
{% endfor %}

And also want all the subcategories to be shown, even if there is no match in any of the counts. But, then with a value of 0 for that count.

Answer 1

There is no easy, efficient way to do this with the Django ORM. With the implementation of annotation, there is no way in the ORM to annotate on different filtered sets within the same query. Even if you annotate, filter, and annotate again, all annotations will be on the filtered set at the time of the last annotation. I am not even sure if what you want is feasible in raw SQL code, but I'm not expert on that.

You can fetch all related tags and articles, and do the actual annotation in Django. You must be careful, however: even when using prefetch_related, if you filter on the related set or alter it in any way that actually clones the queryset, you will still execute a database query for each queryset.

The following is a way you can do this. It will also handle any tags that have no articles in that specific subcategory.

subcategories = category.sub_category_set.prefetch_related('tags', 'articles')
for subcategory in subcategories:
    subcategory.num_articles = dict()
    for tag in tags:
        num = len([a for a in subcategory.articles.all() if a.tag_id == tag.id])
        subcategory.num_articles[tag.tag_name] = num

And in your template:

{% for subcategory in subcategories %}
    {{ subcategory.name }} <br />
    {% for k,v in subcategory.num_articles.iteritems %}
        {{ k }} -- {{ v }}
    {% endfor %}
{% endfor %}

This will produce 3 database queries regardless of the amount of tags (4 including the query for category) and will do the rest of the processing in python. Fetching a large dataset from the database can be expensive, though. If you need to work with very large datasets and see performance issues, you might want to take a look how doing some well-optimized queries for each tag compares to doing only a few large queries with data-processing in python.