Badge not being applied, sql error


(lostdesign) #1

edit: i now checked with “preview granted badges”, it now shows the following:
screenshot

I don’t see it in my profile yet, when will it be added? What is the best trigger time for the badge?

Hi everyone,
i just installed solved and added the following sql. After solving one thread, the badge was not applied.
We tried running the sql query inside the data explorer. Here is the error i got in the data explorer:

ActiveRecord::PreparedStatementInvalid: missing value for :backfill in /*
 * DataExplorer Query
 * Query: /admin/plugins/explorer?id=3
 * Started by: lost
 */

It’s a ubuntu 18.04 w/ docker, fresh install from two days ago. I was using the following sql query i got form Discourse Solved (Accepted answer plugin)

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)

(lostdesign) #2

Seems like we just got confused that “data explorer” cannot run it like that and it just took quite long to actually hand out the badge.

I may ask here again:
What is the best trigger time for this?