Django: Filter on related model's field not being equal to a specific value

user_1018447 0
Viewed 524

Given these models:

from django.db import models

class Foo(models.Model):
    pass  # table with only one column, i.e. a primary key 'id' of type integer

class Bar(models.Model):
    value = models.TextField()
    foo = models.ForeignKey(Foo, on_delete=models.CASCADE, related_name='bars')

How to create a filter which returns all Foos related to at least one Bar with a non-zero value?

I am aware that it is possible to at first select the non-zero Bars and then return the Foos related to them. But I am specifically looking for a solution which obtains the relevant Foos in a QuerySet directly. The reason is that it it could then easily be applied to relationships which are nested to a deeper level.

What does not work

It is straightforward to create a QuerySet which returns all Foos related to at least one Bar with a zero value:

Foo.objects.filter(bars__value="zero")

However, due to the lack of the "not equal" operator in Django's filter methods, it is impossible to do something like this:

Foo.objects.filter(bars__value__not_equal="zero")  # Unsupported lookup 'not_equal' for TextField

Using exclude:

Foo.objects.exclude(bars__value="zero")

does not produce the desired output because it excludes all Foos which relate to at least one Bar with a zero value.

So, if a Foo is related to two Bars, one of which has a zero value and the other has a non-zero value, it would be filtered out by this exclude. The intention is, however, to include it because it is related to at least one Bar with a non-zero value.

Similarly, using the negated Q object:

Foo.objects.filter(~models.Q(bars__value="zero"))

also does not produce the desired output because it would only include all Foos which are not related to any Bars with a zero value.

It would create the same QuerySet as the exclude method mentioned above.

Notes

The type of the B.value field has intentionally been chosen as text in the examples to emphasize that I am looking for a generic solution where the "not equal" operator cannot be emulated by using e.g. a combination of "less than" and "greater than" operators.

django-queryset django python
1 Answers

I also can't think of a simple way of achieving this, but using a Subquery to exclude zero-value Bars might work:

from django.db.models import Subquery

qs = Foo.objects.filter(
    bars__pk__in=Subquery(Bar.objects.exclude(value="zero").values('pk'))
)

This should give you all Foo instances with a relation to a Bar with non-zero value.

user_27646340
Related