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: stackoverflow.com/questions/31169108/…

Ian Clark
– Ian Clark

2016-08-18 10:28:37 +00:00
Commented Aug 18, 2016 at 10:28 — Ian Clark
– Ian Clark, Commented Aug 18, 2016 at 10:28

Ian Clark · Accepted Answer · 2016-08-18 10:21:52Z

1

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.

answered Aug 18, 2016 at 10:21

Ian Clark

9,3874 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django query to annotate number of foreign keys matching certain value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related