已固定主题数量的徽章 / 徽章 SQL？

支持
1

我们的社区以置顶和全局置顶的主题为核心。不幸的是，用户个人资料页面完全没有相关的统计数据，而且似乎置顶/全局置顶信息在数据库中也未被系统地存储。

鉴于此，我正在寻找一种方法，至少可以为“首次置顶”、“10 次置顶”、“100 次置顶”以及“全局置顶主题”授予徽章。

请问有人能帮我提供实现这些徽章的 SQL 语句吗？

我的起点如下：

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.unpinned IS NOT NULL
GROUP BY p.user_id
HAVING count(*) > 5

但这返回了错误：

ERROR: column t.unpinned does not exist
LINE 6: WHERE t.unpinned IS NOT NULL

我想我只能使用 unpinned 字段，因为据我所知，没有其他数据点能记录主题曾经被置顶过。或者，我是否可以使用 post_actions？

或者，是否可以通过搜索主题中由工作人员/系统用户发布的、包含“pinned … ago”的第一条帖子来统计？action_code => pinned.disabled

image
image388×150 1.9 KB

更新：这似乎能运行了……

SELECT t.user_id, current_timestamp granted_at
FROM badge_posts p
INNER JOIN topics t on t.id = p.topic_id
WHERE p.action_code ilike 'pinned.disabled'
GROUP BY t.user_id
HAVING count(*) > 0

但我不确定这是否是最佳方案。由于一个帖子可能被多次置顶和取消置顶……我不确定这个查询是否每个 topic.id 只会捕获到一个 pinned.disabled 记录。

如果有徽章 SQL 高手能提供帮助，将不胜感激。

另外，是否可以考虑在数据库中像存储“点赞”一样系统地存储置顶信息，并在用户个人资料的“统计”部分及下方显示，并附带“已置顶”链接？因为主题被置顶所代表的认可度远高于点赞，理应被纳入统计。

2 个赞
2

更新：

我现在自己解决了这个问题。以下是最终的徽章查询语句：

WITH unpinned AS ( /* 获取至少有一个 pinned.disabled 的符合条件的主题帖子 */
	SELECT t.user_id, t.id
	FROM badge_posts p
	INNER JOIN topics t on t.id = p.topic_id
	WHERE p.action_code ilike 'pinned.disabled'
	GROUP BY t.id, t.user_id
	HAVING count(*) > 0
)
SELECT user_id, count(*), current_timestamp granted_at 
FROM unpinned
GROUP BY user_id
HAVING count(*) > 0

最后一个 count 必须修改为曾经被置顶过的主题数量，例如：>0 表示至少有一个曾经被置顶的主题，>10 表示至少十个曾经被置顶的主题。

我不得不采用 pinned.disabled 这一变通方案，因为 Discourse 数据库中没有任何其他关于置顶历史的数据点。此外，由于缺乏可用数据，该查询也无法区分常规置顶和全局置顶。

依我之见，今后应当对此进行调整，因为在全局置顶 > 常规置顶 > 评论 > 点赞 > 浏览量的参与度统计中，这些指标目前完全没有被纳入考量。

3 个赞