我正试图写一个查询来选择我所有的论坛，并获得相应的最新帖子(包括作者)…但是我失败了。
这是我的结构:
forums forum_threads forum_posts
---------- ------------- -----------
id id id
parent_forum (NULLABLE) forum_id content
name user_id thread_id
description title user_id
icon views updated_at
created_at created_at
updated_at
last_post_id (NULLABLE)
这是我当前的查询:
SELECT forum.id, forum.name, forum.description, forum.icon, post_user.username
FROM forums AS "forum"
LEFT JOIN forum_posts AS "post" ON post.thread_id = (
SELECT id
FROM forum_threads
WHERE forum_id = forum.id
ORDER BY updated_at DESC LIMIT 1)
LEFT JOIN users AS "post_user" ON post_user.id = post.user_id
WHERE forum.parent_forum = 1
GROUP BY forum.id
当然这个查询是不正确的，因为在一个线程中有许多帖子…
有人能帮忙吗?我正在使用PostgreSQL btw.
哦:我忘了:目前，我通过所有的"类别"(论坛有parent_forum = NULL)，然后运行额外的查询每个论坛(这就是为什么你看到parent_forum = 1在我的查询)。有更好的方法吗?
编辑:我的最后一篇文章是在forum_posts
DISTINCT ON应该使这更容易:
SELECT DISTINCT ON (f.id)
f.id, f.name, f.description, f.icon, u.username
FROM forums f
LEFT JOIN forum_threads t ON t.forum_id = f.id
LEFT JOIN forum_posts p ON p.thread_id = t.id
LEFT JOIN users u ON u.id = p.user_id
WHERE f.parent_forum = 1
ORDER BY f_id, p.updated_at DESC;
根据你的Q更新，最新的帖子是最新的forum_posts.updated_at。
假设列定义为NOT NULL
详细解释:选择每个GROUP BY组的第一行?
这是你想要的吗?您可以使用子查询获得给定论坛的最新帖子。
SELECT forums.id, forums.name, forums.description, forums.icon,
(SELECT username FROM forum_threads AS ft
JOIN forum_posts AS fp ON ft.id = fp.thread_id
JOIN users AS u ON fp.user_id = u.user_id
WHERE ft.forum_id = forums.id
ORDER BY updated_at DESC LIMIT 1) AS username_of_latest_post
FROM forums