Skip to content
Advertisement

Django group by Choice Field and COUNT Zeros

Consider the following django model:

class Image(models.Model):
image_filename = models.CharField(max_length=50)

class Rating(models.Model):
DIMENSIONS = [
    ('happy', 'happiness'),
    ('competence', 'competence'),
    ('warm_sincere', 'warm/sincere'),
]
rating_value = models.IntegerField(),
rating_dimension = models.CharField(max_length=50, choices=DIMENSIONS),
image = models.ForeignKey(Image, on_delete=models.CASCADE)

Now, I’d like to group all Ratings by the number of ratings per category like this:

Rating.objects.values("rating_dimension").annotate(num_ratings=Count("rating_value"))

which returns a QuerySets like this:

[{'rating_dimension': 'happy', 'num_ratings': 2},
 {'rating_dimension': 'competence', 'num_ratings': 5}]

Is there a way to include all not-rated dimensions? To achieve an output like:

[{'rating_dimension': 'happy', 'num_ratings': 2},
{'rating_dimension': 'competence', 'num_ratings': 5},
{'rating_dimension': 'warm_sincere', 'num_ratings': 0}] # ← zero occurrences should be included.

Advertisement

Answer

First we will create a dictionary with counts for all dimensions initialised to 0.

results = {dimension[0]: 0 for dimension in Rating.DIMENSIONS}

Next we will query the database:

queryset = Rating.objects.values("rating_dimension").annotate(num_ratings=Count("rating_value"))

Next we will update our results dictionary:

for entry in queryset:
    results.update({entry['rating_dimension']: entry['num_ratings']})

In the template we can iterate over this dictionary by {% for key, value in results.items %}. Or the dictionary can be converted to any suitable structure as per need in the views.

Advertisement