Phydeaux Phydeaux - 5 months ago 31
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
User
s that have at least one verified email, so something like:

User.objects.filter(num_verified_emails__gt=0)
where:
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

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:

 User.objects.filter(email_verifications__verified=True)

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