Hi everyone, I have some badge SQL which issues a badge depending on the number of new topics created in a specific category which also and containing a specific phrase (a specific url) in the first post of the topic. It works, but not quite as intended: SELECT badge_posts.user_id, min(badge_posts.created_at) granted_at, MIN(badge_posts.id) AS post_id FROM badge_posts JOIN topics ON topics.id = badge_posts.topic_id WHERE category_id = 17 AND badge_posts.post_number = 1 AND badge_posts.raw LIKE '%dronescene.co.uk%' GROUP BY badge_posts.user_id HAVING COUNT(*) > 49 ORDER BY post_id DESC I have four badges available, bronze, silver, gold, platinum and I use things like HAVING count(*) > 1 for the bronze and HAVING count(*) > 10 for the silver, etc. The badges all get issued automatically, no problems there. The problem I do seem to be having is that all the badges are issued for the first topic ID in that category, instead of the last topic id. For example, I have a member with three badges and all three badges have been issued against the same first topic they made that match my WHERE clause, instead of for the very latest topic id that matches the WHERE clause. Can someone let me know what I’m doing wrong with my ORDER BY’s ? :thinking:

I’m still quite new to SQL, but is it the MIN? Won’t that return the first rather than the last?

If I swap the MIN with MAX I then get the most recent topic id (thanks @JammyDodger ) but now having looked at some preview changes I think my logic here is fundamentally flawed anyway :grimacing: Using MAX then sees all the other badges in this group be awarded for the most recent topic id instead. It’s the same issue, just in reverse. D’oh :man_facepalming: My logic was simply: The problem thereby being if someone earns the Bronze in January, the Silver in March and the Gold in June, all three badges are awarded for their first topic id back in January :thinking: Anyone have any ideas? :man_shrugging:

I think that’s because you’re running it against people that already qualify for all four. If it was introduced ‘fresh’ I think it would automatically grant at the latest topic date as soon as a user cleared a threshold. For a ‘legacy’ run, you may need to add a LIMIT? Edit: Returning the first (eg) 25 eligible records per user in one query is beyond my current skills. :slightly_smiling_face: However, depending on how many users qualify for multiple badges you could award the legacy ones manually using info from a data-explorer query? And then running the auto-badge query above should grant the right badge for the right post after those have been awarded. Something like: -- [params] -- int :user_id = 1 -- int :limit = 50 SELECT bp.user_id, bp.created_at, bp.id as post_id FROM badge_posts bp JOIN topics t ON t.id = bp.topic_id WHERE bp.user_id = :user_id AND t.category_id = 17 AND bp.post_number = 1 AND bp.raw LIKE '%dronescene.co.uk%' ORDER BY bp.created_at ASC LIMIT :limit I apologise for any typos. :slightly_smiling_face: (The Bronze should be do-able with the MIN version, and the Platinum with the MAX, so it’s just the 10 and 25 ones)

Need some help with a bit of Badge SQL please

Product Support

pfaffman (Jay Pfaffman) December 17, 2021, 12:47pm 3

My guess is to SORT DESC the topics and then maybe you’ll get the last one.

Topic		Replies	Views
“First post” badge! Data & reporting sql-triggered-badge	11	490	August 22, 2020
Some common badge queries Data & reporting sql-triggered-badge	0	9149	August 7, 2015
Badge query for each time posting on a new topic in a category? Data & reporting sql-triggered-badge	9	635	April 21, 2024
Make a badge for the answer with the most likes in a topic! Data & reporting sql-triggered-badge	4	3455	March 29, 2016
A badge granted when someone posts a reply in a single specified topic Data & reporting sql-triggered-badge	7	534	June 19, 2018

Need some help with a bit of Badge SQL please

Related topics