I use annotation to add a property to an object, which I can then use for order_by. However, I want to annotate the relationship field in relation. I know that I will be able to get to the field using bi-directional notation, but I just can’t wrap my head around me.
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)
Thus, a discussion may not necessarily be related to the Group, and DiscussionResponses are related to the discussion. What I would like to do is to find the latest discussion on the answers to any discussions related to the Group, if it exists, and sort by it.
I got to this:
Group.objects.filter(some_filtering).distinct().annotate( last_response=Max('some__reverse__relationship__timestamp').order_by( '-last_response')
I just can't figure out how to correctly approach the timestamp on the DiscussionResponse in this case.
UPDATE: You can indeed order an annotated value. Here is an example with order_by in the timestamp of the corresponding 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 No. 2 has related discussions, so it was moved to the beginning; the rest maintain a natural order. However, I would like the transition groups that have the latest answers to be discussed at the top of the list. This is why I thought that 'discussion__discussionresponse__timestamp' would work, but that doesn't seem to be the case.