CODEWITHSUNDEEP
djangodjango-modelsdjango-querysetdjango-orm
frnhr
frnhr

Reputation: 12903

Sum of related objects across 2 FK to the same table, with conditions

I have two models:

class User(Model):
    ...

class Message(Model):
    sender = ForeignKey(User, CASCADE, 'sent_msgs')
    receiver = ForeignKey(User, CASCADE, 'rcvd_msgs')
    ignored = BooleanField()

I'm trying to annotate a queryset of Users with a sum total of their related messages, i.e. sum of both sent_msgs and rcvd_msgs. Additionally, any Message with ignored=True should be ignored.

I can do this with RawSQL fairly simply, using a subquery:

SELECT COUNT("messages_message"."id")
FROM "messages_message"
WHERE "messages_message"."ignored" = FALSE
  AND (
    "messages_message"."sender_id" = "users_user"."id"
    OR
    "messages_message"."receiver_id" = "users_user"."id"
  )

queryset = queryset.annotate(msgs_count=RawSQL(that_query_above))

Is there a way to do this without using RawSQL?

Upvotes: 2

Views: 298

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476669

We can use a Subquery [Django-doc] here:

from django.db.models import Count, OuterRef, Subquery, Q

User.objects.annotate(
    msgs_count=Subquery(
        Message.objects.filter(
            Q(sender_id=OuterRef('pk')) | Q(receiver_id=OuterRef('pk')),
            ignored=False
        ).order_by().values('ignored').annotate(cn=Count('*')).values('cn')
    )
)

This then produces a query like:

SELECT auth_user.*,
    (
        SELECT COUNT(*) AS cn
        FROM message U0
        WHERE (U0.sender_id = auth_user.id OR U0.receiver_id = auth_user.id)
            AND U0.ignored = False)
        GROUP BY U0.ignored
    ) AS msgs_count
FROM auth_user

Upvotes: 2

Related Questions