Home > Mobile >  Concatenate foreign key fields with delimiter Django
Concatenate foreign key fields with delimiter Django

Time:11-17

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.

CodePudding user response:

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.

  • Related