Django queryset specific order by based on values of foreign key

Question

I have two models, Position and Player, for a baseball site. Positions are named pitcher, catcher, first base, second base, third base, etc.

class Position(models.Model):
    name = models.CharField(max_length=100)
    slug = models.SlugField()

class Player(models.Model):
    name = models.CharField(max_length=300)
    slug = models.SlugField()
    position = models.ForeignKey(Position)

Is there a way to make one query to return players in a specific order? For example, I'd like to do something like:

Player.objects.all().order_by(position=('first base', 'second base', 'third base', 'pitcher', 'catcher',))

This will return all players sorting them by the position field in the specified order of first base, second base, third base, pitcher, catcher, etc.

score 4 · Accepted Answer · edited May 23 '17 at 10:31

4

You can do this in python using sorted():

order = ['first base', 'second base', 'third base', 'pitcher', 'catcher']
players = sorted(Player.objects.all(), key = lambda p: order.index(p.position.name))

Also see these related topics:

edited May 23 '17 at 10:31

Community

1
1

answered May 02 '14 at 17:06

alecxe

462,703
120
1,088
1,195

1

If there's 1 million players, this is gonna take forever. – Patrick Bassut May 02 '14 at 17:12
@PatrickBassut yup, that's a fair point, but it is still an option and would work for simple use cases. Thanks. – alecxe May 02 '14 at 17:15
@Rollo hard to disagree. – Divij Sehgal Mar 22 '18 at 12:05

Patrick Bassut · Answer 2 · 2014-05-02T17:17:04.697

I would recommend adding another field to the Position model and retrieve the results ordering by this field.

class Position(models.Model):
    order = models.IntegerField()
    name = models.CharField(max_length=100)
    slug = models.SlugField()

    class Meta:
        ordering = ['order']

then, when you create a new position field, you set the order of it. When you need them you just do:

Position.objects.all()

Then they will be ordered.

EDIT:

As @AndrewGorcester stated, add unique=True to the order attribute to avoid programming mistakes(in case you add the same order to two different positions), looking like this:

order = models.IntegerField(unique=True)

I agree with this, although I'd also add `unique=True` to the `order` column if that is in line with your use case. — Andrew Gorcester, May 02 '14 at 17:14

score 3 · Answer 3 · answered Feb 24 '17 at 09:02

I have a long table of profiles and sometimes need to preview one of them, so idea was to load this profile from DRF in the first chunk of data, here how it was solved:

from django.db.models import Case, When, BooleanField
Profile.objects.all().annotate(is_preview_profile=Case(When(id__exact=preview_profile_id, then=1), default=0, output_field=BooleanField())).order_by('-is_preview_profile')

More information can be found here: https://docs.djangoproject.com/en/1.10/ref/models/conditional-expressions/#case

score 0 · Answer 4 · answered May 02 '14 at 17:18

If you do not want to add another field to track ordering, you can override the default id field to not be auto-assigned, and make sure that you add positions with IDs that correspond to their ordering.

id = models.PositiveIntegerField()

class Meta:
    ordering = ['id']

Now you can do this:

Player.objects.all().order_by('position_id')

score 0 · Answer 5 · answered Dec 27 '19 at 19:54

While all the above methods are correct here is a more pythonic way to achieve the same. You can use the double underscore to link the key from another table (Which is linked as a foreign key). The reference for the same is here

Where user is a built in model that can be imported with from django.contrib.auth.admin import User

For example

I have a model Post connected with the author name which is linked to the foreign key model User. Where user is a built in model that can be imported with from django.contrib.auth.admin import User

from django.contrib.auth.admin import User
class Post(models.Model):
    author = models.ForeignKey(User, on_delete=models.CASCADE)

Now, to sort Posts with the username of User you can use

Post.objects.order_by('author__username')

(note that it is a double underscore)

Django queryset specific order by based on values of foreign key

5 Answers5

Linked