Badge query broken, using mysterious "badge_posts" table

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, 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?

1 Like

That table confused me for a long time. It is defined here: discourse/badge_posts_view_manager.rb at master · discourse/discourse · GitHub. It is a postgres view. My understanding of a view is that it is a name that is assigned to a query.

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.