Order by subset of related field?

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.

If I have understood everything correctly, it can be done with a query like this:

from django.db import models

queryset = (
    Release.objects
    .alias(
        primary_role=models.Case(
            *(
                models.When(type=k, then=models.Value(v))
                for k, v in Release.TYPES_PRIMARY_ROLE.items()
            )
        ),
    )
    .alias(is_primary=models.Q(credits__role__name=models.F('primary_role')))
    .filter(is_primary=True)
    .order_by('credits__entity__name')
)

Exclude all releases that are not is_primary, then sort by “entity”.“name”. Look at the raw query by printing out queryset.query, is this what you need? You can also look at queryset.explain(), I don't know how efficient it would be on big data.

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, 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")
)
Вернуться на верх