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

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>

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>