What cool badge queries have you come up with?

No. It’s to make them self documenting, since those particular badges on our instance are based on 3^3, 3^4, 3^5, 3^6 etc, (i.e. because 9, 27, 81, 243 and higher are less readable when glancing at the query.)

If you’re basing yours on powers of 10, then simply use 10000, 20000, 30000 or whatever.

Yes, but

  1. Do you mean badges for one single topic?
  • All topics within a single category?

If the first, then that’s easy, the topic_id is in post_timings:

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
WHERE :backfill
   AND topic_id=<TOPICNum>
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10)  /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC

But you’d need a separate set of badges for each topic you want to count posts in.

If it’s the second - all topics within a category then you’re going to need to join against the topics table to get the category_id you’re looking for:

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
JOIN topics t on t.id=pt.topic_id
WHERE :backfill
   AND  t.category_id=<CATEGORYNum>
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10)  /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC

Ditto for the above though - you’d need a separate set of badges for each category you want badges for (unless you do AND t.category_id in (<CATNO1>, <CATNO2>, <CATNO3>))


Note, however, that these queries are expensive in the time they take to execute. Run them at most once a day.

2 Likes

Amazing! Thanks very much!

1 Like

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:

1 Like

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

1 Like

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

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

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
1 Like

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.

1 Like

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

3 Likes

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:

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

@iamntz

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

2 Likes

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

1 Like

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

@pjh: That is enough, Thanks!

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