Enabling Badge SQL entails security and performance risks so it is not available by default.
For more information, see: Enable Badge SQL
There are lots of potential badges that we can come up with using SQL queries. I thought I’d start a topic for people to share some of the queries and badges they’ve created. I for one am looking forward to seeing some cool inspiration!
This post is a wiki topic. If you have a good badge query to add, please reply to this topic with your query, and then edit this post (pencil icon in the upper right) and include a link to your reply here in the following index. Thanks!
Since there deliberately isn’t the concept of achievements for posting, I’ve managed to implement it on ours, since we had it on our old forums and some were complain about the lack thereof.
Generic version:
SELECT user_id, 0 post_id, current_timestamp granted_at
FROM badge_posts
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id
HAVING count(*) > 1000
Change the 1000 for whatever post limit you’re after. I’ve themed ours (due to the nature of our board) on powers of 2.
Note that this will (should) only count posts that are public (not in categories that are restricted or private messages) or otherwise unrestricted (checkbox on edit category ignoring badges)
Additionally, for our board I’ve had to exclude certain topics due to the nature of my charges:
SELECT user_id, 0 post_id, current_timestamp granted_at
FROM badge_posts
WHERE topic_id NOT IN (
[elided]
) AND topic_id NOT IN (
[elided]
) AND (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id
HAVING count(*) >= 2048
I’ve changed the query to be less DB intensive (only needs to run once per day), time insensitive (doesn’t matter when it’s run) and (unfortunately) much longer. Here if anyone’s interested.
Anyone want a (hopefully easy) challenge? A badge granted when someone posts a reply in a single specified topic. (i.e., A “Please introduce yourself here” topic.)
SELECT
DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 32 AND -- 32 is the "please introduce yourself" topic
(:backfill OR p.id IN (:post_ids) )
Pick an icon. The font-awesome names can be found here (fa-pencil-square for 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
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.
I run a game development community and showcasing your game is always a great way to harvest Likes. As far as grand total of Likes go, spreading your updates out is probably the way to go, but the Badge system won’t take much notice of you unless you save up for bigger updates (especially as long as the default threshold is 10).
Here’s a guy who’s getting a decent amount of attention for his good work, but no badges.
What about a default badge with a query along these lines:
Likeable Poster:
30 likes accumulated over the span of 15 days
Could be one-time-award to keep things simple. Else one would have to do a reset of the count once it’s awarded (while possibly still counting on the old “score” as well in case there’s a 60-likes badge as well).
Bonus idea:
Topic Participant Extraordinaire
total of 30 likes spread over posts in the same topic
SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
WHERE :backfill
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
I highly recommend not running this query more frequently than daily - our forum’s only been running for 10 months and the table referenced here has 11,949,028 rows making this query rather time intensive (6 seconds per badge - most of our badge queries are sub-second.)
And yes - as it stands - PMs, flags and non-public posts are counted in this - trying to exclude those pushes the execution time of the query even higher… (ref 1, ref 2)
SELECT count(*), r.username Liked, r.id user_id, current_timestamp granted_at
FROM post_actions pa
INNER JOIN posts p on p.id=pa.post_id /* Get post details */
INNER JOIN users r on r.id=p.user_id /* The user who made the post that was liked */
WHERE pa.post_action_type_id=2 /* The user who liked the post */
AND pa.created_at > CURRENT_DATE - INTERVAL '15 DAYS' /* Change to suit */
GROUP BY Liked, r.id
HAVING count(*) > 30 /* Change to suit */
ORDER BY count(*) DESC
I’d recommend running that once a day (certainly not every post edit/creation) - it takes 2 seconds to run on my backup instance, though I’m sure the query could be tweaked to make it quicker.
Enabling Badge SQL entails security and performance risks so it is not available by default.
This post is a wiki topic. If you have a good badge query to add, please reply to this topic with your query, and then edit this post (pencil icon in the upper right) and include a link to your reply here in the following index. Thanks!
