Как сделать запрос в Django, который подсчитывает объекты внешнего ключа?

Привет всем!

Новичок в Django, и запутался, помощь приветствуется! Я пытаюсь создать таблицу, типа:

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

У вас три модели:

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)

Объекты модели Организация:

organization_name
Organization 1
Organization 2

Объекты модели AppealForm:

form_name
In written form
In oral form

Объекты модели Appeal:

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

Как сделать сложный запрос, чтобы получить информацию из модели Appeal? И поместить в точные поля таблицы выше?:(

)

Вы можете сделать запрос с помощью:

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'))
)

Затем мы можем отобразить Organizations с помощью:

<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>

Я знаю, что мое решение не самое лучшее. Но результат - это то, что вы искали.

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 %}

enter image description here

Вернуться на верх