CODEWITHSUNDEEP
pythondjangodjango-rest-framework
grandalph
grandalph

Reputation: 95

Django queryset filter with Q object or relationship is not working as expected

I have a model A with two fields:

class A(models.Model):
     text = models.TextField()
     b = models.ForeignKey(
        B, null=True, blank=True, on_delete=models.SET_NULL
     )

And a model B:

class B(models.Model):
     text = models.TextField()

I have a record A with text field "12345" and a b field as null.

When trying to filter model A with:

        queryset = queryset.filter(
            models.Q(text__icontains="123") | models.Q(b__text__icontains="123")
        )

But the record is filtered out. Did I miss anything?

Upvotes: 0

Views: 1210

Answers (1)

madbird
madbird

Reputation: 1379

This expression

        queryset = queryset.filter(
            models.Q(text__icontains="123") | models.Q(b__text__icontains="123")
        )

results in JOIN which filters out As having b = NULL.

SELECT * FROM a JOIN b ON a.b_id = b.id WHERE a.text ILIKE "%123%" OR b.text ILIKE "%123%"

Try appending manually the records having b field unset:

        queryset = (queryset.filter(b__text__icontains="123")
                    | queryset.filter(text__icontains="123"))

The second part of query will contain the records with b=NULL as long as it doesn't JOIN B with itself

SELECT * FROM a JOIN b ON a.b_id = b WHERE b.text ILIKE "%123%"
UNION
SELECT * FROM a WHERE a.text ILIKE "%123%"

Upvotes: 1

Related Questions