I am trying to make two annotations for the objects of the Articles model: the number of views (Views model) and the rating (Rating model)
Articles:
class Articles(models.Model):
title = models.CharField(max_length=120)
body = models.TextField(blank=True)
create_datetime = models.DateTimeField()
pub_datetime = models.DateTimeField(
blank=True,
null=True
)
lasted_datetime = models.DateTimeField(
blank=True,
null=True
)
author = models.ForeignKey(
User,
on_delete=models.SET_NULL,
null=True,
verbose_name='Author'
)
tags = models.TextField(blank=True)
status = models.CharField(max_length=24)
objects = models.Manager()
def __str__(self):
return self.title
class Meta:
verbose_name = 'Article'
verbose_name_plural = 'Articles'
permissions = (
("restore_articles", "Can restore Articles"),
("publish_articles", "Can publish drafts of Articles"),
("view_published", "Can view published Articles"),
("view_draft", "Can view drafts of Articles"),
("view_deleted", "Can view deleted Articles"),
)
Views:
class Views(models.Model):
article = models.ForeignKey(
Articles,
on_delete=models.SET_NULL,
null=True,
verbose_name='Article',
related_name='Views'
)
user = models.ForeignKey(
User,
on_delete=models.SET_NULL,
blank=True,
null=True,
verbose_name='User'
)
user_ip = models.CharField(
max_length=16,
blank=True,
null=True
)
view_datetime = models.DateTimeField(
blank=True,
null=True
)
view_type = models.CharField(
max_length=24,
default='Default'
)
view_weight = models.IntegerField(default=1)
objects = models.Manager()
def __str__(self):
return self.view_datetime
class Meta:
verbose_name = 'Views'
verbose_name_plural = 'Views'
Rating:
class Rating(models.Model):
article = models.ForeignKey(
Articles,
on_delete=models.PROTECT,
verbose_name='Article',
related_name='Rating'
)
user = models.ForeignKey(
User,
on_delete=models.SET_NULL,
blank=True,
null=True,
verbose_name='User',
related_name='Given_rating'
)
rating_datetime = models.DateTimeField(
blank=True,
null=True
)
rating_type = models.CharField(
max_length=24,
default='Default'
)
rating_weight = models.IntegerField(default=1)
status = models.CharField(
max_length=24,
default='Active'
)
objects = models.Manager()
def __str__(self):
return str(self.article) + ": " + "{:+}".format(self.rating_weight)
class Meta:
verbose_name = 'Rating'
verbose_name_plural = 'Rating'
permissions = (
("give_rating", "Can give rating to articles"),
("manage_rating", "Can manage rating of articles")
)
unique_together = ('article', 'user')
The aggregation itself takes place here:
def stdict(request):
article_rating = Coalesce(Sum('Rating__rating_weight'), 0)
poparts = Articles.objects.annotate(
article_rating=article_rating
).annotate(
views_num=Coalesce(
Sum(
'Views__view_weight',
filter=Q(Views__view_datetime__gte=(
datetime.datetime.now() + datetime.timedelta(days=-7)
).strftime("%Y-%m-%d %H:%M:%S"))
),
0
)
).order_by('-views_num')[:3]
toparts = Articles.objects.annotate(
article_rating=article_rating
).annotate(
views_num=Coalesce(
Sum('Views__view_weight'),
0
)
).order_by('-article_rating')[:3]
return {
'user_data': request.user,
'sidebar_data': {
'pop_arts': poparts,
'top_arts': toparts
}
}
Everything is output in the end like this:
<div class="side_articles">
<h6>Articles popular this week:</h6>
{% for post in sidebar_data.pop_arts %}
<div id="pop-article{{post.id}}">
<b class="article_title"><a href="/news/{{post.id}}">{{post.title}}</a></b>
<h6 class="article_views">Views: {{post.views_num}}</h6><br>
<h6 class="article_datetime">{{post.pub_datetime|date:"d.m.Y H:i"}}</h6>{% if post.lasted_datetime != NULL %} <span class="btn-link article_lasted_datetime" title="{{post.lasted_datetime|date:'d.m.Y H:i'}}">(ed.)</span> {% endif %}
<h6 class="article_rating">Rating: {{post.article_rating}}</h6><br>
<h6 class="article_author"><a href="/users/{{post.author}}"><u>{{post.author}}</u></a></h6>
<p class="article_tags">{{post.tags}}</p>
<hr>
</div>
{% endfor %}
</div>
<div class="side_articles">
<h6>Articles with the highest rating:</h6>
{% for post in sidebar_data.top_arts %}
<div id="top-article{{post.id}}">
<b class="article_title"><a href="/news/{{post.id}}">{{post.title}}</a></b>
<h6 class="article_views">Views: {{post.views_num}}</h6><br>
<h6 class="article_datetime">{{post.pub_datetime|date:"d.m.Y H:i"}}</h6>{% if post.lasted_datetime != NULL %} <span class="btn-link article_lasted_datetime" title="{{post.lasted_datetime|date:'d.m.Y H:i'}}">(ed.)</span> {% endif %}
<h6 class="article_rating">Rating: {{post.article_rating}}</h6><br>
<h6 class="article_author"><a href="/users/{{post.author}}"><u>{{post.author}}</u></a></h6>
<p class="article_tags">{{post.tags}}</p>
<hr>
</div>
{% endfor %}
</div>
Total: the rating is multiplied by the number of views (for all time) and the rating, in the views I did not understand by what principle the number is obtained. In the upper block, the number of views is displayed for the last week, in the lower block - for all the time (and it should be so), the rating in both blocks - for all the time.
Result: (red is what it should be)
P.S. If I remove the views_num annotation, then the rating starts to be displayed correctly. The problem is precisely in the use of two annotations. If I do not prescribe them sequentially, but inside one comma-separated annotation, nothing changes - they are multiplied in the same way.