Abzeichen für die Anzahl der angehefteten Themen / Badge SQL?

Unterstützung
1

Unsere Community konzentriert sich auf angepinnte und global angepinnte Themen. Leider gibt es auf den Benutzerprofilseiten überhaupt keine Statistik dazu, und es scheint, dass Pinnungen bzw. globale Pinnungen nirgendwo systematisch in der Datenbank gespeichert werden.

Angesichts dessen suche ich nach einer Möglichkeit, mindestens folgende Abzeichen zu vergeben: „Erstes angepinntes Thema“, „10 angepinnte Themen“, „100 angepinnte Themen“ sowie für global angepinnte Themen.

Könnte mir jemand bitte mit dem SQL-Code für diese Art von Abzeichen helfen?

Mein Ausgangspunkt:

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.unpinned IS NOT NULL
GROUP BY p.user_id
HAVING count(*) > 5

Dabei erhalte ich jedoch den Fehler:

ERROR: column t.unpinned does not exist
LINE 6: WHERE t.unpinned IS NOT NULL

Ich vermute, ich muss mich auf unpinned stützen, da es meines Wissens nach keinen anderen Datenpunkt gibt, der mit Themen verknüpft ist, die einmal angepinnt waren. Oder könnte ich stattdessen eventuell post_actions verwenden?

Oder sollte ich vielleicht nach dem ersten Beitrag in einem Thema suchen, der von einem Staff-/System-Benutzer erstellt wurde und „pinned … ago“ enthält, um dies zu zählen? action_code => pinned.disabled

image
image388×150 1.9 KB

Update: Dies scheint irgendwie zu funktionieren…

SELECT t.user_id, current_timestamp granted_at
FROM badge_posts p
INNER JOIN topics t on t.id = p.topic_id
WHERE p.action_code ilike 'pinned.disabled'
GROUP BY t.user_id
HAVING count(*) > 0

Ich bin mir jedoch nicht sicher, ob dies der beste Ansatz ist. Da ein Beitrag mehrmals angepinnt und wieder entspint werden kann, bin ich unsicher, ob diese Abfrage wirklich nur EIN pinned.disabled pro topic.id erfasst.

Hilfe von einem SQL-Zauberer für Abzeichen wäre sehr willkommen.

Alternativ könnte man auch darüber nachdenken, Pinnungen zukünftig systematisch in der Datenbank zu speichern – ähnlich wie bei „Likes“ – und sie im Statistik-Bereich des Benutzerprofils anzuzeigen, ergänzt durch Links wie „ANGEPINNT“. Denn wenn ein Thema angepinnt wird, ist dies eine deutlich höhere Wertschätzung im Vergleich zu Likes und sollte entsprechend berücksichtigt werden.

2

Update:

Ich habe das jetzt selbst gelöst. Hier ist die finale Badge-Abfrage:

WITH unpinned AS ( /* Abfrage von eligible Topics mit mindestens einem "pinned.disabled" */
	SELECT t.user_id, t.id
	FROM badge_posts p
	INNER JOIN topics t on t.id = p.topic_id
	WHERE p.action_code ilike 'pinned.disabled'
	GROUP BY t.id, t.user_id
	HAVING count(*) > 0
)
SELECT user_id, count(*), current_timestamp granted_at 
FROM unpinned
GROUP BY user_id
HAVING count(*) > 0

Die letzte Bedingung muss auf die Anzahl der Topics geändert werden, die jemals gepinnt waren. Z. B. bedeutet >0 mindestens ein gepinntes Topic, >10 mindestens 10 Topics, die einmal gepinnt waren.

Ich musste auf die “pinned.disabled”-Umgebungslösung zurückgreifen, da Discourse keine anderen Datenpunkte zur Pin-Historie in der Datenbank vorhält. Zudem ist es aufgrund fehlender Daten nicht möglich, diese Abfrage zwischen regulären Pins und globalen Pins zu unterscheiden.

Meiner Meinung nach sollte dies zukünftig geändert werden, da globale Pins > reguläre Pins > Kommentare > Likes > Aufrufe für die Engagement-Statistiken relevant sind, aber derzeit überhaupt nicht berücksichtigt werden.