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