Badge not working

data & reporting
1

Hi,

I’m not sure if I’m posting in the correct category.

anyway theres a badge for the forum called help desk and its not working, users get 10 accepted answers yet they dont get a badge

here’s the SQL code:

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

I don’t know if the query change in the meanwhile but the original code is this:

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
)

e.g. in your code I don’t see the line “HAVING COUNT” for count the number of accepted answers

EDIT: @zee the code you paste in your post is for 1 accepted answer, not for 10

3 Likes
3

thank you… I shall change it

4

@Trash, in this code, with the last version of discourse I get this error:

Could you check it please?

My mistake, solved here:
https://meta.discourse.org/t/10-accepted-answer-badge-giving-an-error/34560/3?u=sidv

Thanks!

1 Like