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

djangodjango-modelsdjango-orm

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?

Best Answer

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.

Related Topic