Request: Custom Badge SQL

Hello!

There’s a default badge shipped with the software that I quite like, but would like it tweaked a little.

The original:

This badge is granted when you have 100 liked posts and give 100 or more likes in return. Thanks for paying it forward!

Desired:

This badge is granted when you have received 100 likes and given 100 or more likes in return.

The “give part” shall remain identical. But to completely disregard the post count in forum gamification, I want the users who have received 100 likes on 1 post to qualify for that badge the same as users who have received a single like on 100 different posts.

So far, I have followed this: Badge SQL can no longer be edited by default

  • Enabled SiteSetting.enable_badge_sql = true, pushed the new badge SQL via GUI, and then disabled custom SQL queries for security reasons with SiteSetting.enable_badge_sql = false. The custom badge happens to work just fine but I’m not taking barbaric risks with no database/SQL qualifications.

As of now the forum is small and there are no apparent problems but I might be in for some performance issues as the forum grows. If you got the time, please provide your SQL query for that custom badge.

Thanks and regards!

Hello and welcome @rapphann :slight_smile:

I’m afraid I’m not a SQL master either :slight_smile: But I would think it would be something like this:

SELECT us.user_id, current_timestamp AS granted_at 
FROM user_stats AS us 
WHERE us.likes_received >= 100 
AND us.likes_given >= 100
AND (:backfill OR us.user_id IN (:user_ids))
2 Likes