Custom Solved badges are not being assigned to users


(Ben Leong) #1

Hi! We use the Solved plugin extensively for our customer support topics, and I’m trying to introduce a series of badges to encourage members to use it more often.

TL;DR version
Solved Post badges definitely appear on our badges page, and the queries return a list of users who should qualify for these. No badges are actually being assigned though.

Longer version:
We’re using a series of ranked badges, where members should only have the badge matching their current level (e.g. just L3, not L1, L2 and L3). To do this, I’ve tried specifying the exact range for number of solved posts, and selected the “run revocation query daily” option.

I’ve based all badge queries on the examples on those @sam provided this thread: Discourse Solved (Accepted answer plugin)

Examples:

1 solved post (I’m not sure if p.post_number > 1 should use = instead of > here)

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
                     value IS NOT NULL 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)

2-10 solved posts

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 
                    value IS NOT NULL 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(*) BETWEEN 2 AND 10
)

And so on, for 11-25 solutions, 26-50, etc.

I’ve used the following settings for the new badges:
21%20pm

And the preview shows users who the badge should be assigned to:

The badges definitely appear on our badges page. However, a week later there still hasn’t been a single badge assigned (either from new Solved Posts, or backfilling older ones) - we’ve continued marking new posts as solutions since we enabled these badges.

Am I missing something obvious, or should this be working?

@neil has had a look at these, and noted that while the performance is pretty terrible, those do eventually return results and users who should get the badges.


(Ben Leong) #2

:wave: Still hoping someone can help with this.

TL;DR version

  • Solved Post badges definitely appear on our badges page
  • Badge queries return a list of users who should qualify for these.
  • No badges are actually being assigned though.

If I’m asking in the wrong spot, please let me know where I should be raising this.