Getting the latest distinct object in Django

Question

Currently I have a simple messaging-chat app.

class Person(models.Model):
     user = models.OneToOneField(User, primary_key=True)
     sex = models.CharField(max_length=1, null=True, blank=True)

class Message(models.Model):
     sender = models.ForeignKey(Person, related_name= 'sent')
     recipient = models.ForeignKey(Person, related_name = 'received')
     created = models.DateTimeField(auto_now_add=True, null = True, blank = True)
     message = models.CharField(max_length=500)
     conversation_id = models.CharField(max_length = 90)

I use Django Tastypie as my rest API. I would like to create an inbox, similar to that of Facebook's inbox where conversations are ordered by the most recent. The most recent message conversation would show up at the top of the inbox.

The "conversation_id" is the sender's username and the recipient's username put together.

So for example, if user Kyle messages user Alex, the conversation_id would be "alexkyle".

If Alex messaged Kyle, it would still be "alexkyle". If Alex messaged Bob it would be "alexbob".

I have it set up so that the conversation_id will always be in that unique alphabetical order. The conversation_id is what I use to differentiate between conversations.

I have the following resource:

class MessageResource(ModelResource):
    sender = fields.ForeignKey(PersonResource, 'sender', full =True)
    recipient= fields.ForeignKey(PersonResource, 'recipient', full=True)
    class Meta:
        queryset = Message.objects.all()
        allowed_methods = ['get']
        resource_name = 'message-list'
        fields = ['message', 'sender', 'recipient', 'created', 'id', 'conversation_id', 'username']
        authorization = Authorization()
        authentication = BasicAuthentication()
        include_resource_uri = False

    def get_object_list(self, request):
        return super(MessageResource, self).get_object_list(request).filter(Q(sender__user = request.user) | Q(recipient__user = request.user)).distinct('conversation_id').order_by('conversation_id','-created')

This works however! It's sending the data in alphabetical order and not by the most recent recent conversation. In order to use distinct, I have to use order_by. Problem is I don't want the data to be in alphabetical order. I want it to be in order of latest conversation. How can I go about doing this?

Answer 1

Your last line should be like this

return super(MessageResource, self).get_object_list(request).filter(Q(sender__user = request.user) | Q(recipient__user = request.user)).distinct('conversation_id').annotate(last_created=MAX(created)).order_by('-last_created')

In the code I have used MAX method which should be imported with the following:

from django.db.models import Max

By the way steve has a point! conversation_id seems a bit problematic to me.

Update 1

To use annotate with grouping, the values or values_list method should be called:

return super(MessageResource, self).get_object_list(request).filter(Q(sender__user = request.user) | Q(recipient__user = request.user)).values('conversation_id').annotate(last_created=Max(created), distinct=True).order_by('-last_created')