Recreate the Discourse user’s directory

Comunità Dati e reportistica
,
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 o @jomaxro - c’è un modo per aggiungere il “nome” a questa query invece di solo lo “username”?

Questa è per la Query Directory Utenti:

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

Aggiungi:
users.name AS "Nome", dopo SELECT users.username AS "Username",

4

Molte grazie @SidV!

Volevo anche aggiungere l’ID utente (per poter ordinare in base alla data di iscrizione alla community ツ

Ecco come appare ora la mia query ed è ESATTAMENTE quello che volevo! (Nota: ho aggiunto i diversi parametri “period” per ricordarmi cosa sono senza dover tornare al post di @jomaxro.

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

-- [params]
-- null int :period

-- 1: tutti
-- 2: annuale
-- 3: mensile
-- 4: settimanale
-- 5: giornaliero
-- 6: trimestrale

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

Grazie ancora, a tutti voi!!!