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.
Try:
instead?
ObOT: I note that <ins>
has been CSS’d on here, but not <del>
…
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)
Thanks!
“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.
Hello!
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 )