I am having trouble writing a query using Django ORM, I want to find the latest record in each group. I am putting chat messages in the model and I want to find the latest chat of each user and show chats latest chat of each user and with the latest user's chat on the home screen just like in WhatsApp, Skype or similar apps. Currently, I am using the following query,

Chats.objects.all().order_by('user_id', '-date').distinct('user_id')

Using this I am able to get the latest chat of each user but I am not able to get the sequence correct. The result of the query is in the order of which the users were created in the database which I understand is correct, but I want to show the user who sent the latest chat at the top.

My Models.py

class Chats(models.Model):
    user_id = models.ForeignKey(User, on_delete=models.CASCADE)
    chat = models.CharField(max_length=1023, null=True, blank=True)
    date = models.DateTimeField(auto_now_add=True)

Thank you so much, Please let me know if any other information is required.

CodePudding user response：

Option 1: Order on the Django/Python layer

The items are first sorted by user_id, and only in case of a tie, it takes the one with the latest date. But that means that you eventually get for each user a Chats object, ordered by the user_id.

I think here your only option is to sort it at the Django/Python level, so wrap it into a list, and sort by the date:

from operator import attrgetter

items = list(Chats.objects.order_by('user_id', '-date').distinct('user_id'))
items.sort(key=attrgetter('date'), reverse=True)
# work with items

and then render the items in the template.

Option 2: Annotate the User model instead

Another option is to annotate the User model and thus work with a QuerySet of User objects:

from django.db.models import Max, OuterRef, Subquery

User.objects.filter(
    chats__isnull=False
).annotate(
    last_date=Max('chats__date'),
    last_message=Subquery(
        Chat.objects.filter(user_id=OuterRef('pk')).order_by('-date').value('chat')[:1]
    )
).order_by('-last_date')

Here the User objects will have an extra attribute .last_date with the latest date time of the object, and .last_message with that message.

Note: It is normally better to make use of the settings.AUTH_USER_MODEL [Django-doc] to refer to the user model, than to use the User model [Django-doc] directly. For more information you can see the referencing the User model section of the documentation.