How do I find the name and its count for a FK object?

Question

Having these models:

class AccidentJob(models.Model):
    name = models.CharField(max_length=50)

class Accident(TimeStampedModel, models.Model):
    job = models.ForeignKey(AccidentJob, on_delete=models.CASCADE)

I want something like this:

[
    {'job__name': 'Auxiliar de surtido', 'count': 2},
    {'job__name': 'Técnico de mantenimiento', 'count': 1}
]

but I am getting this:

[
    {'job__name': 'Auxiliar de surtido', 'count': 1},
    {'job__name': 'Auxiliar de surtido', 'count': 1},
    {'job__name': 'Técnico de mantenimiento', 'count': 1}
]

This is my query:

from django.db.models import Count

Accident.objects.values(
    "job__name"
).annotate(
    count=Count('job__name')
).values("job__name", "count")

I can do it with Python, but I would prefer having PostgreSQL do the work.

score 0 · Answer 1 · answered Jun 19 '20 at 02:38

I finally got the answer, looks like it just need an order_by

Accident.objects.values(
    "job__name"
).annotate(
    count=Count('job__name')
).values(
    "job__name", "count"
).order_by(
    "job__name"
)

So after tweaks, this is a better version:

Accident.objects.values(
    "job__name"
).order_by(
    "job__name"
).annotate(
    count=Count('job__name')
)

How do I find the name and its count for a FK object?

1 Answers1