Badge ideas for Topic Voting

data & reporting
,
1

Great plugin. Because everyone loves badges, I’ve put together some basic queries for badges based on voting. (As I’m still finding my way around the DB and the way badges work in Discourse, there may be issues with these queries – but I think they’re right!)

Suggester: Granted to users who have made a suggestion (fa-lightbulb-o)
Trigger when a user creates a post

select
  users.id user_id,
  p.created_at granted_at
from
  users
  join badge_posts p on users.id = p.user_id
  join topics t on (p.topic_id = t.id and users.id = t.user_id)
  join categories c on t.category_id = c.id
  join category_custom_fields ccf on c.id = ccf.category_id
where 
  ccf.name = 'enable_topic_voting'
  and ccf.value = 'true'
  and (:backfill or p.id in (:post_ids))
group by
  users.id,
  p.created_at
having
  count(*) >= 1
order by
  p.created_at desc
limit 1

Edison: same as above, but change to count(*) >= 10.

Voter: Granted to users who have cast a vote (fa-check-square)
Trigger when a user acts on a post

select    
    users.id user_id,
    current_date granted_at
from
    users
    join badge_posts p on users.id = p.user_id
    join topics t on (p.topic_id = t.id and users.id != t.user_id)
    join user_actions acts on (users.id = acts.user_id and t.id = acts.target_topic_id)
where
    acts.action_type = (select tp.id from post_action_types tp where tp.name_key = 'vote')
    and (:backfill or p.id in (:post_ids))
group by
    users.id
having
    count(*) >= 1
limit 1

Opinionated (same as above, but change to count(*) >= 10).

Member of the Legislative Assembly (same as above, but change to count(*) >= 200).

I’m sure there are many more badges that could be reasonably built around this plugin. Any other ideas for badges?

7 Likes