Concatenate foreign key fields with delimiter Django

Question

Lets say I have 2 models with a foreign key relation which are used to bundle books:

class Bundle(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    name = models.CharField(max_length=20)
    isbn = models.CharField(max_length=13)
    bundle = models.ForeignKey(Bundle)

we'll identify the bundles by concatenating the ISBN numbers with the delimiter like so:

123456788 & 123456789

To further export a list of available bundles for further processing we need that number.

I'm aware I could use:

for bundle in Bundle.objects.all():
    complex_isbn = ' & '.join(bundle.book_set.all().values_list('isbn', flat=True))

But this would just be too slow for the real-world purpose. Is there a way I could use annotate to accomplish this? If so, how? I'm struggling to find my way through the docs on how to accomplish concatenating multiple foreign key entries.

what database do you use? – Willem Van Onsem Nov 16 '21 at 11:45 — Willem Van Onsem, Nov 16 '21 at 11:45

score 3 · Accepted Answer · answered Nov 16 '21 at 11:53

You can make use of the StringAgg aggregate function [Django-doc], which is only available for PostgreSQL. You thus can annotate the Bundles with the complex ISBN:

from django.contrib.postgres.aggregates import StringAgg

Bundle.objects.annotate(
    complex_isbn=StringAgg('isbn', delimiter=' & ')
)

The Bundles that arise from this queryset will have an extra attribute .complex_isbn.

Concatenate foreign key fields with delimiter Django

1 Answers1