Amazing! Thanks very much!
Sorry, I mean check if someone has read a specific topic (like the welcoming topic). just the first post or whole discussion
[quote=“alefattorini, post:79, topic:18978”]
Sorry, I mean check if someone has read a specific topic (like the welcoming topic). just the first post [/quote]
We’ve already sorta covered that - start reading near this post:
Cool man, really! Now I have all what I need
As already mentioned here:
Grant a badge to everyone having posted at least
<POST_COUNT> posts in the category
<CATEGORY_NAME> that have received at least
<LIKE_COUNT> likes by users in the group
<TEAM_NAME>. Similar to the bug reporter badge here, but can require more than one like.
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id FROM badge_posts p JOIN topics t ON t.id = p.topic_id WHERE category_id = ( SELECT id FROM categories WHERE name ilike '<CATEGORY_NAME>' ) AND ( SELECT count(*) FROM post_actions pa WHERE pa.post_id = p.id AND post_action_type_id = ( SELECT id FROM post_action_types WHERE name_key = 'like' ) AND pa.user_id IN ( SELECT gu.user_id FROM group_users gu WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<TEAM_NAME>' ) ) ) >= <LIKE_COUNT> AND p.post_number = 1 AND p.user_id >= 0 GROUP BY p.user_id HAVING count(*) >= <POST_COUNT>
This badge detects whether a user has enabled mailing list mode:
SELECT u.id user_id, current_timestamp granted_at FROM users u WHERE mailing_list_mode = true AND (:backfill OR u.id IN (:user_ids))
It should trigger when a user is created or edited.
I’m looking for something similar, a badge awarded when replying to posts in a particular category.
SELECT p.user_id user_id, min(p.id) post_id, min(p.created_at) granted_at FROM badge_posts p JOIN topics t ON p.topic_id = t.id WHERE t.category_id = (SELECT id FROM categories WHERE slug = 'introductions') GROUP BY p.user_id
Crikey, if this is what I think it is, how easy is it to adapt to specify the number of posts for silver/gold badges?
SELECT p.user_id user_id, min(p.id) post_id, min(p.created_at) granted_at FROM badge_posts p JOIN topics t ON p.topic_id = t.id WHERE t.category_id = (SELECT id FROM categories WHERE slug = 'introductions') GROUP BY p.user_id HAVING COUNT (*) > 10
Would it be that simple?
Turns out it is. Awesome, thanks @riking!
Update: throws an error on any category name which isn’t unique.
ObOT: I note that
<ins> has been CSS’d on here, but not
It’s just terrible contrast vs the quote.
Hey guys, there are any chance to have a badge for a suspended user? (and keep that badge after the suspended period expired)
“Mark of Cain” ?
I would strongly recommend against such a badge and in fact not work on writing it for you, but it is very doable.
Badges are meant to be a positive thing, if you start using them for negative stuff you send some really weird messages out there
That’s true. The thing is that we have a community where, in more than a year we have exactly two suspended users. So they are in very exclusive club, and when we joked about this, this idea came up: why we don’t give them a badge?
If it’s that rare, and if you really want to do it, simply assign them manually at /admin/users/USERNAME/badges.
I’ll attest to the fact that ‘negative’ badges aren’t really a good idea on most forums, having created a few myself and seen the fallout of one of them…
So you would have no problem if you were awarded a “made an unpopular suggestion” badge here at meta?
I vote it’s called ‘king of bad ideas’, can be awarded more than once, and I get at least two.
If you want your query to not be dependent on system field labels such as
user_field_2, you can do the following:
SELECT cf.user_id user_id, cf.updated_at granted_at FROM user_custom_fields cf INNER JOIN user_fields uf ON uf.id = (0 || regexp_replace(cf.name, 'user_field_', ''))::integer AND uf.name = 'FriendlyFieldName' WHERE LENGTH(cf.value) > 0
It seems like you’d want to check for a length value greater than zero, just in case you have some single-digit UID GitHub graybeards. And if you are granting a badge multiple times, you’ll want to add
GROUP BY cf.user_id to be safe.
We have a dev, a staging, and a live site, and the custom user fields we manually add don’t always have the same user_field_n values from site to site.
I want to create a badge for those who help in the forums, so I want to give it to the people who have replied X times to ANY thread inside the category ‘Help’. Of course this has to avoid replies in your own topic.
Please any help would be great! I have no idea how to deal with this kind of databases (I’m new to discourse )