Badges not being assigned


(Steven Slade) #1

Any reason the badge query preview would read:

91 badges to be assigned.

But the badge never gets assigned. How can this be?


Badge preview working, badge assignment not working
(Sam Saffron) #2

Backlog badge assignments run daily


(Felix Freiberger) #3

If you don’t want to wait, visit /sidekiq/scheduler and click Trigger on Jobs::BadgeGrant.


(Steven Slade) #4

@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:


(Steven Slade) #5

@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. :persevere:


(Steven Slade) #6

@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.


(Sam Saffron) #7

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.


(Steven Slade) #8

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.


(Sam Saffron) #9

In that case you want to return the post_id cause then you associate the badge with a post.


(Steven Slade) #10

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? :persevere:


(Kane York) #11

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.


(Sam Saffron) #12

Please … look at the existing badge queries, you need your select list for the query to match the columns it expects.


(Steven Slade) #13

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. :yuno:


(Steven Slade) #14

Good news, everyone. :goodnews:

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.


(Kane York) #15

This sounds like a badge that should only be awarded once per person anyways :slightly_smiling:


(Steven Slade) #16

@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 :slightly_smiling: