What cool badge queries have you come up with?

badge

(PJH) #22
SELECT count(*), r.username Liked, r.id user_id, current_timestamp granted_at, p.topic_id
FROM post_actions pa
INNER JOIN posts p on p.id=pa.post_id
INNER JOIN users r on r.id=p.user_id
WHERE pa.post_action_type_id=2 
GROUP BY Liked, r.id, p.topic_id
HAVING count(*) > 30
ORDER BY count(*) DESC

10 seconds. Certainly no more frequently than once a day.


(PJH) #23

Depends on what about those posts you’re after. Basically a post with an attachment is generally one with a picture in, so something like:

SELECT user_id
FROM posts
WHERE raw ilike '%<img src="%"'

would probably be a good start.


(rddddddd) #24

Im planning to open a forum where there could be some parallel trade and promotion of it in other platforms.
Are there any kinf of “trust” badges? Like pro trader, or reliable vendor…
If there are not, maybe i could open a category for trading profiles, where anyone would have a selfthread where people could leave feedback, and give likes to the OP presentation post, and give badges if likes for that post > x.


(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.