Badge preview working, badge assignment not working


(Steven Slade) #1

Continuing the discussion from Badges not being assigned:

I originally posted the linked topic under support because I had the following badge query not working:

SELECT topics.id AS topic, posts.id AS post_id, users.id AS user_id, users.title, MAX(posts.like_count) AS like_count, CURRENT_TIMESTAMP granted_at
FROM topics
JOIN posts ON topics.id = posts.topic_id
JOIN users ON users.id = posts.user_id
WHERE posts.like_count > 0
AND users.title = 'Real Estate Agent'
AND (:backfill OR ( users.id IN (:user_ids) ))
GROUP BY topics.id, posts.id, users.id, users.title
HAVING MAX(posts.like_count) >= (SELECT MAX(p.like_count)
	FROM topics t
JOIN posts p ON t.id = p.topic_id
JOIN users u ON u.id = p.user_id
WHERE t.id = topics.id
GROUP BY u.id order by max desc limit 1)
ORDER BY MAX(posts.like_count) DESC

The query has present for weeks and the badge has not yet been assigned. The frustrating aspect of this is that the preview badge query lists X number of users who are set to get the badge. Furthermore, on my own backend using postgres, I can see the query returns the desired results.

In the linked topic, some suggestions were to try relating the post_id rather than the user_id, I tried this to no avail. What are the chances that there is a bug involved? In my mind, if the Discourse is telling me that badges are to be assigned in the preview, then it should work.


(Mittineague) #2

Only a wild guess, does
CURRENT_TIMESTAMP granted_at
need an AS ?

EDIT
Hmmm, Lokking at a few of the default badge queries, none of them had CURRENT_TMESTAMP in them.