Recreate the Discourse user’s directory

Gemeinschaft Daten & Berichterstattung
,
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 oder @jomaxro – gibt es eine Möglichkeit, das „name"-Feld in diese Abfrage aufzunehmen, anstatt nur den „username"?

Dies betrifft die Abfrage für das Benutzerverzeichnis:

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

Füge hinzu:
users.name AS "Name", nach SELECT users.username AS "Benutzername",

4

Vielen Dank, @SidV!

Ich wollte auch noch die Benutzer-ID hinzufügen (damit man nach dem Beitrittsdatum zum Community sortieren kann ツ

So sieht meine Abfrage jetzt aus, und sie ist GENAU das, was ich wollte! (Hinweis: Ich habe die verschiedenen „period"-Parameter hinzugefügt, um mich daran zu erinnern, was sie bedeuten, ohne wieder auf @jomaxros Beitrag zurückkommen zu müssen.)

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

-- [params]
-- null int :period

-- 1: alle
-- 2: jährlich
-- 3: monatlich
-- 4: wöchentlich
-- 5: täglich
-- 6: vierteljährlich

SELECT users.id AS "Benutzer-ID",
users.username AS "Benutzername",
users.name AS "Name",
directory_items.likes_received AS "Erhaltene Likes",
directory_items.likes_given AS "Gegebene Likes",
directory_items.topic_count AS "Erstellte Themen",
directory_items.post_count AS "Antworten",
directory_items.days_visited AS "Besuche",
directory_items.topics_entered AS "Angesehene Themen",
directory_items.posts_read AS "Gelesene Beiträge"
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

Nochmals vielen Dank, ihr alle!!!