Recreate the Discourse user’s directory

Comunidad Datos e informes
,
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: ¿Existe alguna forma de agregar el “nombre” a esta consulta en lugar de solo el “nombre de usuario”?

Esto es para la consulta del Directorio de Usuarios:

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

-- [parámetros]
-- null int :period

SELECT users.username AS "Nombre de usuario",
directory_items.likes_received AS "Me gusta recibidos",
directory_items.likes_given AS "Me gusta dados",
directory_items.topic_count AS "Temas creados",
directory_items.post_count AS "Respuestas",
directory_items.days_visited AS "Visitas",
directory_items.topics_entered AS "Vistos",
directory_items.posts_read AS "Leídos"
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

Agregar:
users.name AS "Nombre", después de SELECT users.username AS "Nombre de usuario",

4

¡Muchas gracias @SidV!

También quería agregar el ID de los usuarios (para poder ordenar según cuándo el usuario se unió a la comunidad ツ

Así es como se ve mi consulta ahora, y es EXACTAMENTE lo que quería! (nota: agregué los diferentes parámetros “period” para recordarme qué son sin tener que volver al post de @jomaxro.

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

-- [params]
-- null int :period

-- 1: todo
-- 2: anual
-- 3: mensual
-- 4: semanal
-- 5: diario
-- 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

¡Gracias de nuevo, todos!!!