Why are annotations multiplied?

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.

You’re getting what’s known as a “Cartesian Product” or “Cross Join” because of the foreign key references from two different tables in the query. The results table within the database is creating a row for every instance of Rating combined with each instance of Views of each as related to Articles.

This topic is touched upon (but not really discussed in detail) at Aggregation | Django documentation | Django.

1 Like