Recreate the Discourse user’s directory

Comunidade Dados e relatórios
,
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.

10 curtidas
2

@riking ou @jomaxro — há uma maneira de adicionar o “nome” a esta consulta em vez de apenas o “nome de usuário”?

Isso é para a Consulta Diretório de Usuários:

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

Adicione:
users.name AS "Nome", após SELECT users.username AS "Nome de Usuário",

3 curtidas
4

Muito obrigado, @SidV!

Também queria adicionar o ID do usuário (para poder ordenar por quando o usuário se juntou à comunidade ツ

Aqui está como minha consulta ficou agora, e é EXATAMENTE o que eu queria! (Nota: adicionei os diferentes parâmetros “period” para me lembrar do que são, sem precisar voltar ao post do @jomaxro.

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

-- [params]
-- null int :period

-- 1: todos
-- 2: anual
-- 3: mensal
-- 4: semanal
-- 5: diário
-- 6: trimestral

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

Obrigado novamente, pessoal!!!

3 curtidas