Phydeaux Phydeaux - 2 years ago 131
SQL Question

Django - filter objects by number of related objects which meet a condition

Given the following model:

class EmailVerification(models.Model):
user = models.ForeignKey(User, related_name='email_verifications')
token = models.CharField(max_length=30)
email = models.CharField(max_length=200)
expiry = models.DateTimeField()
verified = models.BooleanField(default=False)

I want to filter
s that have at least one verified email, so something like:

num_verified_emails = EmailVerification.objects.filter(user=user, verified=True).count()

Is there a way to accomplish this without using custom SQL?

Many thanks.

Answer Source

You're overcomplicating the query a bit: all you want is Users who have at least one EmailVerification with verified=True. That can be done with a simple join:


If you did need to filter on the actual number of verified emails, you could use annotations, but that's not necessary here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download