Order by subset of related field?

Question

I have a model Release. Each release has a type, and depending on that type different types of Credit are considered primary credits. I want to be able to order releases by the Entity names of their primary credits.

class Release(models.Model):
    TYPES = { "GA": "Game", "MO": "Movie", "TV": "TV Show", "MU": "Music", "BO": "Book", "BU": "Bundle" }
    TYPES_PRIMARY_ROLE = { "GA": "Developer", "MO": "Director", "TV": "Showrunner", "MU": "Primary Artist", "BO": "Author", "BU": "none" }

    type = models.CharField(choices=TYPES)

    # How to order_by the entity__names returned here?
    def get_credits_primary(self):
        return self.credits.filter(role__name=self.TYPES_PRIMARY_ROLE[self.type]).order_by("entity__name")

class Credit(models.Model):
    role = models.ForeignKey(CreditRole, on_delete=models.CASCADE, related_name="credits")
    entity = models.ForeignKey(Entity, on_delete=models.CASCADE, related_name="credits")
    release = models.ForeignKey(Release, on_delete=models.CASCADE, related_name="credits")

I suppose I could create a cached string value of the primary credit names, but that doesn't seem like a good way to do it.

Answer 1

This solution is partly adapted from @Serhii Fomenko's answer, and specific to PostgreSQL.

Using StringAgg you can get a list of values from a related field which can then be used for sorting:

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

queryset = (
    Release.objects
    .alias(
        primary_role=Case(
            *(
                When(type=k, then=Value(v))
                for k, v in Release.TYPES_PRIMARY_ROLE.items()
            )
        ),
    )
    .annotate(credits_list=StringAgg(
        Case(When(credits__role__name=F("primary_role"), then="credits__entity__name")),
        delimiter=", ",
        ordering="credits__entity__name"
    ))
    .order_by("credits_list")
)