Recreate the Discourse user’s directory

Communauté Données et rapports
,
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 ou @jomaxro - y a-t-il un moyen d’ajouter le « nom » à cette requête au lieu de simplement le « nom d’utilisateur » ?

Ceci concerne la requête Annuaire des utilisateurs :

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

-- [params]
-- null int :période

SELECT users.username AS "Nom d'utilisateur",
directory_items.likes_received AS "J'aimes reçus",
directory_items.likes_given AS "J'aimes donnés",
directory_items.topic_count AS "Sujets créés",
directory_items.post_count AS "Réponses",
directory_items.days_visited AS "Visites",
directory_items.topics_entered AS "Vus",
directory_items.posts_read AS "Lu"
FROM users
JOIN  directory_items ON users.id =  directory_items.user_id
WHERE directory_items.period_type = :période
ORDER BY directory_items.likes_received DESC
3

Ajoutez :
users.name AS "Nom", après SELECT users.username AS "Nom d'utilisateur",

4

Un grand merci à @SidV !

J’ai également voulu ajouter l’ID des utilisateurs (pour pouvoir trier par date d’adhésion à la communauté ツ

Voici à quoi ressemble ma requête maintenant, et c’est EXACTEMENT ce que je voulais ! (Note : j’ai ajouté les différents paramètres “period” pour me rappeler ce qu’ils sont sans avoir à revenir au post de @jomaxro.

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

-- [params]
-- null int :period

-- 1 : tout
-- 2 : annuel
-- 3 : mensuel
-- 4 : hebdomadaire
-- 5 : quotidien
-- 6 : trimestriel

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

Encore merci à tous !!!