Django query: stuck trying to display only one instance of the model per id

I am writing a conversation module for a django app and I am failing desperately at buildin g a side menu that shows for each conversation:

  • the name of the recipient
  • the last message in the conversation
  • the timestamp of that last message

I am struggling to write an accurate query.

conversations = ChatRoom.objects.filter(building=building.building_id, participants__in=[user]).prefetch_related(
        'participants','chat_set').order_by('-chat__timestamp')

the issue with this query is that it returns one chatroom object per message, and therefore in template the following code:

<ul class="flex flex-col space-y-1 mt-4 -mx-2 overflow-y-auto" style="height:300px">
    <h2 class="my-2 mb-2 ml-2 text-lg text-gray-600">Chats</h2>
    {% for convo in conversations %}
            <li>
          {% if convo.chat_set.last.content %}
                {% for participant in convo.participants.all %}
                    {% if participant.id != request.user.id %}
                        <a href="{% url 'room' room_id=convo.id %}"
                            class="flex items-center px-3 py-2 text-sm transition duration-150 ease-in-out border-b border-gray-300 cursor-pointer hover:bg-gray-100 focus:outline-none">

                            <div class="w-10 h-10 rounded-full border-2 border-black flex justify-center items-center m-2">

                                <span> {{ participant.username|first|upper }}</span>
                            </div>

                            <div class="w-full pb-2">
                                <div class="flex justify-between">

                                    <span class="block ml-2 font-semibold text-gray-600"> {{ participant.username }}</span>

                                    <span class="block ml-2 text-sm text-gray-600">{{ convo.chat_set.last.timestamp}}</span>
                                </div>

                                <span class="block ml-2 text-sm text-gray-600">{{ convo.chat_set.last.content  }}</span>
                            </div>
                        </a>
                    {% endif %}
                {% endfor %}
            </li>
        {% for %}
    {% endfor %}
</ul>

shows one line per message sent, instead of showing one line per conversation with the latest message in it.

I honestly have no clue how to modify the query on the backend (trying the dinstinct() method does not work and I dont find a way either to twick what I have in the front to only show one item per conversation.

Any help is welcome and I am available to share more info if needed.

You can use the annotate() method in your query to get the latest message for each conversation, and then use that in your template to display the information. Here's an example of how you can modify your query:

from django.db.models import Max

conversations = ChatRoom.objects.filter(building=building.building_id, participants__in=[user]) \
                                .prefetch_related('participants', 'chat_set') \
                                .annotate(latest_message=Max('chat_set__timestamp')) \
                                .order_by('-latest_message')

The annotate() method allows you to add a new field to the queryset, which is computed by the provided expression. In this case, we are adding a new field called latest_message which is computed by taking the maximum timestamp of all the messages in the conversation.

Then in your template, you can use convo.latest_message to access the timestamp of the latest message, and convo.chat_set.last.content to access the content of the latest message.

You can also use distinct() method in your query to eliminate the duplicate results, like this:

conversations = ChatRoom.objects.filter(building=building.building_id, participants__in=[user]) \
                                .prefetch_related('participants', 'chat_set') \
                                .annotate(latest_message=Max('chat_set__timestamp')) \
                                .order_by('-latest_message') \
                                .distinct()

You can also use the values() method to group by the conversation and select the max timestamp, and then use that in your template.

conversations = ChatRoom.objects.filter(building=building.building_id, participants__in=[user]) \
                                .prefetch_related('participants') \
                                .values('id') \
                                .annotate(latest_message=Max('chat_set__timestamp')) \
                                .order_by('-latest_message') 

In this case, you will have to use the 'id' field to get the other fields of the conversation.

I hope this helps!

Back to Top