Badges for number of pinned topics / Badge SQL?

support
1

Our community is centered around pinned and globally pinned topics. Unfortunately, there is not Stats count for this on the user profile pages at all and it seems pin/global pins are not stored in a systematic way anywhere in the database.

In light of this, I’m looking for some way to grant badges at least e.g. First pinned, 10 pinned, 100 pinned as well as globally pinned topics.

Kindly can anybody help me out with the badge SQL for those kinds of badges?

My starting point:

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

But this gives me the error:

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

I guess I have to go with unpinned as there is no other data point that sticks with topics that once have been pinned as far as I know, or could I potentially use post_actions here?

Or maybe search for the first post in a topic by a staff/system user that contains “pinned … ago” to count on? action_code => pinned.disabled

image

Update: This seems to work somehow…

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

But I’m not sure if this is the best approach, and as a post can be pinned and unpinned multiple times… not sure if this query only catches ONE pinned.disabled per topic.id.

Help by a badge SQL wizard would be appreciated.

Or also the consideration to store pins systematically in the database like “likes” etc going forward, and display them on the user’s profile in the Stats section and below with links “GOT PINNED”. Because if a topic gets pinned, it’s a much higher appreciation as compared to likes and should be accounted for.

2 Likes
2

Update:

Worked this out myself now. Here is the final badge query:

WITH unpinned AS ( /* Get eligible topics posts with at least one 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

The last count must be changed to the number of topics that once have been pinned e.g. >0 = at least one pinned topic, >10 at least 10 once pinned topics.

I had to go with the pinned.disabled workaround as Discourse does not have any other data point about pin histories available in the database. Also it is not possible to distinct this query between regular pins and global pins due to the lack of available data.

This should be changed going forward IMHO, as global pin > regular pin > comments > likes > views for engagement statistics in my opinion, but they are not accounted for at all.

3 Likes