I found a way to express the second query in the ORM. Person.objects.filter(sex='F', pk__in=tallest_people) Thanks, Suriya
On Monday, June 1, 2015 at 12:13:49 PM UTC+5:30, Suriya Subramanian wrote: > > 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/43f9e3a1-4211-4bfa-aa8d-292cfe436169%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
