Aggregate over foreign key attributes

Question

I want to know the number of questions that has been asked to a specific user.

Here are my models

class TrueOrFalseQuestion(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    content = models.TextField(default='', blank=True)
    explanation = models.TextField(default='', blank=True)

class TrueOrFalseUserQuestion(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    creator = models.ForeignKey(User, on_delete=models.CASCADE)
    question = models.ForeignKey(TrueOrFalseQuestion, related_name='user_questions', on_delete=models.CASCADE)

Someone creates a question, then when I want to display it for a specific user, I create a TrueOrFalseUserQuestion (and assign the user as creator).

I want to know how many times a question has been displayed to each user. Let's say user with id 1 has 5 TrueOrFalseUserQuestion and user 2 has 3, then I would like to have this result

{"1": 5, "2": 3}

It's easy to do with Python but I think it will give poor performance, is there a way to do that using Django ORM?

Answer 1

Yes, you can use:

from django.db.models import Count

qs = TrueOrFalseUserQuestion.objects.filter(
    question=some_question
).values(
    'creator'
).annotate(
    times=Count('id')
).order_by(
    'creator'
)

With some_question the question for which we want to obtain counts.

This will result in a QuerySet of dictionaries, like:

<QuerySet [{'creator': 1, 'times': 5}]>

We can then use dictionary comprehension, like:

result = {
    q['creator'] : q['times'] for q in qs
}