Filter Django query by annotated field using a subquery

I am trying to further filter a Django query using an annotated field:

My (simplified) tables:

class Inventory(models.Model):
    item = models.CharField(max_length=10, primary_key=True)
    lot = models.CharField(max_length=10)
    ...  MORE FIELDS ...

class Car(models.Model):
    name = models.CharField(max_length=10)
    item= models.ForeignKey(Inventory, on_delete=models.PROTECT, null=True, blank=True)
    ...  MORE FIELDS ...

I would like my results set for the Inventory query to include the name of the car assigned to that item. This works fine:

Inventory.objects.annotate(car=Subquery(Cars.objects.filter(item=OuterRef('item')).values('name')))

The car name is retrieved in the queryset. I used to have the car in the item (so it was easy to filter on the car), but due to other requirements, I has to move the reference the other way around - car now has an item field). I ensure that once an item is associated with a car, it cannot be used again for another car since the Subquery must return a single result.

Now, I would like to further filter the queryset by the annotated field that brings back car:

Inventory.objects.annotate(car=Subquery(Cars.objects.filter(item=OuterRef('item')).values('name'))).filter(car__icontains='AA')

Now, I get an error raise FieldError('Related Field got invalid lookup: {}'.format(lookup_name))

Is it possible to do this the way I am trying?

Back to Top