How can I filter a specific badge? I need to count how many times they achieved it
Es. “howto writer badge” I need to know who wrote more howtos and create a ranking
1 Like
Any hint on this? I tried to add a line
AND b.id = 136
But it doesn’t work
1 Like
Here’s a slightly modified version of that query that adds a badge name
filter. The badge name
defaults to 'all badges'
. When set to that value, results for all badges will be returned. If you set the badge name
to the name of a specific badge, only results for that badge will be returned.
-- [params]
-- int :posts = 1
-- int :top = 10
-- string :badge_name = all badges
SELECT
username,
COUNT(ub.id) as badge_count
FROM user_badges ub
JOIN users u ON u.id = ub.user_id
JOIN user_stats us
ON us.user_id = ub.user_id
JOIN badges b ON b.id = ub.badge_id
WHERE us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
AND CASE
WHEN 'all badges' = :badge_name
THEN true
ELSE b.name = :badge_name
END
GROUP BY u.username
ORDER BY badge_count DESC
4 Likes
That’s it thank you so much
1 Like