Using F() with annotations including another query expression in Django

Question

This is my (simplified) use case:

from django.db import models

class MyType(models.Model):
    whatever = models.CharField()

class A(models.Model):
    type = models.ForeignKey(MyType)

class B(models.Model):
    my_type = models.ForeignKey(MyType)
    position = models.IntegerField(default=0)

I want elements from A sorted by position field of B. So, I'd need to join A and B tables on MyType first. Tried this:

A.objects.all().annotate(position=B.objects.get(my_type=models.F('type')).position).order_by('position')

Got the error:

FieldError: Cannot resolve keyword 'type' into field. Choices are: my_type, my_type_id, position

So, Django understands that F('type') is trying to fetch the value of 'type' field of model B. Which, of course, doesn't exist. I wanted to use 'type' field of model A. Looks like F applies to the inner query, not to the outer one.

So, is there any better way to get what I want?

score 2 · Accepted Answer · answered Jun 29 '18 at 08:02

# Django 1.11 already includes OuterRef and Subquery, but I'm working with Django 1.9
from django_subquery.expressions import OuterRef, Subquery

items = B.objects.filter(my_type=OuterRef('type')).order_by('position')
A.objects.all().annotate(position=Subquery(items.values('position')[:1])).order_by('position')

Get django_subquery for Django versions older than 1.11 from here

Using F() with annotations including another query expression in Django

1 Answers1