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?