Hey I was wondering if there was any template or standard query for awarding badges for a certain amount of solutions? For example I respond to about 10 posts/ questions and they all get marked as solutions. I get a badge for this and each corresponding badge for 20 30 40 solutions marked, etc.
I tried to look around for the standard queries but didn’t seem to be there. I do see there are some custom queries written here.
There are a couple that come with the plugin which you can enable from the /admin/badges page (HelpDesk and TechSupport). But here are the queries if you want to adapt them:
SELECT p.user_id, p.id post_id, p.updated_at granted_at
FROM badge_posts p
WHERE p.post_number > 1 AND
p.id IN (
SELECT post_id FROM (
SELECT pc.post_id, row_number()
OVER (PARTITION BY p1.user_id ORDER BY pc.created_at) as rnum
FROM post_custom_fields pc
JOIN badge_posts p1 ON p1.id = pc.post_id
JOIN topics t1 ON p1.topic_id = t1.id
WHERE name = 'is_accepted_answer' AND
p1.user_id <> t1.user_id AND
(
:backfill OR
p1.user_id IN (
select user_id from posts where p1.id IN (:post_ids)
)
)
) X WHERE rnum = 1)
SELECT id user_id, current_timestamp granted_at
FROM users
WHERE id IN (
SELECT p1.user_id
FROM post_custom_fields pc
JOIN badge_posts p1 ON p1.id = pc.post_id
JOIN topics t1 ON p1.topic_id = t1.id
WHERE p1.user_id <> t1.user_id AND
name = 'is_accepted_answer' AND
p1.user_id IN (
SELECT user_id
FROM posts
WHERE :backfill OR p1.id IN (:post_ids)
)
GROUP BY p1.user_id
HAVING COUNT(*) > 9
)
No worries. The data explorer is really good for trying out queries before you add them as custom SQL badges (though the data explorer ones don’t need the backfill part).
Though I think the second query above has all the info in you need. The solutions are kept in the post_cutom_field table as is_accepted_answer in the name column. And that second query grants the TechSupport badge for 10 solutions (HAVING COUNT(*) > 9), so for the ones you want from the OP you just need to copy that query and change the bottom number to 19, 29, 39, and so on.