How to make a query in Django that counts foreign key objects?

user_17847015 0
Viewed 102

Hi all!

New in Django, and confused, help is appreciated! I'm trying to create a table, 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

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

How to make a complex query, to retrieve info from Appeal model? And place to exact fields of the table above?:(

python-3.x foreign-keys django-views django-models django
2 Answers

You can query with:

from django.db.models import Count, Q

organizations = Organization.objects.annotate(
).annotate(
    total=Count('appeal'),
    total_written=Count('appeal', filter=Q(appeal__appeal_form__form_name='in written form')),
    total_oral=Count('appeal', filter=Q('appeal__appeal_form__form_name='in oral form'))
)

Then we can render the Organizations with:

<table>
<thead>
  <tr><th>Organization</th><th>Total amount of appeals</th><th>Amount of written form appeals</th><th>Amount of oral form appeals</th></tr>
</thead>
<tbody>
{% for organization in organizations %}
  <tr><td>{{ organization.organization_name }}</td><td>{{ organization.total }}</td><td>{{ organization.total_written }}</td><td>{{ organization.total_oral }}</td></tr>
{% endfor %}
</tbody>
</table>
user_675790

I know that my solution isn't the best. But the result is what you were looking for.

views.py:

from django.shortcuts import render
from .models import Appeal, Organization
from django.db.models import Count


def index(request):
    on = Organization.objects.values_list('organization_name').distinct()
    x = Appeal.objects.values(
        "organization__organization_name", "appeal_form__form_name"
    ).annotate(total=Count("appeal_form__form_name"))
    
    out = {}
    for name in on:
        out[name[0]] = {}
    for el in x:
        for k in out.keys():
            if not out[k].get('total'):
                out[k]['total'] = 0 
            if el.get('organization__organization_name') == k:
                out[k].update({el.get('appeal_form__form_name'):el.get('total')})
                out[k]['total']+=el.get('total')
    context = {
        "appeal": out,
    }
    return render(request, "pages/index.html", context)

index.html:

<table>
    <tr>
        <th>Organization</th>
        <th>Appeal Form</th>
        <th>Total amount of appeals </th>
        <th>Amount of written form appeals</th>
        <th>Amount of oral form appeals</th>
    </tr>
{% for k,v in appeal.items %} 
<tr>
    <td> {{ k }} </td>
    {% for form_name ,form_count in v.items %}
    <td align="center">{{ form_count}}</td> 
    {% endfor %} 
</tr> 
{% endfor %}
</table>

enter image description here

user_71063430
Related