Django query sort by all satisfying a condition and then all the others

user_9958179 0
Viewed 399

I have two models City and Contact.

class City(models.Model):
    name = models.CharField(max_length=40)

class Contact(models.Model):
    name_surname = models.CharField(max_length=60)
    preference = models.IntegerField(default=0, choices=PREFERENCE)
    city = models.ManyToManyField(City)

Given a city "A" I am trying to query my DB to get a list of

  • All contacts that have "A" as a city order by preference
  • And after that all contacts that don't have "A" as a city order by preference

So basically if my Contact table (name, city, preference) had something like

  • Tom,NY,2
  • John, LA, 5
  • Mike, NY, 1
  • Richard, SF, 4

and the city I am considering is NY, the query would return:

  • Mike, NY, 1
  • Tom,NY,2
  • Richard, SF, 4
  • John, LA, 5
django-queryset django-models django python
2 Answers

This seems to work:

qs1 = Contact.objects.filter(city__name="NY")
qs2 = Contact.objects.exclude(city__name="NY").order_by("city")

qs1.union(qs2, all=True)

It's possible you wont need all=True. I needed it for my test.

Docs on union()

user_27646340

Try this:

from django.db.models import Exists, OuterRef


Contact.objects.annotate(
    has_ny=Exists(
        City.objects.filter(name='NY', contact=OuterRef('pk'))
    )
).order_by('-has_ny', 'preference')
user_67598440
Related