How to order_by ForeignKey that matches query?

Using Django Using the ORM
1

Hello,

I have a list of Games that each can have multiple release dates based on the region. I want to update my DRF endpoint that lists upcoming games to have just the one relevant release date based on the request header containing user’s locale.

The model looks roughly like this:

class GameReleaseDate(models.Model):
    game = models.ForeignKey(to=Game, null=False, blank=False, on_delete=models.CASCADE, related_name="release_dates")

    date = models.DateField(null=False)

    region = models.PositiveSmallIntegerField(null=False)

I assume I need something like this:

Game.objects.annotate(release_date=Magic(release_dates__region=region_param).filter(release_date_gte=today).order_by(release_date)

Is this where I need to look into subqueries? Or perhaps having different model structure would help?

Thanks

2

Yes, you’re probably looking at a subquery here.

I’m not sure I’m clear on exactly what you’re trying to return with your sample, but I’m suspecting you’ll end up with something like:

Game.objects.annotate(
    release_date=Subquery(
        GameReleaseDate.objects.filter(
            game_id=OuterRef('id'),
            region=region_param
        ).order_by('-release_date').values('release_date')[:1]
    )
).filter(release_date_gte=today)
1 Like
3

Thanks, Ken! This looks like something I need. Just tried it out.

What would be the best approach to filter the subquery with region constant 8 which is worldwide in the case my previous param did not find any dates?

I guess I could try region__in but with this there could be cases where it would return “less” precise release date? Because the worldwide sadly isnt first or last in the enum.

From the docs:

name value
europe 1
north_america 2
australia 3
new_zealand 4
japan 5
china 6
asia 7
worldwide 8
korea 9
brazil 10
4

You’ve got a couple options there - the first one that pops into my head would be to use the Coalesce function with two embedded subqueries - one for the parameter search and the second for the global search.

Another would be to use the Case/When functions - but that’s my second choice here.
YMMV.

1 Like
5

Thanks, Ken! Coalesce work great for my use case