Query objects based on FK objects meeting criteria?

Using Django Using the ORM
1

Hello,

I have a model where I have Game entity and then ReleaseDate entity. The release date has FK to Game and encapsulates the region and platform so a game can have multiple release dates in the database.

The problem is that I want to list upcoming games, which I can do by filtering the ReleaseDate but the regions are not exclusive. The region can mean “worldwide” or perhaps just “Europe” and sometimes there is worldwide release date for a game and also more “precise” date for particular region. So I need a way to prioritize the region user is in with the ability to fallback to worldwide date.

My ideal output from this query is list of games where the release dates are grouped based on the date and I also know which platforms (represented as Int) this release date is for.

Can this somehow be done with the ORM? My previous solution was to annotate the games with release date, but this is not very performant since I need to annotate all the games in the database and have two subqueries because of the regions and platforms complicate this further.

I am currently sending the data based on all queried games and doing the deduplication on the client which is not ideal :face_with_spiral_eyes:

So far my only idea is to move this deduplication logic to Python code but I am thinking maybe I am forgeting some basic database 101 thingy that can help me with this situation.

I have also prepared “professional illustration” to maybe make my problem clear.

image
image609×632 22.6 KB

When the users region is 2, then the result should be “Game” with the release dates for region=2, but the “Game 2” should be returned with the Release date for region 8, since region 8 is the worldwide fallback

2

i think your model maybe like this…

class game:
  rd = fk(release)
class release:
  date = datetimefield
  region = intfield

then,

a = game.objects.filter(rd__date={date}, region={region})
3

But how will this solve my issue?

4

um…

class game:
  rd = fk(release)
class release:
  date = datetimefield
  region = intfield

then,

a = game.objects.filter(region={region a}).order_by('rd__date')
b = game.objects.exclude(region={region a}).order_by('rd__date')
context['game'] = list(a) + list(b)

or

a = game.objects.filter(region={region a}).annotate(date='rd__date').order_by('date')
b = game.objects.exclude(region={region a}).annotate(date='rd__date').order_by('date')
context['game'] = list(a) + list(b)
5

I see, thanks for expanding on that. The issue with this approach is that this won’t override the region with more precise date if available. And for “Game” from my example, this would result in all release dates being queried, when for region=2 I only want those two.

6

Based on your explanation, this is the best I can do to help;;