Recreate the Discourse user’s directory

Community Building Data & reporting
,
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 - "username"만 추가하는 대신 이 쿼리에 "name"을 추가하는 방법이 있을까요?

이것은 User Directory 쿼리에 대한 것입니다:

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

-- [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
3

추가:
SELECT users.username AS "Username", 뒤에 users.name AS "Name",를 추가하세요.

4

@SidV님, 정말 감사합니다!

사용자 ID도 추가하고 싶었습니다(커뮤니티에 가입한 시점으로 정렬할 수 있도록 ͟͟͞͞ツ

현재 제 쿼리는 다음과 같은 형태이며, 제가 원했던 내용과 정확히 일치합니다! (참고로, @jomaxro님의 게시물을 다시 찾아볼 필요 없이 각 “period” 파라미터가 무엇을 의미하는지 기억하기 위해 다른 “period” 파라미터들을 추가했습니다.

-- 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

다시 한번 감사합니다, 여러분!!!