CODEWITHSUNDEEP
pythondjangodistincttastypie
deadlock
deadlock

Reputation: 7330

Getting the latest distinct object in Django

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?

Upvotes: 1

Views: 1180

Answers (2)

scriptmonster
scriptmonster

Reputation: 2769

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')

Upvotes: 3

Steve K
Steve K

Reputation: 11419

Well, there is a flaw in the way you set conversation ids. If you have 4 users, named alex, alexb, bob and ob, the conversation with alex and bob will have the same id as the one with alexb and ob...

About your question, I'm not sure. Your query is a bit complicated, you want to get only one Message per conversation_id, each Message being the latest for the conversation (Max(created)). I do think that you can ease your design around this, by actually adding a Conversation model (the primary key of this model can also be a concatenation of usernames), with an updated field. It will be far easier to manage, and will likely allow cheaper queries.

Your conversation model can be as simple as

class Conversation(models.Model):
    id = models.CharField(primary_key=True, max_length=64, blank=False)  # the primary key is a string
    updated = models.DatetimeField(auto_now=True, db_index=True)

Upvotes: 2

Related Questions