Django: Advanced StringAgg with ManyToMany

Question

Setup

I have two tables:

Person

name
Tim
Tom
Tam

Pet | species | color | |---------|-------| | Cat | black | | Dog | brown |

And a ManyToMany that connects them:

PersonToPet

Person.name	Pet.species
Tim	Cat
Tim	Dog
Tom	Cat

Desired result

Using Django, I would like to annotate Person such that I get this table:

Person.name	result
Tim	<a>Cat (black)</a><a>Dog (brown)</a>
Tom	<a>Cat (black)</a>
Tam

Is this possible?

I've only got this:

from django.contrib.postgres.aggregates import StringAgg


Person.objects.annotate(
    result=StringAgg('pets', delimiter=',')
)

Which gives:

Person.name	result
Tim	Cat,Dog
Tom	Cat
Tam

Can anyone crack this nut?

Answer 1

Found the solution:

from django.contrib.postgres.aggregates import StringAgg
from django.db.models.functions import Concat
from django.db.models import Case, Value, When, F, Value, CharField


Person.objects.annotate(
    result=StringAgg(
        Case(
            When(
                pets__isnull=False,
                then=Concat(
                    # <a>{species} ({color})\<a> 
                    Value('<a>'),
                    F('pet__species'),
                    Value(' ('),
                    F('pet__color'),
                    Value(')</a> '),
                    output_field=CharField()
                )
            )
        ),
        delimiter=''
    )
)