选择所有线程并按最新线程排序 [英] Select all threads and order by the latest one
问题描述
现在，我得到了选择所有论坛并获取最新信息帖子也..如何？问题回答了，我正在尝试编写查询以选择一个特定论坛中的所有线程，并按最新帖子的日期对其进行排序（列 updated_at ）。
Now that I got the Select all forums and get latest post too.. how? question answered, I am trying to write a query to select all threads in one particular forum and order them by the date of the latest post (column "updated_at").
这又是我的结构：
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)
我尝试编写此查询，但它可以工作。与预期不同：它没有按线程的最后发布日期排序：
I tried writing this query, and it works.. but not as expected: It doesn't order the threads by their last post date:
SELECT DISTINCT ON(t.id) t.id, u.username, p.updated_at, t.title
FROM forum_threads t
LEFT JOIN forum_posts p ON p.thread_id = t.id
LEFT JOIN users u ON u.id = p.user_id
WHERE t.forum_id = 3
ORDER BY t.id, p.updated_at DESC;
我该如何解决？
推荐答案
假设每个线程需要一个单行，而不是所有帖子都需要所有行。
Assuming you want a single row per thread and not all rows for all posts.
DISTINCT ON 仍然是最方便的工具。但是前导 ORDER BY 项必须匹配 DISTINCT ON 子句的表达式。如果要以其他方式订购结果，则需要将其包装到子查询中，然后向外部查询中添加另一个 ORDER BY ：
DISTINCT ON is still the most convenient tool. But the leading ORDER BY items have to match the expressions of the DISTINCT ON clause. If you want to order the result some other way, you need to wrap it into a subquery and add another ORDER BY to the outer query:
SELECT *
FROM (
SELECT DISTINCT ON (t.id)
t.id, u.username, p.updated_at, t.title
FROM forum_threads t
LEFT JOIN forum_posts p ON p.thread_id = t.id
LEFT JOIN users u ON u.id = p.user_id
WHERE t.forum_id = 3
ORDER BY t.id, p.updated_at DESC
) sub
ORDER BY updated_at DESC;
如果出于某些未知原因而正在寻找无子查询的查询，
If you are looking for a query without subquery for some unknown reason, this should work, too:
SELECT DISTINCT
t.id
, first_value(u.username) OVER w AS username
, first_value(p.updated_at) OVER w AS updated_at
, t.title
FROM forum_threads t
LEFT JOIN forum_posts p ON p.thread_id = t.id
LEFT JOIN users u ON u.id = p.user_id
WHERE t.forum_id = 3
WINDOW w AS (PARTITION BY t.id ORDER BY p.updated_at DESC)
ORDER BY updated_at DESC;
这里发生了很多事情：
-
根据
JOIN和WHERE 子句。
运行窗口函数 first_value（）的两个实例（在相同的窗口定义）以从每个线程的最新帖子中检索用户名和 updated_at 。这导致与线程中的帖子一样多的相同行。
The two instances of the window function first_value() are run (on the same window definition) to retrieve username and updated_at from the latest post per thread. This results in as many identical rows as there are posts in the thread.
DISTINCT 步骤在窗口功能之后执行 并将每个集合简化为一个实例。
The DISTINCT step is executed after the window functions and reduces each set to a single instance.
ORDER BY 最后应用， updated_at 引用 OUT 列（ SELECT 列表），而不是两个 IN 列之一（ FROM 列表）。
ORDER BY is applied last and updated_at references the OUT column (SELECT list), not one of the two IN columns (FROM list) of the same name.
还有另一个变体，具有窗口功能 row_number（） 的子查询：
Yet another variant, a subquery with the window function row_number():
SELECT id, username, updated_at, title
FROM (
SELECT t.id
, u.username
, p.updated_at
, t.title
, row_number() OVER (PARTITION BY t.id
ORDER BY p.updated_at DESC) AS rn
FROM forum_threads t
LEFT JOIN forum_posts p ON p.thread_id = t.id
LEFT JOIN users u ON u.id = p.user_id
WHERE t.forum_id = 3
) sub
WHERE rn = 1
ORDER BY updated_at DESC;
类似情况：
- Return records distinct on one column but order by another column
您必须测试哪个更快。取决于几种情况。
You'll have to test which is faster. Depends on a couple of circumstances.
这篇关于选择所有线程并按最新线程排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
