There is a table of messages like:

|id|sender_id|recipient_id| date_sent | _______________________________________________ |1 | 100 | 101 |2017-03-02 08:03:45| ----------------------------------------------- |2 | 100 | 101 |2017-03-02 08:10:45| ----------------------------------------------- |3 | 101 | 100 |2017-03-02 09:55:01|

And so on. To decrypt:

  1. Message ID
  2. Sender ID
  3. ID of the message recipient
  4. Date of posting There are more fields of the type of the post itself, but it’s useless.

Need to:

Display dialogs for a specific user with each other user with whom he spoke. (messages vkontakte for example). Those. I need to leave the last message with each user. Moreover, if (as I tried) to hang a group by sender_id, then in the case of an outgoing message, everything is OK, and if the messages are incoming (no answer), they bypass group by. I tried all this through MAX according to this advice. In theory, it seems understandable, but in practice everything goes into the abyss. I have 2 identifiers and how to turn them into one (so that the request understands that 100 and 101, and 101 and 100 is one dialogue. I would be very grateful for any help.

    3 answers 3

    Try this:

     SELECT * FROM dialogs WHERE (LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id), date_sent) IN ( SELECT LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id), MAX(date_sent) FROM dialogs WHERE @user IN (sender_id, recipient_id) GROUP BY LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id) )
    • Cool! It works, although there is not enough data in the table to check everything dramatically, I will test it. I thought I understand sql queries, but did not understand what you did here. Prompt the literature to easily make such requests ?? - Alex
    • Yes, the simplest query ... there is no need for any literature, bare logic. And in order with ease it is an analysis of solutions of various real-world problems proposed on this and other sites, plus practice (try for the same tasks before reading the answers to come up with your own solution or at least an algorithm). - Akina

    Try this approach (I now, unfortunately, have nothing to check):

     SELECT all_dialogs.sender_id, all_dialogs.recipient_id, MAX(all_dialogs.date_sent) FROM ( SELECT sender_id, recipient_id, date_sent FROM dialogs WHERE sender_id = @user_id GROUP BY sender_id, recipient_id UNION ALL SELECT recipient_id, sender_id, date_sent FROM dialogs WHERE recipient_id = @user_id GROUP BY sender_id, recipient_id ) all_dialogs GROUP BY all_dialogs.sender_id, all_dialogs.recipient_id
    • Messages are displayed correctly (by the number of dialogs you have), but not by the last date - Alex

    That request understood that 100 and 101, and 101 and 100 is one dialogue

    Your current data structure is extremely inconvenient for this purpose.
    As a bonus - it creates insurmountable obstacles when trying to organize a group chat.

    I propose another structure:

     Conversation (id, created, created_by) Participants (conversation_id, user_id) Messages (id, conversation_id, user_id(или participant_id), date_sent, content)

    Those. Entities of the dialogues themselves, participants of the dialogues, and the messages themselves appear.

    Then your task will be solved for example:

     SELECT m.id FROM Conversation c JOIN Participants p ON c.id = p.conversation_id JOIN Messages m ON m.conv_id = c.id WHERE p.user_id = :user_id GROUP BY c.id HAVING m.date_sent = MAX(m.date_sent) ORDER BY m.date_sent DESC

    On a real data structure, it will be possible to reflect on optimization.

    • Alas, the application is ready and large, not written by me. Changing the structure means changing a bunch of php classes and methods. - Alex