0

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.

wildd
  • 15
  • 4

2 Answers2

0

In your CategoryEntry model, use related_name in the category field like so:

category = models.ForeignKey(Category, related_name="related_entry_categories")

Now you can use this related name when querying the Category model. For example:

from itertools import chain

categories_not_viewed = Category.objects.filter(user=self.request.user.id, related_entry_categories__viewed=False).annotate(num_not_viewed=Count('related_en‌​try_categories'))
categories_viewed = Category.objects.filter(user=self.request.user.id, related_entry_categories__viewed=True).extra(select={'num_not_viewed': 0})
categories = chain(list(categories_not_viewed), list(categories_viewed))
OrenD
  • 1,751
  • 13
  • 12
  • Hi! Tried your code, but got `Exception Value: object of type 'int' has no len() Exception Location: /home/user/workspace/env/local/lib/python2.7/site-packages/django/core/paginator.py in _get_count, line 77` Also I think that you maybe didn't understand what I wan't to get. I would like to get a list of categories, but with "extra" field what would hold value of how many related entries are in state "viewed=False". – wildd May 27 '15 at 20:58
  • What I could access in template like `category.category_text`, but something like `category.not_viewed_entry_count` - is this even posible with Django ORM? – wildd May 27 '15 at 21:05
  • Could you post the view code you're using? Copy-pasting your model and running my suggested query works. Anyway, if you'd like to add a count field for unviewed elements, you can use annotate. See this documentation page: https://docs.djangoproject.com/en/1.8/topics/db/aggregation/ – OrenD May 27 '15 at 21:43
  • I have looked at aggregation page already, tried some things, but didn't get how I can filter by field "viewed". Tried `categories = Category.objects.filter(user=self.request.user.id).annotate(num_not_viewed=Count('related_entry_categories__viewed=False')` but it seems I cant filter by "False", works only `...annotate(num_not_viewed=Count('related_entry_categories__viewed')` but that just the same total entry count.. I mean, this category have 10 entries, and user have "viewed=True" to 7 of them, then I would like to "num_not_viewed" show 3 (10 total - 7 already viewed) – wildd May 28 '15 at 07:25
  • You're almost there. :) You should use: `Category.objects.filter(user=self.request.user.id, related_entry_categories__viewed=False).annotate(num_not_viewed=Count('related_entry_categories__viewed'))`. I hope this is what you meant. – OrenD May 28 '15 at 07:34
  • Your solution is almost what I need, but this removes the categories where all entries are marked as "viewed=True", I want also these categories to be in the queryset, just with num_not_viewed value 0/None, so that user knows, that below these categories he has nothing new to see. – wildd May 28 '15 at 15:07
  • Ok, I updated the answer. It's a bit of a hack but if I understand you correctly, it should do the trick. – OrenD May 28 '15 at 15:22
  • `.extra(select={'num_not_viewed': 0})` gives (1054, "Unknown column '0' in 'group statement'") But also I checked the result of "categories_viewed" if I return only that as queryset, and it shows every category for every "viewed=True", if category AAA, has 7 entries with "viewed=True", then it returns it 7x times.. don't think that it is really right. Also I found this http://timmyomahony.com/blog/filtering-annotations-django/ and it starts to look like it is not possible without raw SQL, the article is from 2012, and I have Django 1.7.7, so maybe something has changed? – wildd May 28 '15 at 15:44
  • Hmm it doesn't work with `paginate_by = 10` gives `object of type 'itertools.chain' has no len()` but ok, commented that out and now result is like the first part "categories_not_viewed" is ok, but the second part "categories_viewed" shows something like if I have category AAA, it has total 10 entries, 7 viewed, 3 not viewed.. then in the queryset list it gives not "AAA 10 3" but "AAA 10 0, AAA 10 0, AAA 10 0, AAA 10 0, AAA 10 0, AAA 10 0, AAA 10 0" – wildd May 28 '15 at 16:15
0

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.

wildd
  • 15
  • 4