Django queryset annotate calculated values from another model

Question

I have 4 models in a hierarchy: analysis, books, category and publisher. In my app you analyze books. The books belong to categories where you can view the average analysis rating of all the books in the category. This average rating (that I call avg-catg-rating) is not stored in the dB, it’s calculated in the view.

Here’s my question: how do I get the average category rating for each category that the publisher has onto a single publisher view? In other words, how do I annotate the avg-catg-rating onto each category so I can display all of them on the publisher’s page?

So do I iterate using the category somehow like this question? If yes, please give a tip on how because I'm not really sure how to go about it. I also tried groupby as suggested by this chap, but I could only get the count of book instances, I couldn't accurately annotate the avg-catg-rating.

To clarify:

Models.py:

class Analysis: 
    title = models.CharField
    content_rating_1 = models.IntegerField(blank=True,
    null=True, default="0")
    content_rating_1_comment = models.TextField(max_length=300, blank=True,
    null=True)
    source_rating_1 = models.IntegerField(blank=True,
    null=True, default="0")
    source_rating_1_comment = models.TextField(max_length=300, blank=True,
    null=True)
    book = models.ForeignKey

class Book:
    publication_date = models.DateField(default=datetime.date.today)
    slug = models.SlugField(allow_unicode=True, unique=False, max_length=160)
    category = models.ForeignKey

class Category:
    title = models.CharField(max_length=150, unique=False, blank=False)
    sub_title = models.CharField(max_length=100, blank=True, null=True)
    publisher = models.ForeignKey

class Publisher: title = models.CharField(max_length=150, unique=False, blank=False)

publisher/views.py:

class ViewPublisher:

def get_context_data(self, **kwargs):
        category_qs = Category.objects.filter(publisher__pk=self.kwargs['pk'])
        avg-catg-rating = Books.objects.annotate(long complex queryset that uses values from the analysis model)
        context = super(ViewPublisher, self).get_context_data(**kwargs)
        context['categories'] = category_qs
        context['category_stats'] = Books.objects.filter(category__pk__in=category_qs)\
        .annotate(.....and now what???)

Thanks for the response. I've added more fields to the models, hopefully, it'll help. — Dt23, Dec 07 '18 at 21:12

score 0 · Accepted Answer · answered Dec 10 '18 at 15:38

I let go of my obsession with doing everything in the view.

The solution is to create a method in your models that you can call in the template. In my case, I created a method in the Category/models.py like so:

class Category:
    title = models.CharField(max_length=150, unique=False, blank=False)
    sub_title = models.CharField(max_length=100, blank=True, null=True)
    publisher = models.ForeignKey

    def sum_catg_avg_rating(self):
    scar = self.books.annotate(avg-catg-rating=(annotate the AVG calculations from the 
    analysis))

    return scar

Then included the category into the context of the publisher:

class ViewPublisher:

def get_context_data(self, **kwargs):
        category_qs = Category.objects.filter(publisher__pk=self.kwargs['pk'])
        context = super(ViewPublisher, self).get_context_data(**kwargs)
        context['categories'] = category_qs

        return context

Now, I can just call it in the template:

{% for catg in categories %}
<h5>{{ catg.title }}</h5>
<p> RATING:<i>{{ catg.sum_catg_avg_rating.avg-catg-rating }}</i></p>

Django queryset annotate calculated values from another model

1 Answers1