What cool badge queries have you come up with?

badge

(PJH) #25

You can create your own and award them manually.

Or you could probably use groups for this (/admin/groups/custom) which - at a guess - would be more useful for what you’re after.

Groups would allow you to create a category where group members could create topics, yet still allow non-group members to reply to existing topics (but not create their own.) Is that the sort of thing you’re thinking of?


(Michael Downey) #26

Has anyone come up with any badges based on some value for custom user fields? If so, how can we get at those custom user fields with a SQL query?

Here’s a sample badge (there is surely better SQL to do this!) that we quickly hacked together to look for people who filled out an optional custom user field, in this example, to list their GitHub User ID:

    SELECT cf.user_id user_id, cf.updated_at granted_at
    FROM user_custom_fields cf
    WHERE cf.name like 'user_field_2' AND
    LENGTH(cf.value) > 1

Note that the value of name field is actually user_field_1, user_field_2, etc., and not the name that you’ve assigned in the admin section.


(Lisa Wess) #28

Awesome badge. Thank you for sharing this!


(Mykhailo Poliarush) #29

Hi everyone,

I don’t know too much about database structure to create badge sql. I would like to create following badges which is not present in discourse (i think not only i interested in such badges). Please help me out to create following badges:

  • user that made more than 100 comments in general
  • user that made more than 100 comments in particular category
  • user that made more than 100 comments with given at least 5 likes
  • user that gave ‘likes’ more than 100 times in general
  • user that received more than 100 ‘likes’ in general
  • user that received more than 100 ‘likes’ in particular category
  • user that created more than 50 topics in general
  • user that created more than 50 topics in particular category
  • badge for 5 most active users in particular category for a month
  • badge for 5 most active users in general for a month
  • badge for 5 most active users for particular trust level
  • user that shared github gist and received like on comment
  • user that wrote some code blocks in comment and received like on comment

I would gladly appreciate any help on any mentioned items. Many thanks,


(Tobias Eigen) #30

I :heartpulse: this - thanks for sharing! Works for me.

One thing I noticed: it’s not immediately apparent on the custom user field which user_field_n goes which which user field and it doesn’t start at 1! I found it by looking at the source of that page.

Do you know how to modify this query to display it only when a user has another badge already? I’d like to display a “Tech Ambassador” badge on my site to people but only to those users who have self-identified themselves as such and have also provided a bio.


(Michael Downey) #31

I think you’d simply add the following:

AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE cf.user_id = ib.user_id
  AND ib.badge_id = 888
)

Where 888 is the badge ID number of the other prerequisite badge. See the following for details:


(PJH) #32

Well the name given should be in a table somewhere, though joining on it to create a dynamically generated field name may not make the query more self documenting (if that’s even possible in postgres)…

And you’ll have to be careful when editing (specifically adding and removing other) fields of course.


(Michael Downey) #33

Agreed. I’m hopeful to find an answer related to that here:


(Alessio Fattorini) #34

How can you have obtained this?


(PJH) #35

Sure I’d already posted this query, but… this is the one for the top 10%-25% (I have comparable queries for 5%-10%(silver), and top 5%(gold) and top poster (again gold but CSS’d to be a brown spoon)).

Here’s a short version that most sites could probably use:

WITH LastMonth AS ( /* Count eligible posts from last month */
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id 
    WHERE bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    HAVING count(*) > 1
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS ( /* Total relevant users that have posted in the last month */
    SELECT max(row_number) from LastMonth
)
SELECT username, user_id, row_number, (row_number*100/TotalUsers.max), count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.10 AND /* 10% - change this*/
    row_number < TotalUsers.max *.25 /* 25% - change this */


Our site has people who like gaming the system so, so our actual query (presented below) has a few extra clauses that

  • Exclude particular topics (over and above those excluded normally from badge_posts) such as topics created solely for the purpose of encouraging excessive posting
  • Excludes particular ‘users’ (actually 'bots - they don’t get this badge)
  • Excludes particularly short topics:
WITH exclusions AS ( /* Which other topics to exclude from counters */
    SELECT user_id, id, topic_id, post_number
    FROM posts
    WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND
    user_id IN  (
        SELECT gu.user_id
        FROM group_users gu
        WHERE group_id IN(
            SELECT g.id
            FROM groups g
            WHERE g.name IN ('admins')
        )
    )
),
LastMonth AS ( /* Count eligible posts from last month */
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id AND
        bp.user_id NOT IN ( /* ignore bots */
                SELECT gu.user_id
                FROM group_users gu
                WHERE group_id IN(
                    SELECT g.id
                    FROM groups g
                    WHERE g.name IN ('bots')
                )
        )
    WHERE topic_id NOT IN ( /* exclude short topics */
       SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10
    ) AND topic_id NOT IN ( /* Ineligible topics */
       SELECT topic_id
       FROM exclusions
    ) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    HAVING count(*) > 1
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS (
    SELECT max(row_number) from LastMonth
)
SELECT username, user_id, row_number, (row_number*100/TotalUsers.max), count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.1 AND
    row_number < TotalUsers.max *.25

Special "Leaderboards" for Discourse Forums?
(John ) #37

We’d like to have a badge granted to our staff members that have read the guidelines placed in our hidden staff category.

This would (presumably) be very similar to the “Read Guidelines” badge:

SELECT user_id, read_faq granted_at
FROM user_stats
WHERE read_faq IS NOT NULL AND (user_id IN (:user_ids) OR :backfill)

With a modification to allow us to specify specific topics or post, and add more in the future.

Could anyone help with that?


Hide private badge page?
(Manny Hernandez) #38

Thanks so much for everyone contributing their knowledge and experience on this thread, so that those of us who are new to Discourse can make the most of this amazing platform!


(PJH) #39

Unlikely because read_faq is ‘special’ and not reprogrammable or extendable. The following may do what you’re after however:

SELECT user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE topic_id = /* put the topic id of your guidelines here */
AND post_number = /* this will probably be 1 since I suspect it's the only thing there */
AND msecs > 10000 /* they've looked at it for more than 10 seconds */

(John ) #40

Thanks very much @PJH!


(PJH) #41

Test it first… I’m not sure how/when the msecs column gets updated.

I’d also recommend running it -at most- daily, with no revocation.


(John ) #42

Works, thanks! Though this probably isn’t the ideal solution, n the interest of giving back to the community, here’s our multipost version.

    SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at    
FROM post_timings
JOIN (
     SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at      
     FROM post_timings
     JOIN (
           SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at
           FROM post_timings
           WHERE topic_id = 206 
           AND post_number = 1
           AND msecs > 10000 
          ) a ON a.user_id = post_timings.user_id
     WHERE topic_id = 1066
     AND post_number = 1
     AND msecs > 10000
    ) b ON b.user_id = post_timings.user_id
WHERE topic_id = 1043
AND post_number = 1
AND msecs > 10000

(PJH) #43

How about:

SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE
( topic_id = 206 AND post_number = 1 AND msecs > 10000) OR
( topic_id = 1066 AND post_number = 1 AND msecs > 10000) OR
( topic_id = 1043 AND post_number = 1 AND msecs > 10000)
GROUP BY user_id
HAVING count(*) = 3

Edit: Changed since it was pointed out to me that the original wouldn’t have worked.


(Kane York) #44

Regular Emeritus

SELECT user_id, granted_at
FROM user_badges
WHERE badge_id = 3

Description: Users with this badge have been a regular user of this forum now or in the past. The badge is awarded when the user first attained Trust Level 3.

Leader Emeritus

SELECT user_id, granted_at
FROM user_badges
WHERE badge_id = 4

Description: Users with this badge have been an exemplary user of this forum now or in the past. The badge is awarded when the user first attained Trust Level 4.


Revocation query: NO (This is the important one :slight_smile: )
Targets posts: NO
Trigger: Daily
As a title: YES
Granted multiple: NO
Show badge: YES
Show post granting badge: NO
Enable: YES


(Jacob Chapel) #45

Couldn’t this be rewritten as:

SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE post_number = 1
AND msecs > 10000
AND ( topic_id = 206 OR topic_id = 1066 OR topic_id = 1043 )
GROUP BY user_id
HAVING count(*) = 3

I’m no SQL expert, just seems like it should be possible given the duplication in the original query.


(Crackmacs) #46

Would there be a way to assign a badge based on platform? Like, if someone were to login to the forum using Android or iOS or Windows and so on