Django - show in template related class count filtered by parameter

Question

I will give my models first and then write description.

class Entry(models.Model):
    entry_text = models.TextField()

class Category(models.Model):
    user = models.ForeignKey(User)
    category_text = models.CharField(max_length=200)
    entries = models.ManyToManyField(Entry, through='CategoryEntry')

class CategoryEntry(models.Model):
    category = models.ForeignKey(Category)
    entry = models.ForeignKey(Entry)
    viewed = models.BooleanField(default=False)

So I have Entry model and Category model, and I have created intermediate model CategoryEntry as descriebed here https://docs.djangoproject.com/en/1.7/topics/db/models/#extra-fields-on-many-to-many-relationships because I need one extra field "viewed" (marked as True when user for the first time opens specific Entry link).

So I have created generic.ListView view, where I show all these categories that user has created for himself. What I want, is to show next to every category name, how many entries there are and how many entries he hasn't viewed yet. Like:

Category   Total   Not_viewed
AAA        126     5
BBB        17      15

I have managed to show total entries in template by

{% for category in categories %}
    {{ category.text }}
    {{ category.entries.count }}
{% endfor %}

In my view I have get_queryset like

def get_queryset(self):
    categories = Category.objects.filter(user=self.request.user.id)[:]

    return categories

As I understand, then the best way would somehow add this extra info about every categories entries viewed count in get_queryset. I have searched around but didn't found anything what works. Have tried some things with select_related, prefetch_related, annotate but don't get whats the right way to do this. Know that it's not right, but tried something like that and some other things.

categories = Category.objects.filter(user=self.request.user.id).select_related('categoryentry').filter(categoryentry__viewed=False).count()
categories = Category.objects.filter(user=self.request.user.id).annotate(not_viewed_count=Count('categoryentry')).filter(not_viewed_count__viewed=False)

Hope you get my idea what I wan't to achieve.

Answer 1

At end I came up with this solution:

categories = Category.objects.filter(user=self.request.user.id).extra(select = {
          "num_not_viewed" : """
          SELECT COUNT(*)
          FROM app_categoryentry
          WHERE app_categoryentry.category_id = app_category.id
          AND app_categoryentry.viewed = %d """ % 0,
        })

Based on the solution from this resource http://timmyomahony.com/blog/filtering-annotations-django/

If anyone have other solution how the get the same result with only Django ORM, I would like to know.