Recreate the Discourse user’s directory

社区 数据与报告
,
1

Recreate the Discourse user’s directory

OK, here is the query to recreate the user’s directory.

-- [params]
-- null int :period

SELECT users.username AS "Username",
directory_items.likes_received AS "Likes Received",
directory_items.likes_given AS "Likes Given",
directory_items.topic_count AS "Topics Created",
directory_items.post_count AS "Replied",
directory_items.days_visited AS "Vists",
directory_items.topics_entered AS "Viewed",
directory_items.posts_read AS "Read"
FROM users
JOIN  directory_items ON users.id =  directory_items.user_id
WHERE directory_items.period_type = :period
ORDER BY directory_items.likes_received DESC

Once the query is saved you can enter the period into the field below the query to determine what data you get. The periods are as follows:

1: all
2: yearly
3: monthly
4: weekly
5: daily
6: quarterly

You can also change the sort by adjusting directory_items.likes_received on the last line.

2

@riking 或 @jomaxro - 有没有办法在这个查询中添加“姓名”，而不仅仅是“用户名”？

这是用于 用户目录 的查询：

-- https://meta.discourse.org/t/43516/48?u=sidv

-- [params]
-- null int :period

SELECT users.username AS "用户名",
directory_items.likes_received AS "收到的点赞",
directory_items.likes_given AS "给出的点赞",
directory_items.topic_count AS "创建的议题",
directory_items.post_count AS "回复",
directory_items.days_visited AS "访问天数",
directory_items.topics_entered AS "查看的议题",
directory_items.posts_read AS "阅读的帖子"
FROM users
JOIN  directory_items ON users.id =  directory_items.user_id
WHERE directory_items.period_type = :period
ORDER BY directory_items.likes_received DESC
3

添加：
在 SELECT users.username AS "Username", 之后添加 users.name AS "Name",

4

非常感谢 @SidV！

我还想添加用户 ID（以便能够按用户加入社区的时间进行排序 ツ

这是我现在的查询语句，完全符合我的预期！（注意，我添加了不同的"period"参数，以便提醒自己它们的含义，而无需再回到 @jomaxro 的帖子中查看。）

-- https://meta.discourse.org/t/43516/48?u=sidv

-- [params]
-- null int :period

-- 1: 全部
-- 2: 年度
-- 3: 月度
-- 4: 周度
-- 5: 每日
-- 6: 季度

SELECT users.id AS "User ID",
users.username AS "Username",
users.name AS "Name",
directory_items.likes_received AS "Likes Received",
directory_items.likes_given AS "Likes Given",
directory_items.topic_count AS "Topics Created",
directory_items.post_count AS "Replied",
directory_items.days_visited AS "Vists",
directory_items.topics_entered AS "Viewed",
directory_items.posts_read AS "Read"
FROM users
JOIN  directory_items ON users.id =  directory_items.user_id
WHERE directory_items.period_type = :period
ORDER BY directory_items.likes_received DESC

再次感谢大家！！！