MySQL Inbox запрос, возвращающий двойные результаты
Используя PHP и MySQL, я создаю систему обмена сообщениями «Входящие», и она работает, но в некотором смысле возвращает двойные значения. В папке «Входящие» должен отображаться разговор между двумя людьми с последним отправленным сообщением. Пользователь может нажать на это последнее сообщение и пообщаться с ним. Прямо сейчас в папке «Входящие» отображается последнее сообщение, отправленное пользователем 1, если пользователь 1 отправляет сообщение пользователю 2, И оно показывает последнее сообщение, если пользователь 2 отправил сообщение пользователю 1. Если пользователь 1 ИЛИ пользователь 2 отправил сообщение друг другу, я просто хочу показать ПОСЛЕДНЕЕ сообщение отправлено одним из них. Вот мой запрос, я хотел сделать это самостоятельно, но мои навыки SQL немного притупились.
Вот моя таблица сообщений:
Вот моя таблица пользователей:
И наконец, вот запрос:
SELECT m.body, m.createddate,m.recuserid,m.createduserid,
FROM( SELECT MAX(createddate) as maxdate
from
messages
group by recuserid,createduserid
) c
inner join messages m on m.createddate = c.maxdate
inner join users u on u.userid = m.createduserid
WHERE createduserid = 143 OR recuserid = 143
И вот результаты, которые он возвращает:
Как видите, он возвращает любое сообщение, которое каким-либо образом связано с пользователем 143. Мои желаемые результаты — вернуть только 1-й и 3-й ряд.
Решение
Я думаю, что это то, что вы после:
SELECT m.`body`,
MAX(m.`createddate`) AS maxcreateddate,
m.`pairid`
FROM (SELECT `body`,
`createddate`,
CONCAT(`recuserid`,`createduserid`) as pairid
FROM `messages`
WHERE `recuserid`=143
UNION
SELECT `body`,
`createddate`,
CONCAT(`createduserid`,`recuserid`) as pairid
FROM `messages`
WHERE `createduserid`=143) as m
GROUP BY pairid;
Кто-то может поджарить меня по поводу производительности, но это даст вам результат, который вы ищете (только первый и третий ряды).
Надеюсь, поможет 😉
Другие решения
SELECT m.body, m.createddate,m.recuserid,m.createduserid,
FROM ( SELECT MAX(createddate) as Maxdate, recuserID, createduserId
FROM MESSAGES
GROUP BY recuserid,createduserid) c
INNER JOIN messages m
on m.createddate = c.Maxdate
and m.RecUserID = C.RecuserId
and m.CreateduserId = C.CreatedUserId
INNER JOIN users u
on u.userid = m.createduserid
WHERE createduserid = 143
Оператор OR, который у вас есть, является причиной возврата второй строки. как @MarcB указано в первоначальном комментарии.
Однако из-за возможных совпадений по дате / времени вы должны вернуть то, что вы группируете, и добавить его к критериям объединения.
Может быть, вы просто хотите самое последнее сообщение, отправленное или полученное пользователем ….
SELECT m.body, m.createddate,m.recuserid,m.createduserid,
FROM ( SELECT MAX(createddate) as Maxdate,
FROM MESSAGES
where createdUserID =143 OR RecUserID = 143) c
INNER JOIN messages m
on m.createddate = c.Maxdate
INNER JOIN users u
on u.userid = m.createduserid
WHERE createduserid = 143