Как собрать контакт-лист из истории переписок?

Question

[Радость моя]

Есть история переписки между юзерами на сайте (таблица im)

id, user_from, user_to, message, dt_created
-----
1, 1, 2, hello, date
2, 2, 1, hey, date
...

как мне из этого составить список контактов (диалогов) чтобы он был уникальный? если например я user_id = 1.

Я пробовал так:

SELECT * FROM im WHERE 
user_to = :user_id OR user_from = :user_id
GROUP BY user_to, user_from
ORDER BY dttm ASC

но на выводе я среди прочего вижу записи где мне писала alice, и я писал alice - как две разные записи контакт-листа. а должно быть просто запись alice, неважно кто из нас кому писал. как в телеграме (и в любом другом мессенджере).

Comments

[Радость моя]

netyshka дада, я про вас и ваши скоростные запросы сразу вспомнил :)

[netyshka]

Радость моя, тааак приятно, мрмрмр

[netyshka]

Радость моя, нарисуй таблицу , что на выходе нужно получить

[Радость моя]

netyshka,

на выходе получить бы поля:

user_id (с кем чат?)
message (текст последнего сообщения)
dt_created (дата последнего сообщения)

в общем то и всё, для контакт листа хватит :)

[netyshka]

Радость моя, вот если писать в прогу, в которой я писала ваш пример предыдущий, вообще огонь бы было, но там парень вам по-моему уже ответил верно

[Радость моя]

netyshka, сейчас напишу в sqlfiddle. мне важен ваш вариант

[netyshka]

Радость моя, можно на ты)

[Радость моя]

netyshka, sqlfiddle.com/#!9/c4270a6/1

[netyshka]

Радость моя, теперь из данных которые ты забил, напиши что должно получиться, только не словами, а значениями из твоего insert

[netyshka]

Радость моя, я просто немного не понимаю что должно быть на выходе и в проге ты написал мало входных данных для теста, поэтому чтоб написать точный запрос , надо в проге ввести больше входных данных и тут написать из тех данных, которые ты ввел в проге, что должно получиться на выходе в виде таблицы, тогда мне станет понятно, что тебе нужно и я составлю запрос

[netyshka]

39 | 16:05:39 | bonjoure |

Так? На выходе

[Радость моя]

netyshka,

SELECT *, if(user_to = :current_user, user_from, user_to) AS recipient FROM im WHERE 
user_to = :current_user OR user_from = :current_user
GROUP BY recipient
ORDER BY dttm DESC

вот что мне нужно было :) все вроде ок, только почему то последнее сообщение не похоже на последнее.

[netyshka]

Радость моя, ты написал запрос, я просила таблицу со значениями выходными

[Радость моя]

netyshka, к этому запросу надо еще дописать join на вытасткивание имени recipient'a. я вот думаю как это лучше сделать, тут же в этом запросе, или обернуть его во внутренний и сделать join к уже готовому результату. типа:

select tmp.*, users.firstname from (запрос_который_выше) as tmp join users on tmp.recipient = users.id

[netyshka]

Радость моя, мне вообще не нравится запрос который выше) как-то сложно

[Радость моя]

netyshka, sqlfiddle.com/#!9/7151b1/2 вот что мне нужно было. теперь вопрос в быстродействии. т.к. юзеров может быть много, и соответственно сообщений между ними.

P.S. тостер перестал присылать уведомления о новых комментах :( мониторю переодически сам

[netyshka]

Радость моя, счас сделаю

[netyshka]

Радость моя, точнее не сделаю, потому что это тот случай в котором,я вообще не могу понять что тебе надо)) всмысле сам запрос, что формирует, если оптимизировать то запрос который ты вывел, избавится от SELECT * как минимум, вместо звездочки, напиши полностью наименование поля, ну и явно с датами\временем , что-то не так, не понятно , почему не попала запись :
(5, 39, 4, 2, '2020-01-30 16:40:36', 0, '4 to 2'),

Answer 1

[Aricus]

Причина ясна, вы группируете по уникальным значениям как первого, так и второго столбца:

GROUP BY user_to, user_from

Если MIN и MAX работают со строками, то должно сработать такое:

GROUP BY MAX(user_to, user_from), MIN(user_to, user_from)

Другой вариант, через тернарный оператор:

GROUP BY if(user_to = :user_id, user_from, user_to)

Comments

[Радость моя]

спасибо ща попробую.

PS. а причём тут сроки?

Если MIN и MAX работают со строками, то должно сработать такое

user_from и user_to это int в моём случае. или я не о том говорю?

[Радость моя]

Aricus, если я вас правильно понял - вы имели ввиду:

SELECT * FROM im WHERE 
user_to = 2 OR user_from = 2
GROUP BY GREATEST(user_to, user_from), LEAST(user_to, user_from)
ORDER BY dttm ASC

да это вроде работает, но будет ли это быстро?

[Радость моя]

Вот финальный результат для будущих поколений:

SELECT *, if(user_to = :current_user, user_from, user_to) AS recipient FROM im WHERE 
user_to = :current_user OR user_from = :current_user
GROUP BY recipient
ORDER BY dttm DESC

Answer 2

[netyshka]

SELECT
i.ad_id,i.message, tmp.recipient,IFNULL(u.first_name, 'Без имени') AS recipient_name,tmp.date_last
FROM im i
INNER JOIN (
SELECT IF(user_to = 2, user_from, user_to) AS recipient, max(dttm) as date_last
FROM im
WHERE user_to = 2 OR user_from = 2
GROUP BY recipient
) AS tmp ON i.dttm = tmp.date_last
LEFT JOIN user u ON tmp.recipient = u.id

смотри на дату и время, здесь оно верно, и он должен выполниться быстрее, как заказывали:)

Comments

[Радость моя]

я них не понял, но очень интересно

❤ спасибо, действительно быстро, всё в лучших традициях.

[netyshka]

Радость моя,
Запрос , который ты мне дал ссылку, не правильно выводил данные, если ты обратишь внимание на дату, ты как раз это увидишь, что должна попасть запись с временем 16:40 , т.к. оно последнее, а у тебя попало 16:15. Что говорит о неправильноом запросе