Django: Annotate list of related fields

Question

I have an Company and User models with a related model CompanyRecruiter:

class CompanyRecruiter(models.Model):

    organization = models.ForeignKey(Company, related_name="company_recruiters")
    recruiter = models.ForeignKey(User, related_name="company_recruiters")

I want to annotate the list of user ids of users who are recruiters for companies to be able to filter on them later:

Company.objects.annotate(some_stuff=some_other_stuff).values_list("user_ids", flat=True)
# [ [1, 2], [1, 56], [] ]

I already tried with Custom Aggregates and Subqueries without success. I use postgres.

MattRowbum · Accepted Answer · 2020-05-19 08:41:49Z

5

If you need output similar to the commented section in your example code, you should probably query the CompanyRecruiter model:

recruiters = CompanyRecruiter.objects.values_list('organization', 'recruiter')
recruiter_list = [[o, r] for o, r in recruiters]

However, in the comments you have indicated that you want to specifically query the Company model. You should be able to do this with the Postgresql aggregating function ArrayAgg:

Company.objects.annotate(recruiter_ids=ArrayAgg('company_recruiters__recruiter'))

edited May 19, 2020 at 8:41

answered May 18, 2020 at 10:31

MattRowbum

2,1921 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Faucheux Over a year ago

I need to apply the annotation on a Company queryset. Querying the CompanyRecruiter table would make me do 2 DB calls and I would like to keep all in one to be efficient.

Collectives™ on Stack Overflow

Django: Annotate list of related fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related