Any reason the badge query preview would read:
91 badges to be assigned.
But the badge never gets assigned. How can this be?
Backlog badge assignments run daily
If you don’t want to wait, visit /sidekiq/scheduler and click Trigger on Jobs::BadgeGrant.
4 Likes
@sam even though backlog badge assignments run daily, shouldn’t the badge still be assigned at some point? The query proved to work in postgres (identifying user’s who should be receiving the badge) and the preview query has listed the number of badges to be assigned weeks ago yet 0 have been given.
This is the query (let me know if the backfill might be screwing me up):
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 settings are set as below:
@fefrei thanks for this. I did just use /sidekiq/scheduler for the first time because your suggestion. Really great tool, however, my badge is still not being assigned. It is so odd because I would think if the preview query lets me know that X amount of people are set to get the badge, and then I run Jobs::BadgeGrant …you would think the badge would definitely be assigned afterwards, but still 0. 
@sam UPDATE: upon looking at /logs I see this error:
Job exception: ERROR: duplicate key value violates unique constraint "index_user_badges_on_badge_id_and_user_id_and_seq" DETAIL: Key (badge_id, user_id, seq)=(118, 139, 0) already exists.
You have a query that is targetting posts there no? If not, you need to be selecting distinct user ids, looks like you are selecting dupes.
Can’t a badge be given more than once though? The query gives a badge for the person with the most likes on a post in a topic (if they are part of a particular group). A person could achieve this on multiple topic threads, thus their user id would appear more than once in the query.
In that case you want to return the post_id cause then you associate the badge with a post.
2 Likes
Hate to be a pain but even by using (:backfill OR ( posts.id IN (:post_ids) )) instead of relating the user_id doesn’t do the trick. I am mostly frustrated because if the preview says its working, why shouldn’t it give those users the badge?
Err, wait a second, what do you think that query is actually doing?
Also, you need to make the (user_id, post_id) pairs unique or you crash the grant.
The :backfill snippet is for triggered badges, this should be a “check once per day” badge.
Please … look at the existing badge queries, you need your select list for the query to match the columns it expects.
This sounds like a riddle. These are the coloumns:
topic | post_id | user_id | title | like_count | granted_at
-------+---------+---------+-------------------+------------+-------------------------------
119 | 161 | 61 | Real Estate Agent | 5 | 2016-01-26 17:22:24.152271+00
277 | 477 | 118 | Real Estate Agent | 5 | 2016-01-26 17:22:24.152271+00
123 | 168 | 68 | Real Estate Agent | 4 | 2016-01-26 17:22:24.152271+00
125 | 156 | 56 | Real Estate Agent | 4 | 2016-01-26 17:22:24.152271+00
133 | 382 | 139 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
122 | 183 | 73 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
238 | 385 | 119 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
115 | 349 | 119 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
209 | 308 | 90 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
300 | 565 | 156 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
132 | 169 | 68 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
120 | 205 | 87 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
142 | 391 | 139 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
249 | 405 | 139 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
115 | 331 | 111 | Real Estate Agent | 3 | 2016-01-26 17:22:24.152271+00
From @sam and others who have supported me on this problem, from what I gather, these are the possible issues:
- The query is pulling multiple user_ids resulting in a duplicate key value violation
- The query, for the aforementioned reason, should target post_id as it would be unique
- As per Sam’s tongue twister, the select list for the query needs to match the column it expects
I have looked at many other badge queries and successfully written a handful as well. I am posting in support because that’s what I need. As you can see from the table, those are the columns I expect. I want to see the user with the most likes on a post in a topic. 
Good news, everyone. 
I got a query to work. I wrapped my original query in:
SELECT DISTINCT(user_id), CURRENT_TIMESTAMP granted_at
FROM (my existing query here) AS top_answerers
WHERE (:backfill OR ( user_id IN (:user_ids) ))
So in this case, the multiple user_ids was the issue. I guess users won’t be able to get this badge multiple times, but I can live with that for now.
2 Likes
This sounds like a badge that should only be awarded once per person anyways 
@riking I think it could go either way. Having a top answer would certainly be rare enough that having the badge once would feel like an accomplishment. However, in the same regard, it’s rarity would make having more than one top answer an even bigger feat, which is why granting the badge multiple times would be nice.
My personal opinion is that having one badge is all it takes to accomplish what getting a badge is supposed to do (that special feeling), which is why I’m totally ok with it only being awarded once