Django query to annotate number of foreign keys matching certain value

Question

If I had the following related models:

class User(models.Model):
    ...


class Identity(models.Model):
    user = models.ForeignKey(User, on_delete=models.PROTECT)
    category = models.CharField(max_length=8, choices=IDENTITY_CATEGORIES)
    ...

How could I query for users with multiple email identities, where multiple Identity instances exist of category "email" which point to the same User.

I've seen that Django 1.8 introduced Conditional Expressions, but I'm not sure how they would apply to this situation.

Similar: http://stackoverflow.com/questions/31169108/django-query-annotation-with-boolean-field — Ian Clark, Aug 18 '16 at 10:28

score 1 · Accepted Answer · answered Aug 18 '16 at 10:21

By applying django.db.models.Sum, here's one way of achieving it:

from django.db.models import Case, IntegerField, Sum, When


def users_with_multiple_email_identities():
    """
    Return a queryset of Users who have multiple email identities.
    """
    return (
        User.objects
        .annotate(
            num_email_identities=Sum(
                Case(
                    When(identity__category='email', then=1),
                    output_field=IntegerField(),
                    default=Value(0)
                )
            )
        )
        .filter(num_email_identities__gt=1)
    )

So, we use use .annotate() to create an aggregate field representing the number of email identities per user, and then apply .filter() to the results to return only users with multiple email identities.

Django query to annotate number of foreign keys matching certain value

1 Answers1