#mysql #unique #messaging #messages
#mysql #уникальный #обмен сообщениями #Сообщение
Вопрос:
У меня есть messages таблица, которая структурирована примерно так:
from | to | date
-----------------------------------
1 | 3 | 2011-09-23 11:51:44
3 | 1 | 2011-09-23 11:56:29
3 | 2 | 2011-10-04 10:20:01
2 | 3 | 2011-10-05 07:48:00
Я хочу отобразить страницу сообщений на моем веб-сайте, очень похожую на страницу Facebook messages, на которой отображается список людей, с которыми пользователь общается. Независимо от глубины разговора, страница показывает каждого пользователя только один раз, вместе с самым последним сообщением в этом разговоре с этим конкретным человеком, было ли оно отправлено или получено.
Часть, которая ставит меня в тупик, заключается в том, что самое последнее сообщение может быть либо отправлено, либо получено, что означает, что идентификационный номер пользователя может быть в любом из столбцов from или to . Я не уверен, как протестировать оба столбца так, как мне нужно.
Я все еще учусь писать более сложные запросы MySQL, и хотя мне кажется, что это простой случай использования OR с подзапросами, я, похоже, не могу понять это правильно.
РЕШЕНИЕ Оказывается, что это был совсем не простой случай. Widor потребовалось некоторое время, чтобы помочь мне с этим, и следующий запрос, наконец, кажется, справился с задачей. Он не был тщательно протестирован, но пока, похоже, работает нормально:
SELECT m.*
FROM messages m
JOIN (SELECT Max(x.id) AS `id`,
x.userid,
x.partnerid,
Max(x.mostrecent) AS `mostrecent`
FROM (SELECT Max(id) `id`,
`from` AS `userid`,
`to` AS `partnerid`,
Max(`created`) AS `mostrecent`
FROM messages
GROUP BY `from`,
`to`
UNION
SELECT Max(id) `id`,
`to` AS `userid`,
`from` AS `partnerid`,
Max(`created`) AS `mostrecent`
FROM messages
GROUP BY `to`,
`from`) AS `x`
GROUP BY x.`userid`,
x.`partnerid`) AS `y`
ON y.id = m.id
WHERE y.userid = $userid
Комментарии:
1. Что-то не так с этим вопросом 🙂
2. вы можете объединить таблицу с самой собой, используя from = to, и посчитать отдельную дату
3. Помимо благодарностей и тегов в заголовке…
4. Что вы пробовали и каков был результат? Это вопрос типа «сделай это для меня».
5. @Tomalakgeret’kal, я понимаю, что вы имеете в виду. Дело в том, что я очень быстро попробовал несколько разных вещей за короткий промежуток времени, и когда что-то не работает, я не считаю нужным отмечать это, поэтому я действительно не помню все, что я пробовал. В следующий раз я постараюсь быть более тщательным.
Ответ №1:
ОТРЕДАКТИРОВАНО ЗАНОВО
Мой предыдущий ответ (как и некоторые другие здесь) не учитывал случай, когда у вас более 2 «партнеров» по разговору, чего нет в вашем примере данных, но я уверен, что так будет в реальной жизни.
Итак, рассмотрим случай, когда у вас теперь есть дополнительная запись в данных:
1 | 4 | 2011-10-04 08:34:12
Мой пересмотренный запрос выглядит следующим образом:
SELECT userid, partnerid, max(mostRecent) from (
SELECT [from] as [userid], [to] as [partnerid], max([date]) as [mostrecent] FROM messages GROUP BY [from], [to]
UNION
SELECT [to] as [userid], [from] as [partnerid],max([date]) as [mostrecent] FROM messages GROUP BY [to], [from]
) [x]
WHERE userid = ?
GROUP BY userid, partnerid
Итак, наш внутренний UNION предоставляет нам набор данных, содержащий userid наряду с partnerid , то есть с кем они общаются. Это может быть создано отдельно как представление, если вы хотите, для упрощения запроса.
Затем внешний SELECT извлекает по одной записи для каждого «партнера», с которым указанный userid общался, вместе с самой последней датой.
Max() Функция получает самую последнюю дату, и GROUP BY заботится о том, чтобы мы вернули более одной записи для каждого партнера.
Комментарии:
1. На самом деле, только что понял, что вам не нужны дополнительные данные в примере.
3обращается к1и2, и поэтому должно быть возвращено 2 записи.2. Это очень важный момент, и я полностью упустил его из виду. Было бы несколько человек, с которыми вы бы общались. Просто делает это более сложным для меня. Я попробую ваш запрос и сообщу вам, как это работает для меня.
3. Сначала я не понял, что мне пришлось изменить ваши скобки [] на `. В любом случае, я получил его для успешного возврата результатов, но я не упомянул очень важную часть всего этого. Мне нужно иметь возможность извлекать всю строку, а не только те столбцы, которые мы используем для их поиска. Я прошу прощения за то, что забыл упомянуть эту часть.
4. Упс, похоже, что происходит много ошибок при чтении — я использовал нотацию SQl Server, а не мой SQL! В любом случае, я отредактирую свой пост, чтобы показать вам, как получить всю строку, но есть ли у вас первичный ключ в таблице?
5. Похоже, я смог просто добавить несколько * здесь и там, чтобы извлечь все столбцы. Однако есть проблема. По какой-то причине при этом не извлекаются самые последние сообщения. Он извлекает самую старую.
Ответ №2:
Похоже, вы хотите что-то вроде:
SELECT MAX(date), * FROM messages WHERE to IN (
SELECT DISTINCT to FROM messages WHERE from = :id
) GROUP BY to
UNION
SELECT MAX(date), * FROM messages WHERE from IN (
SELECT DISTINCT from FROM messages WHERE to = :id
) GROUP BY from
ORDER BY 1
Где id параметр, который вы используете для идентификатора пользователя.
Ответ №3:
вы можете создать представление и запросить его:
CREATE VIEW dataview AS
(SELECT t1.from AS userid, t1.date FROM messages AS t1)
UNION
(SELECT t2.to AS userid, t2.date FROM messages AS t2)
посмотрите на таблицу результатов, теперь должно быть очень легко запросить то, что вы хотите
Ответ №4:
Я думаю, вы, вероятно, хотите что-то вроде этого:
SELECT MAX(date)
FROM messages
WHERE from = $userid
OR to = $userid
Вы бы заменили $userid фактическим значением, надеюсь, используя подготовленные инструкции . 🙂