CODEWITHSUNDEEP
pythondjangodjango-orm
Krzysieqq
Krzysieqq

Reputation: 1121

Django ORM order by expression

Hi i could order by < 2 in ORM my only idea is: 

players = GamePlayer.objects.raw('SELECT * FROM `Farmer_gameplayer` WHERE (`Farmer_gameplayer`.`game_id` = %d) ORDER BY `turn` < %d' % (game.pk, game.turn))

I'd like to order this:

players = GamePlayer.objects.filter(game=game) ORDER BY `turn` < 2

Upvotes: 4

Views: 2517

Answers (1)

bakkal
bakkal

Reputation: 55448

Note: You can probably adapt your application to use ORDER BY turn, as qualitatively the result set isn't very different from ORDER by turn < 2. You'll see a difference only in that ORDER BY turn would alter the sort of all rows depending on their turn column, while ORDER BY turn < 2 sorts by the boolean expression turn < 2 which won't reorder all rows beyond that. Below I present how you can order a Django queryset using an expression, e.g. turn < 2

Annotate with an expression, then order by it:

The idea is to annotate a field called turn_is_less_than_2 that goes either True or False, then order by it. The said annotation will be representing the expression turn < 2 that you want to order by later on (ORDER BY turn < 2)

from django.db.models import Case, When, BooleanField

GamePlayer.objects.filter(game=game).annotate(turn_is_less_than_2=Case(
    When(turn__lt=2, then=True),
    default=False,
    output_field=BooleanField()
)).order_by('turn_is_less_than_2') 

# or order_by('-turn_is_less_than_2') for DESC order instead of ASC

Breaking it down to explain:

First I'm just taking your query

GamePlayer.objects.filter(game=game)

Then what I do is annotate it with a field turn_is_less_than_2 that is True if turn < 2, and False otherwise

GamePlayer.objects.filter(game=game).annotate(turn_is_less_than_2=...)

Then when we have annotated that filtered queryset, we can now order_by that field:

GamePlayer.objects.filter(game=game).annotate(...).order_by('turn_is_less_than_2')

Upvotes: 4

Related Questions