django count ids in every subgroup

Question

I have models of subjects, topics and questions structured as following:

class Subject(models.Model):
    subject = models.CharField(max_length=200, unique=True)        
class Topic(models.Model):
    subject = models.ForeignKey(Subject, on_delete=models.CASCADE)
    topic = models.CharField(max_length=200, blank=False)
class Question(models.Model):
    subject = models.ForeignKey(Subject, on_delete=models.CASCADE,                                
    topic = ChainedForeignKey(
        "Topic",
        chained_field="subject",
        chained_model_field="subject",
        show_all=False,
        auto_choose=True,
    )

Now I want to create a query that will count ids for each subject, topic. viz:

╔═════════╤═══════╤═══════╗
║ subject │ topic │ count ║
╠═════════╪═══════╪═══════╣
║ 1       │ 1     │ 10    ║
╟─────────┼───────┼───────╢
║ 1       │ 2     │ 11    ║
╟─────────┼───────┼───────╢
║ 2       │ 3     │ 5     ║
╟─────────┼───────┼───────╢
║ 2       │ 4     │ 4     ║
╚═════════╧═══════╧═══════╝

So far tried the following:

numberq=Question.objects.all().values('subject','topic').annotate(TotalQuestions=Count('topic'))
numberq=Question.objects.all().values('subject','topic','id').order_by('subject','topic')        
numberq=df.groupby(['subject','topic'])['id'].count()#.size()

Unfortunately nothing yielding expected results. Can someone please guide? As a bonus 🙃 I also want to replace the subject_id and topic_id by the respective name fields.

Answer 1

from django.db.models import Count, F

result = Question.objects.values('subject__subject', 'topic__topic')\
                     .annotate(count=Count('id'))\
                     .annotate(subject_name=F('subject__subject'), 
topic_name=F('topic__topic'))\
                     .order_by('subject__subject', 'topic__topic')

Here you can use F function to reference the subject and topic fields, and aliases them as subject_name and topic_name in the result.