CODEWITHSUNDEEP
pythondjangodjango-orm
alonisser
alonisser

Reputation: 12068

Django orm - order_by the result of select on a foreign key

I have the following models (Simplified example of course):

class ParentModel(Model):
   id = IntegerField(primary_key=True)
   family = OneToOneField(Family)
   objects = SpecificManagerThatDoesAlotOfQueryLogic()

class Family(Model):
    id =IntegerField(primary_key=True)

class Child(Model):
   family = ForeignKey(Family, related_name='children')
   is_the_relevent_child = BooleanField(default=False)  #Only one Child would have this a s
   score_to_sort_by = DecimalField()

Where I need to return a queryset of ParentModel (yes, I need this to be a queryset, so pagination, etc that happen later won't break) that is ordered by family__children__score_to_sort_by using the is_the_relevent_child flag to choose which Child I order by.

I know that if Child had a OneToOne relationship to Family I could that with:

ParentModel.objects.filter(**{something:something}).order_by('family__child__score_to_sort_by')

I know this modeling isn't great for doing this, but I need it for other reasons, so I need to get the queryset thing working I suspect annotation can help me here, but I can't figure out how, (Or if someone can point me to using some extra SQL I can probably handle this also, if not too complicated), we are using django 1.6 Any Ideas? Thanks..

Upvotes: 0

Views: 55

Answers (1)

Robert Jørgensgaard Engdahl
Robert Jørgensgaard Engdahl

Reputation: 3364

family__children__score_to_sort_by is a well-defined column in the query rows of any query on ParentModel. So you can just filter on the is_the_relevant_child directly, like so:

ParentModel.objects.filter(...)
    .filter(family__children__is_the_relevant_child=True)
    .order_by('family__children__score_to_sort_by')

only the relevant child should be considered in this ordering.

Upvotes: 1

Related Questions