选择所有论坛并获取最新帖子。 [英] Select all forums and get latest post too.. how?

问题描述

我正在尝试编写查询以选择所有论坛并获取相应的最新帖子（包括作者）...但是我失败了。

I am trying to write a query to select all my forums and get the corresponding latest post (including the author)... but I failed.

这是我的结构：

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

当然，此查询是不正确的，因为一个线程中有很多帖子...

Of course this query is incorrect, because there are many posts in one thread...

有人可以帮忙吗？我正在使用PostgreSQL btw。

Can anyone help? I am using PostgreSQL btw.

哦：我忘记了：
目前我遍历所有类别（具有parent_forum = NULL的论坛），然后运行每个论坛的附加查询（这就是为什么您在查询中看到parent_forum = 1的原因）。有更好的方法吗？

Oh: I forgot: Currently I run through all "categories" (forums which have parent_forum = NULL) and then run an additional query for each forum (that's why you see parent_forum = 1 in my query). Is there a better way to do that?

编辑：
我的最后一篇帖子是 forum_posts 中updated_at中最新日期的帖子

My last post is the post with newest date in updated_at in forum_posts

推荐答案

DISTINCT ON 应该使此操作更容易：

DISTINCT ON should make this easier:

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 。

假设已定义列不为空。

According to your Q update the latest post is the one with the latest forum_posts.updated_at.
Assuming the column is defined NOT NULL.

详细说明：

在每个GROUP BY组中选择第一行？

这篇关于选择所有论坛并获取最新帖子。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！