Count and Sum objects from different models

I'm working on my Django project and I'm triying to order my posts by the sum of 2 related models.

So this query should take the attendance count from the Post model and the attendant count from the Attending model and get the total sum.

These are the models:

class Post(models.Model):
    title = models.CharField(max_length=100)
    attendance = models.ManyToManyField(User, related_name='user_event_attendance')

class Attending(models.Model):
    attendant = models.ForeignKey(User, related_name='events_attending', on_delete=models.CASCADE, null=True)
    post = models.ForeignKey('Post', on_delete=models.CASCADE, null=True)

By now I have the following code but is not working properly:

views.py

new_query = Post.objects.filter(
            Q(status='NOT STARTED', post_options='PUBLIC') | Q(status='IN PROGRESS',post_options='PUBLIC')).distinct().annotate(total_attendance=Count('attendance')).annotate(total_attendant=Count("attending__attendant")).annotate(total=F("total_attendant")   F("total_attendance")).order_by('-total')

CodePudding user response：

You are getting duplicates in your counts, because of this documented behaviour:

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries.

... For most aggregates, there is no way to avoid this problem, however, the Count aggregate has a distinct parameter that may help.

You can resolve this by adding the distinct parameter to both your count annotations:

new_query = Post.objects.filter(
    Q(status='NOT STARTED', post_options='PUBLIC') | Q(status='IN PROGRESS',post_options='PUBLIC')
).distinct().annotate(
    total_attendance=Count('attendance', distinct=True), # Add distinct
    total_attendant=Count("attending__attendant", distinct=True)   # Add distinct
).annotate(
   total=F("total_attendant")   F("total_attendance")
).order_by('-total')

Also, Count("attending__attendant", distinct=True) can be simplified to just Count("attending", distinct=True), since there can only ever be one attendant for each Attending instance.