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

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.

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.

---

The only way to get native SQL EXISTS/NOT EXISTS without extra queries or JOINs is to add it as raw SQL in the .extra() clause:

users = users.extra(where=[
    """NOT EXISTS(SELECT 1 FROM {reports} 
                  WHERE user_id={users}.id)
    """.format(reports=Report._meta.db_table, users=User._meta.db_table)
])

In fact, it's a pretty obvious and efficient solution and I sometimes wonder why it wasn't built in to Django as a lookup. Also it allows to refine the subquery to find e.g. only users with[out] a report during last week, or with[out] an unanswered/unviewed report.