Badge SQL Error: q.user_id

(Holly Ross) #1


I am working on creating a badge for people that have their post marked as the solution via the solutions checkbox plugin. This is the SQL query:

       u.username AS "username",
       count(*) AS "count_received_best_answer",
       current_timestamp granted_at
       notifications n
       users u ON n.user_id =
       topics t ON n.topic_id =
       categories c ON t.category_id =
       n.notification_type = 14 AND LIKE '%solved.accepted_notification%' AND 
       n.created_at > CURRENT_DATE - INTERVAL '30 days'
GROUP BY u.username
HAVING count(*) > 0
ORDER BY count(*)

However, when I try to preview the badge, I get this error:

From searching here, I gather that q.user_id is a field in the background of these badge jobs, but I can’t figure out why I am getting this error. Any help appreciated!

(Gerhard Schlager) #2

Your query needs to return at least the following columns:

  • user_id
  • granted_at

Also, you should definitely take a look at Add Badge to Solve answer. :wink:

(Holly Ross) #3

YES. That was all I needed. Not sure why staring at it for four hours did not make it obvious. thanks much @gerhard

(Jeremy M (Jerdog)) #4

How does this differ from the Add Badge to Solve answer topic? I am looking to do the same thing but unsure if either one would be applied for every solution or a set number?