Need some help with a bit of Badge SQL please

Support
1

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:

1 Like
2

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

2 Likes
3

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

2 Likes
4

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:

2 Likes
5

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)

2 Likes