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
Can someone let me know what I’m doing wrong with my
ORDER BY's ?