I'm trying to do a private message functionnality.

My goal is: a authenticated user go to the messages url, where he can see: every people he sent or received a message from, with every conversation grouped by the user, with the last message shown and the date of the last message.

My problem is: I actually retrieved the messages, but I can't find a way to only show the user one time (for example, if you sent the recipient a message, and he sent you one, the conversation is displayed two times, one for when you sent him a message, and one when he sent you one.)

I don't really know how to explain this clearly, but I want to do something like twitter/messages.

Database table structure image, with the foreign keys linked to user_id in user table

What I tried:

• Repository:

     /**
     * @return Message[] Returns an array of Message objects
     */
    public function findByUser($user)
    {
        return $this->createQueryBuilder('m')
            ->setParameter('user', $user)
            ->where('m.sender = :user')
            ->orWhere('m.recipient = :user')
            ->groupBy('m.recipient')
            ->orderBy('m.date', 'DESC')
            ->getQuery()
            ->getResult();
    }

• Controller:

#[Route('/messages', name: 'app_messages')]
    public function index(MessageRepository $messageRepository): Response
    {
        return $this->render('messages/index.html.twig', [
            'messages' => $messageRepository->findByUser($this->getUser()),
            'controller_name' => 'MessagesController',
        ]);
    }

• Twig:

{% for message in messages %}
    {% if message.sender.id == app.user.id %}
        {% set displayName = message.recipient %}
    {% elseif message.recipient.id == app.user.id %}
        {% set displayName = message.sender %}
    {% endif %}

    <a href="{{ path('app_messages_show', {id: message.id}) }}">
        <p>{{ displayName.name }}</p>
        <p>{{ displayName.username }}</p>
        <p>{{ message.date|date }}</p>
        <p>{{ message.content }}</p>
    </a>
{% else %}
    <p>You don't have any message