数据浏览器查询：查找无回复的帖子

社区 数据与报告
1

主题：How to find topics without a reply from someone other than the topic owner?

我创建了以下查询，但在我的情况下存在问题。

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "t"
LINE 31:    AND t.category_id = ANY ('{48,23}'::int[])

以下是查询代码：

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)

SELECT 
p.created_at,
p.topic_id,
p.id as post_id,
p.like_count,
p.post_number,
p.reply_count
FROM posts p
LEFT JOIN post_search_data psd ON psd.post_id = p.id
RIGHT JOIN query_period qp
    ON p.created_at >= qp.period_start
        AND p.created_at <= qp.period_end
WHERE 
      reply_count = 0
      AND post_number != 0
	  AND t.category_id = ANY ('{48,23}'::int[])

我有两个问题：

  1. 如何定义 Categories 表并解决此错误？
  2. 如何将周期开始时间设置为当前日期的后一天？
1 个赞
2

该错误是因为 posts 表中没有 category_id 列。要正常工作，您需要与 topics 表进行连接。

如下所示：
LEFT JOIN topics t ON t.id = p.topic_id

如果您能详细说明所需的数据，我可以尝试调整查询。
您想要的是属于类别 48 和 23 的所有帖子，而不是主题吗？结果中是否应忽略已删除的帖子和主题？

CURRENT DAY() 返回今天的日期。您想搜索的周期是什么？

3 个赞
3

感谢您的回复！

正确

正确（主题不应包含在查询中）

从（当前时间减去 24 小时）的时间戳开始，为期 1 个月

2 个赞
4

与已删除主题相关的帖子也被忽略了。

查询中考虑的时期：period_start: 2021-01-14 / period_end: 2021-02-14

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        DATE_TRUNC('day', CURRENT_DATE - INTERVAL '1 day') - INTERVAL ':months_ago months' AS period_start,
        (CURRENT_DATE - INTERVAL '1 day')  AS period_end
)

SELECT 
    p.created_at,
    p.topic_id,
    p.id AS post_id,
    p.like_count,
    p.post_number,
    p.reply_count
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.reply_count = 0
    AND p.post_number > 1
    AND t.category_id IN (48, 23)
    AND p.deleted_at IS NULL
    AND t.deleted_at IS NULL
    AND p.created_at >= (SELECT period_start FROM query_period)
    AND p.created_at <= (SELECT period_end FROM query_period)
3 个赞
5

太好了！代码运行正常。

但在测试时，我发现结果中仍然包含了本应排除的用户发布的帖子。在我们的论坛中，审核功能由特定组的成员负责。

我们能否在查询中排除属于某些组的用户发布的帖子？

我检查了主题页面的 .json 文件，发现它以可见形式仅跟踪了以下用户信息：

  • “moderator”（版主）
  • “admin”（管理员）
  • “staff”（工作人员）
  • primary_group_name（主组名称，在我们的论坛中未设置，因为版主可能属于不同的组）
3 个赞