Столы

форумы

• Идентификационный код на форуме
• название

потоки

• thread_id
• Идентификационный код на форуме
• Логин пользователя
• add_ts

thread_replies

• thread_reply_id
• thread_id
• Логин пользователя
• add_ts

пользователи

• Логин пользователя
• имя пользователя

Эта проблема

Я хочу вернуть список форумов с последним сообщением для каждого. Эффективно. Часть проблемы заключается в том, что сообщения разделены между потоками и thread_replies.

Мой первый инстинкт — получить UNION потоков и thread_replies с данными, которые мне нужны:

SELECT * FROM ((SELECT forum_id, thread_id, user_id, added_ts FROM threads t)
UNION (SELECT t1.forum_id, r.thread_id, r.user_id, r.added_ts FROM thread_replies r 
      INNER JOIN threads t1 ON t1.thread_id = r.thread_id)) messages;

Теперь у меня есть список всех сообщений с forum_id, thread_id, user_id и add_ts. Следующим моим побуждением было присоединиться к этой таблице в таблице forums, но я не вижу четкого способа сократить это до самого последнего значения для add_ts для каждого форума, при этом возвращая остальные необходимые данные.

SELECT * FROM forums f 
INNER JOIN 
  (SELECT messages.*, users.username FROM
     ((SELECT forum_id, thread_id, user_id, added_ts FROM threads t) 
       UNION 
      (SELECT t1.forum_id, r.thread_id, r.user_id, r.added_ts 
       FROM thread_replies r 
       INNER JOIN threads t1 ON t1.thread_id = r.thread_id)) messages 
       INNER JOIN users ON messages.user_id = users.user_id 
       ORDER BY messages.added_ts) last_replies ON last_replies.forum_id = f.forum_id;

Я попробовал GROUP BY forum_id в сочетании с MAX(last_replies.added_ts), но это единственные два поля результирующего набора, которые я могу выбрать с помощью группировки и агрегатных функций, насколько я могу судить.

SELECT f.forum_id, MAX(last_replies.added_ts)
FROM forums f
INNER JOIN (
   SELECT messages.*, users.username
   FROM (
      (SELECT forum_id, thread_id, user_id, added_ts
       FROM threads t)
      UNION
      (SELECT t1.forum_id, r.thread_id, r.user_id, r.added_ts
       FROM thread_replies r
       INNER JOIN threads t1 ON t1.thread_id = r.thread_id)) messages
       INNER JOIN users ON messages.user_id = users.user_id
      ORDER BY messages.added_ts
        ) last_replies ON last_replies.forum_id = f.forum_id GROUP BY f.forum_id;

Я думаю, что может быть способ сделать это, используя ORDER BY add_ts DESC и LIMIT 1, но я тоже не вижу, как это сделать. Я надеюсь получить некоторые хорошие идеи здесь, которые могут направить меня в правильном направлении.

Решение

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

SELECT f.*, last_replies.*, u.username 
FROM forums f 
INNER JOIN (
    SELECT DISTINCT ON (messages.forum_id) messages.*
    FROM (
        (SELECT forum_id, thread_id, user_id, added_ts FROM threads t)
    UNION
        (SELECT t1.forum_id, r.thread_id, r.user_id, r.added_ts FROM thread_replies r INNER JOIN threads t1 ON t1.thread_id = r.thread_id)
    ) messages
   ORDER BY messages.forum_id, messages.added_ts DESC
) last_replies ON last_replies.forum_id = f.forum_id
INNER JOIN users u ON last_replies.user_id = u.user_id;