One of our users had 5 “Nice Reply” badges (liked 100 posts and gave 100 likes). Then we moved some topics to a private category, and now he has only 3 badges. It seems the badge query is not counting the likes given or received on the moved topics, perhaps because they moved or perhaps because they are now private.
In debugging this, I copied the badge query to Data Explorer:
select p.user_id, p.id post_id, current_timestamp granted_at
from badge_posts p
where p.post_number > 1
and p.like_count >= 10
The above query returns only 3 hits for the user, but it should be 5. I noticed it uses the table “badge_posts”. I can’t find the badge_posts table in the Data Explorer tree view. So I changed “badge_posts” to “posts”, and then it returns 5 as expected.
Where does the “badge_posts” table come from, and shouldn’t the badge query use the “posts” table instead?
Posts in the badge_posts view are visible posts that are not in read restricted categories. When you moved your topics to a private category, you removed their posts from the badge_posts view.
Our private category is not all that private- you just have to accept a policy to gain access. So I would like to include posts from that category. I guess I would need to create a custom badge that looks similar to Nice Reply (and Good Reply and Great Reply), but use posts instead of badge_posts.