Выборка последих сообщений

Jake Badland · 7 Июл 2016

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

Есть таблица "messages"
Поля:
message_id, from_id, to_id, date, message_text, read
Необходимо получить 15ть последних сообщений так, чтобы
read = 0 были первыми
ORDER BY date DESC
От каждого from_id только одно сообщение.
Как только я делаю GROUP BY from_id - возвращает случайное сообщение вместо того, чтобы выдать последнее.

Kotofey · 7 Июл 2016

Jake Badland написал(а):
От каждого from_id только одно сообщение.

Зачем ты делаешь группировку по from_id?
сделал бы сортировку по date DESC и по read ASC и используй LIMIT, вот и решение задачи.

Jake Badland · 7 Июл 2016

Kotofey написал(а):
Зачем ты делаешь группировку по from_id?
сделал бы сортировку по date DESC и по read ASC и используй LIMIT, вот и решение задачи.

Мне необходимо из множества сообщений выбрать по одному последнему от каждого from_id
Т.е. я мог получить несколько сообщений подряд от одного пользователя.
Впрочем, также, как и отправить несколько.

Kotofey · 7 Июл 2016

Jake Badland написал(а):
Мне необходимо из множества сообщений выбрать по одному последнему от каждого from_id
Т.е. я мог получить несколько сообщений подряд от одного пользователя.

"От каждого from_id только одно сообщение." - решил, что идется о данных в таблице, а не о задаче.
хорошо, тогда делай ту сортировку о которой я написал и использование limit + твоя group by from_id

Jake Badland · 7 Июл 2016

Kotofey написал(а):
"От каждого from_id только одно сообщение." - решил, что идется о данных в таблице, а не о задаче.
хорошо, тогда делай ту сортировку о которой я написал и использование limit + твоя group by from_id

Видимо ты не до конца прочитал задачу.
Во первых я в самом начале написал, что надо:
"От каждого from_id только одно сообщение."
Во вторых :
"Как только я делаю GROUP BY from_id - возвращает случайное сообщение вместо того, чтобы выдать последнее."

Kotofey · 7 Июл 2016

Jake Badland написал(а):
Видимо ты не до конца прочитал задачу.
Во первых я в самом начале написал, что надо:
"От каждого from_id только одно сообщение."
Во вторых :
"Как только я делаю GROUP BY from_id - возвращает случайное сообщение вместо того, чтобы выдать последнее."

покажи sql запрос который ты выполняешь.

Jake Badland · 7 Июл 2016

SELECT *
FROM utf_message
WHERE message_id IN (
SELECT message_id
FROM utf_message
WHERE (from_id = 19908 OR to_id = 19908)
)
ORDER BY readed ASC, date DESC

Kotofey · 7 Июл 2016

Твой запрос вовсе не соответствует задаче описанной в первом соообщении + не тот порядок столбцов в ORDER BY + отсутствие GROUP BY, + отсутствие LIMIT,
такое чувство, что ты отправил рандомный запрос,у тебя должно выйти что-то в виде этого:
select * from ... group by from_id order by date DESC ,readed ASC limit 15

Jake Badland · 7 Июл 2016

Окей гугл.
SELECT
utf_message.*,
utf_author.author_id,
CASE
WHEN utf_author.account_type = '0' then ''
WHEN utf_author.account_type = '1' then 'silver'
WHEN utf_author.account_type = '2' then 'gold'
END AS account_type_text,
TRIM(CONCAT(utf_author.name_first,' ',utf_author.name_last)) AS full_name,
IF (avatar != 0,
CONCAT('http://$domain/avatar_big/',DATE_FORMAT(DATE(FROM_UNIXTIME(avatar)),'%Y/%m/%d'),'/',author_id,'-',avatar,'.jpg'),
'$default_avatar'
) AS avatar_url,
IF (utf_author.set_invisible_mode = 1, 'true', 'false') AS user_invisible
FROM
utf_message
INNER JOIN utf_author ON utf_author.author_id = utf_message.from_id OR utf_author.author_id = utf_message.to_id
WHERE message_id IN (
SELECT message_id
FROM utf_message
WHERE (from_id = 111 OR to_id = 111)
)
GROUP BY utf_message.from_id, utf_message.to_id
ORDER BY readed ASC, date DESC
LIMIT 15

Зачем цепляться к деталям если и так суть ясна ?

antson · 7 Июл 2016

сделать 2 запроса ?

так как план выполнения у единичного мне не нравиться совсем

Код:

SELECT *
FROM pm
WHERE id
IN (

SELECT mid
FROM (

SELECT 0 AS flag, MAX( id ) AS mid, otkogo
FROM  `pm`
WHERE  `read` =  '0000-00-00 00:00:00'
GROUP BY otkogo
UNION SELECT 1 AS flag, MAX( id ) AS mid, otkogo
FROM  `pm`
WHERE  `read` >  '0000-00-00 00:00:00'
GROUP BY otkogo
ORDER BY flag, mid DESC
LIMIT 15
)a
)

Jake Badland · 7 Июл 2016

antson написал(а):
сделать 2 запроса ?
так как план выполнения у единичного мне не нравиться совсем

Да мне и самому не нравится))
Хм, а в этом есть смысл. Спасибо, завтра попробую.
Я тоже думал про UNION, но не мог придумать как к нему лимиты прилепить)

kkn1960 · 7 Июл 2016

Попробуй такой запрос

Код:

select t.* from messages t join
  (select max(message_id) last_id ,from_id from messages group by from_id order by `read` ASC,  date DESC  limit 15) t1  on t.from_id=t1.from_id and t.message_id=t1.last_id order by `read` ASC,  date DESC

AnrDaemon · 7 Июл 2016

GROUP BY выполняется до сортировки. Так что без второго запроса ничего не выйдет в принципе.
@kkn1960, то же самое. Он у тебя СНАЧАЛА сгруппирует, а ПОТОМ будет искать максимальные.
С GROUP BY можно использовать только аггрегаторы. AVG(), SUM(), COUNT().

kkn1960 · 7 Июл 2016

AnrDaemon написал(а):
С GROUP BY можно использовать только аггрегаторы. AVG(), SUM(), COUNT().

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

AnrDaemon · 7 Июл 2016

Хм. Оплошал. Каюсь. Забыл про этот момент.

Jake Badland · 8 Июл 2016

Всем спасибо)
Финал получился таким:

Код:

SELECT utf_message.*
FROM utf_message JOIN
(
SELECT MAX(message_id) AS last_id,
from_id
FROM utf_message
WHERE (to_id = $author_id)
GROUP BY from_id
ORDER BY readed ASC, date DESC
LIMIT $offset, $limit ) temp ON utf_message.from_id = temp.from_id AND utf_message.message_id = temp.last_id
UNION
SELECT utf_message.*
FROM utf_message JOIN
(
SELECT MAX(message_id) AS last_id,
to_id
FROM utf_message
WHERE from_id = $author_id GROUP BY to_id
ORDER BY readed ASC, date DESC
LIMIT $offset, $limit ) temp2 ON utf_message.to_id = temp2.to_id AND utf_message.message_id = temp2.last_id
ORDER BY readed ASC, date DESC

Вполне возможно, что не идеал, но пашет)
Я получаю все, сообщения для меня и от меня и на пыхе вырезаю лишние ответы от меня или мне))))

antson · 8 Июл 2016

на страницах начиная со 2ой тебя ждет сюрпрайз %)

Jake Badland · 8 Июл 2016

Не, все нормально) Конечно получается, что количество плавает от страницы к странице, но в итоге все счастливы)

UPD: Хотя, Antson, посмотрел еще раз на ваш запрос и закралось желание опять переписать))))

antson · 8 Июл 2016

@Jake Badland, там не плавает а теряется порция прочитанных.

сделай тупо
1.сколько прочитанных
2. сколько не прочитанных
3. получили из их суммы кол-во страниц всего
4 для текущей страницы
решаем микс тут или обойдемся запросом к первому или второму
5. находим айдишники писем
6. выбираем записи из таблицы по ид в порядке их следования в списке

Jake Badland · 8 Июл 2016

Так они, собственно, и не нужны.
"От каждого from_id только одно сообщение." Тоже надо и для тех сообщений которые отправил я.
Это по сути построение листа контактов с которыми велось общение, плюс само сообщение которое я отправил или получил.
После клика на определенный я уже выстраиваю диалог.
Подгрузка следующей порции по запросу, сколько их будет не суть важно т.к. я уже смогу подгрузить сколько надо. Смысл был лишь в том, чтобы дробить на определенные порции, а не выдавать всей кучей сразу.
Ну и количество порций мне тоже не важно, пагинации тут не будет. Даже в планах)

Конечно сделать запрос таким образом, чтобы всегда получать строго необходимое количество было бы идеальным, но я не перфекционист)))
Исключительно ради спортивного интереса еще попробую)

Выборка последих сообщений

Новичок

FloodMaster.

Новичок

FloodMaster.

Новичок

FloodMaster.

Новичок

FloodMaster.

Новичок

Новичок

Новичок

Новичок

Продвинутый новичок

Новичок

Продвинутый новичок

Новичок

Новичок

Новичок

Новичок

Новичок