I'm using annotate to add a property to an object which I can then use for order_by. However, I want to annotate on a field of a relation on a relation. I know I should be able to get to the field somehow using double-underscore notation, but I just can't seem to wrap my head around it.

Here are the models:

class Group(Taggable, Uploadable):name            = models.CharField(max_length=250, db_index=True)description     = models.TextField(max_length=5000, null=True,blank=True, db_index=True)private         = models.BooleanField(default=False)members         = models.ManyToManyField(User, null=True,related_name='members', through='GroupToUser')pending_members = models.ManyToManyField(User, null=True,related_name='pending_members')admin           = models.ForeignKey(User, null=True)timestamp       = models.DateTimeField(auto_now_add=True)author          = models.ForeignKey(User, related_name='author')class Discussion(Taggable, Uploadable):author      = models.ForeignKey(User)title       = models.CharField(max_length=250, db_index=True)description = models.TextField(max_length=5000, null=True,blank=True, db_index=True)group       = models.ForeignKey(Group, null=True)timestamp   = models.DateTimeField(auto_now_add=True)class DiscussionResponse(Uploadable):author     = models.ForeignKey(User)discussion = models.ForeignKey(Discussion)message    = models.TextField(max_length=5000)timestamp  = models.DateTimeField(auto_now_add=True)

So, a Discussion can optionally be associated with a Group, and DiscussionResponses are associated with a discussion. What I would like to do is find the most recent DiscussionResponse on any discussions connected to a Group, if it exists, and sort by that.

I've gotten as far as this:

Group.objects.filter(some_filtering).distinct().annotate(last_response=Max('some__reverse__relationship__timestamp').order_by('-last_response')

I just can't seem to figure out the right way to get to the timestamp on a DiscussionResponse in this case.

UPDATE: You can indeed order by an annotated value. Here is an example with an order_by on the timestamp of a related discussion:

>>> groups = Group.objects.all().annotate(last_response=Max('discussion__timestamp')).order_by('-last_response')
>>> for group in groups:
...     print(group.id, group.last_response)
...     
... 
(2L, datetime.datetime(2013, 5, 8, 15, 32, 31))
(1L, None)
(3L, None)
(4L, None)
(6L, None)
(7L, None)
(8L, None)
(9L, None)

In this case, only group #2 has related discussions so it was moved to the top; the rest retain the natural order. What I'd really like to do, though, is move groups that have recent responses to discussions moved to the top of the list. That's why I thought 'discussion__discussionresponse__timestamp' would work, but it doesn't seem to.

Ok, apparently it is just 'discussion__discussionresponse__timestamp'. I tried it in the Django shell and it didn't work after saving a new DiscussionResponse, but it worked several minutes later when I tried it again:

>>> groups = Group.objects.all().annotate(last_response=Max('discussion__discussionresponse__timestamp')).order_by('-last_response')
>>> for group in groups:
...     print(group.id, group.last_response)
...     
... 
(2L, datetime.datetime(2013, 5, 16, 14, 56, 22))
(1L, None)
(3L, None)
(4L, None)
(6L, None)
(7L, None)
(8L, None)
(9L, None)
>>> 

If anyone knows why it didn't work right after saving a new object to the database, but did work later, that would probably be useful information.

Here's another run at the query with discussions/responses added to another group just for added verification:

>>> groups = Group.objects.all().annotate(last_response=Max('discussion__discussionresponse__timestamp')).order_by('-last_response')
>>> for group in groups:
...     print(group.id, group.last_response)
...     
... 
(4L, datetime.datetime(2013, 5, 16, 15, 25, 40))
(2L, datetime.datetime(2013, 5, 16, 15, 16, 46))
(1L, None)
(3L, None)
(6L, None)
(7L, None)
(8L, None)
(9L, None)
>>>