Badges for X Number of Solutions Created?

Community Building Data & reporting
,

AI-generated summary

The discussion revolves around creating badges for a certain number of solutions created in Discourse. Kyle_Pollock asks for a template or standard query to award badges based on the number of solutions marked. JammyDodger provides two SQL queries that can be used to create such badges, explaining that solutions are tracked in the post_custom_fields table under the is_accepted_answer field. The second query, which grants a badge for 10 solutions, can be adapted by adjusting the count threshold (e.g., 19, 29, 39) to create badges for higher solution counts. JammyDodger also recommends using the Data Explorer to test queries before implementing them as custom SQL badges.

1

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.

hanks,

2

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
)
3

Thanks @JammyDodger,

I may need a bit of more assistance. Are there some docs you have that identifies some the selectors/fields for a solution?

Screen Shot 2022-06-06 at 11.05.29 AM

4

No worries. :slightly_smiling_face: 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.