[SOLVED] Concatenate foreign key fields with delimiter Django

Concatenate foreign key fields with delimiter Django

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.

Solution

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.