Django How to do a correlated EXISTS or NOT EXISTS-CodePudding

How can I perform the following correlated EXISTS and NOT EXISTS in Django?

I do not want to use IN and NOT IN. These are not the same as EXISTS and NOT EXISTS.

Correlated EXISTS:

SELECT *
FROM foo
WHERE EXISTS (
    SELECT 1
    FROM bar
    WHERE foo.baz = bar.baz

)

Correlated NOT EXISTS:

SELECT *
FROM foo
WHERE NOT EXISTS (
    SELECT 1
    FROM bar
    WHERE foo.baz = bar.baz

)

CodePudding user response：

You can use an Exists() subquery to generate a query like you desire. Not entirely sure how your model looks but the following may look similar to what you want

from django.db.models import Exists, OuterRef

matching_bars = Bar.objects.filter(baz=OuterRef('baz'))
Foo.objects.filter(Exists(matching_bars))

To use NOT EXISTS just prefix the Exists subquery with ~

Foo.objects.filter(~Exists(matching_bars))

CodePudding user response：

You can use method of QuerySet - exists(). Assuming that:

class Foo(models.Model):
    bar = models.ForeignKey(Bar, ..., related_name='foos')

You can check if there is relation:

foo = Foo.objects.get(id=1)
bar = Bar.objects.get(id=1)

bar.foos.exists()   # returns True if not empty else False
foo.bar             # returns related object or None