In a Django QuerySet, how to filter for "not exists" in a many-to-one relationship

I have two models like this:

class User(models.Model):
    email = models.EmailField()

class Report(models.Model):
    user = models.ForeignKey(User)

In reality each model has more fields which are of no consequence to this question.

I want to filter all users who have an email which starts with 'a' and have no reports. There will be more .filter() and .exclude() criteria based on other fields.

I want to approach it like this:

users = User.objects.filter(email__like = 'a%')

users = users.filter(<other filters>)

users = ???

I would like ??? to filter out users who do not have reports associated with them. How would I do this? If this is not possible as I have presented it, what is an alternate approach?

Solution 1:

Note: this answer was written in 2013 for Django 1.5. See the other answers for better approaches that work with newer versions of Django

Use isnull.

users_without_reports = User.objects.filter(report__isnull=True)
users_with_reports = User.objects.filter(report__isnull=False).distinct()

When you use isnull=False, the distinct() is required to prevent duplicate results.

Solution 2:

As of Django 3.0 you can now use expressions directly in a filter(), removing the unnecessary SQL clause:

User.objects.filter(
    ~Exists(Reports.objects.filter(user__eq=OuterRef('pk'))),
    email__startswith='a'
)

SELECT user.pk, user.email
FROM user
WHERE NOT EXISTS (SELECT U0.pk FROM reports U0 WHERE U0.user = user.pk) AND email LIKE 'a%';

Docs:

• Exists
• OuterRef
• Filtering on a Subquery() or Exists() expressions

For Django 1.11+ you can add EXISTS subqueries:

User.objects.annotate(
    no_reports=~Exists(Reports.objects.filter(user__eq=OuterRef('pk')))
).filter(
    email__startswith='a',
    no_reports=True
)

This generates SQL something like this:

SELECT
    user.pk,
    user.email,
    NOT EXISTS (SELECT U0.pk FROM reports U0 WHERE U0.user = user.pk) AS no_reports
FROM user
WHERE email LIKE 'a%' AND NOT EXISTS (SELECT U0.pk FROM reports U0 WHERE U0.user = user.pk);

A NOT EXISTS clause is almost always the most efficient way to do a "not exists" filter.