Aggregation / annotation over results of a subquery

[Malcolm Box]

Hi all,

I'm trying to get the ORM to let me sort things based on an annotation, where that annotation requires a subquery to select items to consider.

Concrete example, given models:

class Contact(models.Model):

   name = models.CharField()

class Message(models.Model):

  sender = models.ForeignKey(Contact, related_name='frm')

  recipient = models.ForeignKey(Contact, related_name='to')

  unread = models.BooleanField()

  send_time = models.DateTimeField(auto_now_add=True)

  ....

I want to do things like "for Contact X, create a list of other contacts ordered by the number of messages to X" or "Order the contacts by number of unread messages to X"

It seems as if annotate/aggregate should be able to do what I want, but I can't get it to produce a subquery to select the messages to count:

Messages.objects.filter(recipient=X).count() - number of messages to X from all contacts

Contact.objects.annotate(msg_count=Count('frm__id')) - gives number of messages from each contact, but to anyone, not just X

Contact.objects.annotate(msg_count=Count(Q(frm__recipient=X)).order_by('msg_count') - gives the wrong answer for the msg_count (seems to do same as query above)

In SQL, what I want is something like:

select contact.name, count(message.id) from contact left outer join message on (contact.id = message.sender_id) where (message.recipient_id = X.id) group by contact.id

But I can't get the ORM to generate SQL that looks like this.

Any pointers/help - even "you can't do that using the ORM" would be very welcome.

Cheers,

Malcolm

[Simon Charette]

Hi Malcom,

I suggest you look into the conditionnal aggregation documentation[1].

from django.db.models import Case, Count, When

Contact.objects.annotate(
    messages_count=Count(
        Case(When(
            messages__recipient=recipient,
            messages__status=Message.STATUS_UNREAD,
            then='messages'
        )),
    )
).filter(
    message_count__gte=1,
).order_by('-message_count')

Cheers,
Simon

[1] https://docs.djangoproject.com/en/1.9/ref/models/conditional-expressions/#conditional-aggregation

[Malcolm Box]

Thanks Simon, that's exactly what I needed. I had read the aggregation documentation, but hadn't figured out how to get it to do what I needed.

Cheers,

Malcolm