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 [email protected]. To post to this group, send email to [email protected]. 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.
