Can someone please help me with the SQL and everything if I wanted to do one for “First post” badge!
Thank you!
1 Like
You mean the first public post that they ever make to your forum?
- /admin/badges
- + New
- Give your badge a name
- Pick an icon. The font-awesome names can be found here (
fa-pencil-squarefor example) or use a hosted image. This will be used on the various badges pages dotted around your site. - Pick an (optional) image. You can use the same as the previous step. This image will appear on user cards.
- Select a badge type. For this you’d probably want Bronze.
- Pick a group (or create a new group for it first.)
- Give your badge a description
- Enter the following sql
SELECT MIN(bp.id) post_id, bp.user_id, MIN(bp.created_at) granted_at
FROM badge_posts bp
WHERE (:backfill OR bp.id IN (:post_ids) )
GROUP BY bp.user_id
- You probably want the following options checked:
- Save your badge and refresh the page to get more options between the SQL and the options shown above and you probably want the following
15 Likes
Wah this is perfect! Thanks PJH! Are there any resources I could look at to create more badges?
1 Like
Not that I’ve found - everything I’ve done has been through trial and error, what passes for SQL that I’ve picked up over the years and mucking around with an offline restored-backup of the database in phppgadmin. And a bit of guesswork.
As a result I’ve created quite a few badges with fairly hairy queries on our site -
- “Made X posts. But not in certain topics”
- “Spent X consecutive days on the forum”
- “Top X% poster in the last month. Except certain topics”
- The above one to allow easier awarding of ‘manual’ badges.
Currently contemplating “Has had a post replied to X times” but that one’s proving problematic depending on how you query the database.
4 Likes
How can you have obtained this?
3 Likes
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
16 Likes
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!
4 Likes
That’s great! Last question, how do you calculate the TOP poster of the month?
The only difference (everything else above these is exactly the same) between the four queries is the last clause:
25%er:
FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.10 AND
row_number < TotalUsers.max *.25
10%er:
FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.05 AND
row_number < TotalUsers.max *.10
5%er:
FROM LastMonth, TotalUsers
WHERE row_number < TotalUsers.max *.05 AND
row_number != 1
Top poster:
FROM LastMonth, TotalUsers
WHERE row_number = 1
5 Likes
Hi ! Sorry if my question is silly but just to verify, is this how to highlight the top poster of the month? I really need that functionality on our site to reward the top poster with a sub to our channel… Thank you!!
SELECT user_id, granted_at
FROM user_badges
WHERE badge_id = 3FROM LastMonth, TotalUsers
WHERE row_number = 1
-e, n00b
Don’t remember who share SQL for “top poster”, sorry. This is the code shared somewhere here on Meta (I saved it in a topic on my forum)
Take a look here:
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 */Saw your code, may I ask if do you want a ‘top poster’ badge only for TL3?
Sorry for the basic question but I’m not a big SQL guy …
When I implement this:
1 top poster
0 top 5% posters
2 top 10% posters
5 top 25% posters
I guess my question is, how can I not have any top 5% posters?
Thanks
1 Like