Badges & Private Communities

Feature
12

I’ve been looking at the existing badge queries to get an idea of which queries target posts in the badge_posts table, or use some other method of excluding posts from protected categories from being used.

The following badges query specific posts and will not be awarded for activity in private categories:

  • Editor
  • First Flag
  • First Like
  • First Link
  • First Quote
  • First Share
  • First Emoji
  • First Mention
  • First Onebox
  • First Reply By Email
  • Reader
  • Wiki Editor
  • Great Share
  • Good Share
  • Helpdesk
  • Nice Share
  • Welcome
  • Famous Link
  • Great Reply
  • Great Topic
  • Good Reply
  • Good Topic
  • Hot Link
  • Nice Reply
  • Nice Topic
  • Popular Link

The following badges do not query specific posts and will be awarded for activity in private categories:

  • Licensed
  • Autobiographer
  • Certified
  • New User of the Month
  • Read Guidelines
  • Admired
  • Champion
  • Crazy in Love
  • Devotee
  • Empathetic
  • Aficionado
  • Anniversary
  • Campaigner
  • Gives Back
  • Higher Love
  • Respected
  • Appreciated
  • Enthusiast
  • Out of Love
  • Promoter
  • Thank You
  • Leader
  • Regular
  • Basic
  • Member
  • Staff
  • Profile Picture

Something similar to this is already covered by the Appreciated (1 like on 20 posts) and Respected (2 likes on 100 posts) badges. Some variations of these queries could be added. For example, 10 likes on 20 posts. A badge that was granted for super-liked topics could also be a good idea - it would function as the equivalent of the Great Topics badge. For example, it could be awarded when a user has created 10 topics that have received 10 likes.

I’m not sure if it would make sense to add a badge that is awarded for activity on a single post or topic that does not link to the post. For example, an alternate First Like badge could be created with the following sql:

SELECT pa1.user_id, pa1.created_at granted_at
FROM (
  SELECT pa.user_id, min(pa.id) id
  FROM post_actions pa
  JOIN posts p on p.id = pa.post_id
  WHERE post_action_type_id = 2
  GROUP BY pa.user_id
) x
JOIN post_actions pa1 on pa1.id = x.id

For the query to work, it needs to use the “Update daily” trigger instead of the “When a user acts on a post” query. On the Badges page, users who have been awarded the badge will be shown along with the time that the badge was granted. There will not be a link to the post that the badge was awarded for:

image
image1048×502 33.2 KB

Does this kind of approach make sense for sites that have mostly protected categories? If it does, it could be used for duplicating some of the queries that are currently targeting the badge_posts table.

3 Likes