Django ORM version of SQL COUNT(DISTINCT ) -

Django ORM version of SQL COUNT(DISTINCT <column>) -

i need fill in template summary of user activity in simple messaging system. each message sender, want number of messages sent , number of distinct recipients.

here's simplified version of model:

class message(models.model): sender = models.foreignkey(user, related_name='messages_from') recipient = models.foreignkey(user, related_name='messages_to') timestamp = models.datetimefield(auto_now_add=true)

here's how i'd in sql:

select sender_id, count(id), count(distinct recipient_id) myapp_messages grouping sender_id;

i've been reading through documentation on aggregation in orm queries, , although annotate() can handle first count column, don't see way count(distinct) result (even extra(select={}) hasn't been working, although seems should). can translated django orm query or should stick raw sql?

from django.db.models import count messages = message.objects.values('sender').annotate(message_count=count('sender')) m in messages: m['recipient_count'] = len(message.objects.filter(sender=m['sender']).\ values_list('recipient', flat=true).distinct())

sql django orm