How to count foreign key objects with annotation in Django?-CodePudding

Hi all!

New in Django, and confused, help is appreciated!

Have three models:

class Organization(models.Model):
    organization_name = models.CharField(max_length=50)


class AppealForm(models.Model):
    form_name = models.CharField(max_length=50)


class Appeal(models.Model):
    organization = models.ForeignKey(Organization, on_delete=models.CASCADE)
    appeal_form = models.ForeignKey(AppealForm, on_delete=models.CASCADE)
    applicant_name = models.CharField(max_length=150)

Objects of Organization model:

organization_name
Organization 1
Organization 2

Objects of AppealForm model:

form_name
In written form
In oral form

Objects of Appeal model:

organization	appeal_form	applicant_name
Organization 1	In written form	First and Last name
Organization 1	In oral form	First and Last name
Organization 1	In oral form	First and Last name
Organization 2	In written form	First and Last name
Organization 2	In oral form	First and Last name

I'm trying to create a table in the template, like:

Organization	Total amount of appeals	Amount of written form appeals	Amount of oral form appeals
Organization 1	3	1	2
Organization 2	2	1	1

The content in the table contents has to be retrieved from Appeal model, that is rendered to the template.

Question: How the query look like in views.py using Appeal model?

CodePudding user response：

i recommend you read about how to do complex query in django from this documents section. Here what your query would be like:

from django.db.models import Count, Case, When, IntegerField

Appeal.objects
      .values('organization')
      .annotate(
          total_appeals=Count('appeal_form'), 
          written_amount=Count(Case(
                When(appeal_form__form_name="In written form", then=1),
                output_field=IntegerField(),
          )),
          oral_amount=Count(Case(
                When(appeal_form__form_name="In oral form", then=1),
                output_field=IntegerField(),
          )),
       ).order_by()

set the above query to a value and loop through it and see the results. You should get something like this

[{'organization': 'Organization 1', 'total_appeals': 3, 'written_amount': 1, 'oral_amount': 2}, {'organization': 'Organization 2', 'total_appeals': 2, 'written_amount': 1, 'oral_amount': 1}]

Explanations:

values to group by organization
Count to count the form in that organization group
we use Case to add an if condition, then=1 to return value(else return null)
the written_amount need to be integer so we set it in the output_field

conditional part in the documents https://docs.djangoproject.com/en/dev/ref/models/conditional-expressions

why you should use order_by() for grouping queries https://docs.djangoproject.com/en/4.0/topics/db/aggregation/#interaction-with-order-by