Re: Aggregation / annotation over results of a subquery

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

On Friday, 20 May 2016 16:52:01 UTC+1, Simon Charette wrote:

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

Le vendredi 20 mai 2016 09:14:10 UTC-4, Malcolm Box a écrit :

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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/10befbe1-f958-4ce4-9c27-f048a57be79e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.