[Fixed]-Django query to annotate number of foreign keys matching certain value

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.