Django ORM Interaction between order_by(), distinct(), and filter()

[Suriya Subramanian]

I have a question about how order_by(), distinct(), and filter() interact. This question is applicable only to the PostgreSQL backend since the Queryset is constructed by passing filed arguments to distinct(). https://docs.djangoproject.com/en/1.8/ref/models/querysets/#distinct In the other backends, distinct() accepts no arguments.

I'll illustrate my question with an example model. Here's a link to the code snippet: https://gist.github.com/anonymous/4111b718dbef264fb339

from django.db import models
 
class Person(models.Model):
    SEX_CHOICES = (
        ('M', 'Male'),
        ('F', 'Female'),
    )
    name = models.CharField(max_length=255)
    sex = models.CharField(max_length=255, choices=SEX_CHOICES)
    city = models.CharField(max_length=255)
    height = models.DecimalField(max_digits=10, decimal_places=2)
 
    def __unicode__(self):
        return self.name

I am interested in queries about the tallest person in a city. Specifically, 1) the tallest female in each city, and 2) the tallest person in cities where a female is the tallest. I am able to write the first query using the ORM, but not the second query.

import django
django.setup()
 
from testapp.models import Person
 
# Get a list consisting of the tallest female in each city.
tallest_females = Person.objects.filter(sex='F').order_by('city', '-height').distinct('city').values_list('pk', flat=True)
print tallest_females.query
# This outputs:
#
# SELECT DISTINCT ON ("testapp_person"."city") "testapp_person"."id"
# FROM "testapp_person"
# WHERE "testapp_person"."sex" = F
# ORDER BY "testapp_person"."city" ASC, "testapp_person"."height" DESC
 
# Get a list consiting of females who are the tallest (among all people) in
# their respective cities.
# How do I get this list using the ORM? Note that I am having resort to
# filtering results in Python.
tallest_people = Person.objects.order_by('city', '-height').distinct('city').values_list('pk', flat=True)
females_who_are_tallest_in_their_city = [ p for p in tallest_people if (p in tallest_females) ]
 
# Ideally, I'd like to write:
# Person.objects.order_by('city', '-height').distinct('city').filter(sex='F').values_list('pk', flat=True)

What's a way to compute the results of the second query fully in the database, without resorting to Python code?

Thanks,

Suriya

[Suriya Subramanian]

I found a way to express the second query in the ORM.

Person.objects.filter(sex='F', pk__in=tallest_people)

Thanks,

Suriya