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+unsubscr...@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/5136c849-edf5-4618-a3ad-aa09af452e40%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
