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

Question

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?

Answer 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.

Answer 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.

---