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

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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/844b90e2-d312-48b9-8239-150ae821d3e3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.