SQL: выберите последнюю тему и последнее сообщение, сгруппированные по форуму, отсортированные по последнему сообщению

Я пытаюсь получить

  • последний поток (id, topic, timestamp, author_id) и
  • последнее сообщение (id, thread_id, timestamp, author_id)
  • каждого форума (id, name)
  • упорядоченный по последнему сообщению , независимо от созданного файла потока.

Зачем?

Я хотел бы иметь возможность отображать такие детали, как:

"The latest Answer of forum $forum_id was given on Question $thread_id. Here it is: $post_id" 

 SELECT f.id AS forum_id, f.name AS forum_name, t.id AS thread_id, t.topic AS thread_topic, t.ts AS thread_timestamp, p.id AS post_id, p.content AS post_content, p.ts AS post_timestamp FROM forums f, threads t, posts p WHERE f.id = t.forum_id AND t.id = p.thread_id GROUP BY f.id ORDER BY p.ts 

Какие-нибудь советы, как изменить SQL, чтобы получить желаемый результат как можно больше? Я пытаюсь избежать подзапросов, но я открыт!

Заранее спасибо!

Поскольку MySQL не поддерживает функции окна, я не думаю, что есть какой-либо способ сделать это без подзапроса:

 SELECT f.id AS forum_id, f.name AS forum_name, t.id AS thread_id, t.topic AS thread_topic, t.ts AS thread_timestamp, p.id AS post_id, p.content AS post_content, p.ts AS post_timestamp FROM forums f JOIN (SELECT t2.forum_id, max(p2.ts) as ts FROM posts p2 JOIN threads t2 ON p2.thread_id = t2.id GROUP BY t2.forum_id) max_p ON f.id = max_p.forum_id JOIN posts p ON max_p.ts = p.ts JOIN threads t ON f.id = t.forum_id AND p.thread_id = t.id ORDER BY p.ts 

Естественно, кеширование последних результатов позволит вам сделать это без снижения производительности при вызове MAX (), но с правильными индексами это не должно быть большой проблемой …

ОБНОВИТЬ

Самый сжатый способ включения потоков без сообщений и форумов без потоков – использовать LEFT JOINs вместо INNER JOINs:

 SELECT f.id AS forum_id, f.name AS forum_name, t.id AS thread_id, t.topic AS thread_topic, t.ts AS thread_timestamp, p.id AS post_id, p.content AS post_content, p.ts AS post_timestamp FROM forums f LEFT JOIN (SELECT t2.forum_id, max(COALESCE(p2.ts, t2.ts)) as ts, COUNT(p2.ts) as post_count FROM threads t2 LEFT JOIN posts p2 ON p2.thread_id = t2.id GROUP BY t2.forum_id) max_p ON f.id = max_p.forum_id LEFT JOIN posts p ON max_p.ts = p.ts LEFT JOIN threads t ON f.id = t.forum_id AND (max_p.post_count = 0 OR p.thread_id = t.id) ORDER BY p.ts 

Я могу думать о двух «правильных» способах этого. Первый использует соединения и подзапросы:

 SELECT f.id AS forum_id, f.name AS forum_name, t.id AS thread_id, t.topic AS thread_topic, t.ts AS thread_timestamp, p.id AS post_id, p.content AS post_content, p.ts AS post_timestamp FROM forums f join threads t on f.id = t.forum_id join posts p on t.id = p.thread_id WHERE t.ts = (select ts from threads t2 where t2.forum_id = t.forum_id order by ts desc limit 1) and p.ts = (select ts from posts p2 where p2.thread_id = p.thread_id order by ts desc limit 1) GROUP BY f.id ORDER BY max(p.ts) 

Проблема с этим подходом заключается в том, что это возвращает самый последний поток и последнее сообщение в этом потоке. Исправить это громоздко (и это может быть то, что вы действительно хотите).

Подзапросы получают последнюю дату для threads и posts . Производительность зависит от индексов, которые у вас есть. Это может быть приемлемо. Это стандартный SQL.

Другой – это трюк с substring_index() / group_concat() , который специфичен для MySQL:

 SELECT f.id AS forum_id, f.name AS forum_name, substring_index(group_concat(t.id order by t.ts desc separator '|'), '|', 1) AS thread_id, substring_index(group_concat(t.topic order by t.ts desc separator '|'), '|', 1) AS thread_topic, substring_index(group_concat(t.ts order by p.ts desc separator '|'), '|', 1) AS thread_timestamp, substring_index(group_concat(p.id order by p.ts desc separator '|'), '|', 1) AS post_id, substring_index(group_concat(p.content order by p.ts desc separator '|'), '|', 1) AS post_content, substring_index(group_concat(p.ts order by p.ts desc separator '|'), '|', 1) AS post_timestamp FROM forums f join threads t on f.id = t.forum_id join posts p on t.id = p.thread_id GROUP BY f.id ORDER BY max(p.ts); 

Эта версия может работать лучше (потому что вы уже набираете накладные расходы group by ). Символ разделителя должен быть выбран таким образом, чтобы он не находился ни в одном из значений. В противном случае появится только часть перед разделителем.

Одно из преимуществ заключается в том, что потоки и столбцы обрабатываются независимо, поэтому вы получаете самый последний поток и, отдельно, самую последнюю запись. Вы можете получить самую последнюю запись в заданном потоке, изменив order by условиям в group_concat() .

Кроме того, чтобы получить заказ, который вам нужен, вам нужно заказать max(p.ts) а не просто p.ts Последний заказывал произвольный штамп времени на форуме; нет никакой гарантии, что она будет самой последней.