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.

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.

• django
• django-models
• sql-order-by

• 1 Can you please add the sql pseudocode, how it should look like, I didn't quite understand what needs to be done. For example, now in your get_credits_primary function, you find all the credits were, for example, WHERE ("credit"."release_id" = 1 AND "creditrole"."name" = 'Developer') ORDER BY "entity"."name" ASC. But what should that part look like? – Serhii Fomenko Commented Feb 6 at 15:20
• @SerhiiFomenko Hmm, I'm trying to think what (pseudo) sql might look like but I can't think of anything. Maybe what I'm asking isn't possible. – bur Commented Feb 6 at 18:06
• 1 You can provide not necessarily a pseudo sql, just an example that shows what it should look like roughly, I will think if it can be expressed in sql syntax. I gather there must be some logic in ORDER BY, something like name in some_list or some_list.index(name)? – Serhii Fomenko Commented Feb 6 at 19:33
• I guess it would be something like ORDER BY (release.credits.entity WHERE release.credits.role = release.primary_role). I know it's nonsense sql but I don't know how to formulate it better. So basically order by the list of primary credits of each release. – bur Commented Feb 7 at 11:03
• 1 I think I understand your point, it should be sorting on two fields, for example, is_primary=release.credits.role == release.primary_role then sorting something like ORDER BY is_primary DESC, “entity”.“name” ASC, did I get that right? – Serhii Fomenko Commented Feb 7 at 14:31

2 Answers 2

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.

UPDATED

Here's a modification to the original query that should work on PostgresSql and filter out release duplicates.

from django.db import models
from django.db.models import functions

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')))
    .alias(
        rank=models.Window(
            functions.Rank(),
            partition_by='id',
            order_by='credits__entity__name',
        )
    )
    .filter(is_primary=True, rank=1)
    .order_by('credits__entity__name')
)

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")
)