CODEWITHSUNDEEP
pythondjangopython-3.xdjango-querysetdjango-annotate
rurp
rurp

Reputation: 1446

Django annotate, combine multiple related values onto same instance

I have a django app with the following models:

class Person(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)

class Job(models.Model):
    title = models.CharField(max_length=100)

class PersonJob(models.Model):
    person = models.ForeignKey(Person, related_name='person_jobs')
    job = models.ForeignKey(Job, related_name='person_jobs')
    is_active = models.BooleanField()

Multiple Person instances can hold the same job at once. I have a Job queryset and am trying to annotate or through some other method attach the names of each person with that job onto each item in the queryset. I want to be able to loop through the queryset and get those names without doing an additional query for each item. The closest I have gotten is the following:

 qs = Job.objects.all().annotate(first_names='person_jobs__person__first_name')
.annotate(last_names='person_jobs__person__last_name')

This will store the name on the Job instance as I would like; however, if a job has multiple people in it, the queryset will have multiple copies of the same Job in it, each with the name of one person. Instead, I need there to only ever be one instance of a given Job in the queryset, which holds the names of all people in it. I don't care how the values are combined and stored; a list, delimited char field, or really any other standard data type would be fine.

I'm using Django 2.1 and Postgres 10.3. I would strongly prefer to not use any Postgres specific features.

Upvotes: 2

Views: 2642

Answers (1)

ruddra
ruddra

Reputation: 51978

You can use either ArrayAgg or StringAgg:

from django.contrib.postgres.aggregates import ArrayAgg, StringAgg  


Job.objects.all().annotate(first_names=StringAgg('person_jobs__person__first_name', delimiter=',')

Job.objects.all().annotate(people=ArrayAgg('person_jobs__person__first_name'))

Upvotes: 5

Related Questions