# How to order\_by ForeignKey that matches query?

**URL:** <https://forum.djangoproject.com/t/how-to-order-by-foreignkey-that-matches-query/17414>\
**Category:** Using the ORM\
**Created:** [December 3, 2022, 10:29am UTC](https://forum.djangoproject.com/t/how-to-order-by-foreignkey-that-matches-query/17414 "2022-12-03T10:29:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![nemecek\_f](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/nemecek_f/32/668_2.png) [@nemecek\_f](https://forum.djangoproject.com/u/nemecek_f)\
**Post date:** [December 3, 2022, 10:29am UTC](https://forum.djangoproject.com/t/how-to-order-by-foreignkey-that-matches-query/17414/1 "2022-12-03T10:29:29Z")

</div>

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:

```auto
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:

```auto
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

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 3, 2022, 12:47pm UTC](https://forum.djangoproject.com/t/how-to-order-by-foreignkey-that-matches-query/17414/2 "2022-12-03T12:47:07Z")

</div>

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:

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![nemecek\_f](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/nemecek_f/32/668_2.png) [@nemecek\_f](https://forum.djangoproject.com/u/nemecek_f)\
**Post date:** [December 3, 2022, 5:20pm UTC](https://forum.djangoproject.com/t/how-to-order-by-foreignkey-that-matches-query/17414/3 "2022-12-03T17:20:06Z")

</div>

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 |

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 3, 2022, 5:32pm UTC](https://forum.djangoproject.com/t/how-to-order-by-foreignkey-that-matches-query/17414/4 "2022-12-03T17:32:38Z")

</div>

You’ve got a couple options there - the first one that pops into my head would be to use the [Coalesce](https://docs.djangoproject.com/en/4.1/ref/models/database-functions/#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](https://docs.djangoproject.com/en/4.1/ref/models/conditional-expressions/#the-conditional-expression-classes) - but that’s my second choice here.  
YMMV.

---

<div class="post-metadata">

**Author:** ![nemecek\_f](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/nemecek_f/32/668_2.png) [@nemecek\_f](https://forum.djangoproject.com/u/nemecek_f)\
**Post date:** [December 6, 2022, 11:16am UTC](https://forum.djangoproject.com/t/how-to-order-by-foreignkey-that-matches-query/17414/5 "2022-12-06T11:16:34Z")

</div>

Thanks, Ken! Coalesce work great for my use case