CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
coderDude
coderDude

Reputation: 904

Annotate returning individual elements rather than grouping

Here are my models: 

    class Submission(models.Model):
         user = models.ForeignKey(to=DefaultUser, on_delete=models.CASCADE)
         total_score = models.DecimalField()

Now I want to get the count of submissions for each user
And here is the query that I am trying

Submission.objects.values('user').annotate(Count('id'))

And the output is:

     {'user': 1, 'id__count': 1}
     {'user': 1, 'id__count': 1}
     {'user': 1, 'id__count': 1}
     {'user': 1, 'id__count': 1}
     {'user': 2, 'id__count': 1}
     {'user': 2, 'id__count': 1}
     {'user': 3, 'id__count': 1}

Whereas the output that I require is:

     {'user': 1, 'id__count': 4}
     {'user': 2, 'id__count': 2}
     {'user': 3, 'id__count': 1}

What am I doing wrong?

Upvotes: 1

Views: 25

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477190

You need to add an .order_by [Django-doc] to force the QuerySet to "fold":

Submission.objects.values('user').annotate(Count('id')).order_by('user')

This behavior is documented in the documentation:

(...)

Fields that are mentioned in the order_by() part of a queryset (or which are used in the default ordering on a model) are used when selecting the output data, even if they are not otherwise specified in the values() call. These extra fields are used to group "like" results together and they can make otherwise identical result rows appear to be separate.

(...)

Note that since , the "default ordering on a model" will no longer be used.

Upvotes: 1

Related Questions