Ad

How To Count Foreign Key Objects With Annotation In Django?

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:

organizationappeal_formapplicant_name
Organization 1In written formFirst and Last name
Organization 1In oral formFirst and Last name
Organization 1In oral formFirst and Last name
Organization 2In written formFirst and Last name
Organization 2In oral formFirst and Last name

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

OrganizationTotal amount of appealsAmount of written form appealsAmount of oral form appeals
Organization 1312
Organization 2211

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?

Ad

Answer

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

Ad
source: stackoverflow.com
Ad