What cool badge queries have you come up with?

howto tips & tricks
#78

Amazing! Thanks very much!

1 Like
#79

Sorry, I mean check if someone has read a specific topic (like the welcoming topic). just the first post or whole discussion

#80

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

1 Like
#81

Cool man, really! Now I have all what I need

1 Like
#82

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>
6 Likes
#83

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.

1 Like
#84

I’m looking for something similar, a badge awarded when replying to posts in a particular category.

#85 
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
1 Like
#86

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.

#87

Try:

instead?

ObOT: I note that <ins> has been CSS’d on here, but not <del>…

#88

It’s just terrible contrast vs the quote.

1 Like
#89

Hey guys, there are any chance to have a badge for a suspended user? (and keep that badge after the suspended period expired)

Thanks!

#90

“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

3 Likes
#91

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

#92

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…

5 Likes
#93

@iamntz

So you would have no problem if you were awarded a “made an unpopular suggestion” badge here at meta?

2 Likes
#94

I vote it’s called ‘king of bad ideas’, can be awarded more than once, and I get at least two.

1 Like
#95

@Mittineague: As long is a very, very limited badge? YES!

@pjh: That is enough, Thanks!

#97

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.

5 Likes
#98

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 :smile:)

1 Like