How to create a conversation inbox in Django

Question

I have a Message class which has fromUser, toUser, text and createdAt fields.

I want to imitate a whatsapp or iMessage or any SMS inbox, meaning I want to fetch the last message for each conversation.

I tried:

messages = Message.objects.order_by('createdAt').distinct('fromUser', 'toUser')

But this doesn't work because of SELECT DISTINCT ON expressions must match initial ORDER BY expressions error.

I don't really understand what it means, I also tried:

messages = Message.objects.order_by('fromUser','toUser','createdAt').distinct('fromUser', 'toUser')

and such but let me not blur the real topic here with apparently meaningless code pieces. How can I achieve this basic or better said, general well-known, result?

Answer 1

Your second method is correct. From the Django docs:

When you specify field names, you must provide an order_by() in the QuerySet, and the fields in order_by() must start with the fields in distinct(), in the same order.

For example, SELECT DISTINCT ON (a) gives you the first row for each value in column a. If you don’t specify an order, you’ll get some arbitrary row.

This means that you must include the same columns in your order_by() method that you want to use in the distinct() method. Indeed, your second query correctly includes the columns in the order_by() method:

messages = Message.objects.order_by('fromUser','toUser','createdAt').distinct('fromUser', 'toUser')

In order to fetch the latest record, you need to order the createdAt column by descending order. The way to specify this order is to include a minus sign on the column name in the order_by() method (there is an example of this in the docs here). Here's the final form that you should use to get your list of messages in latest-first order:

messages = Message.objects.order_by('fromUser','toUser','-createdAt').distinct('fromUser', 'toUser')