User badge counts with badge name filter

[alefattorini (Alessio Fattorini)]

@SidV

Test this:

-- [params]
-- int :posts = 100
-- int :top = 10
SELECT u.username, count(ub.id) as "Badges"
FROM user_badges ub, users u, user_stats us
WHERE u.id = ub.user_id
AND u.id = us.user_id
AND us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
GROUP BY u.username
ORDER BY count(ub.id) desc
LIMIT :top

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

[alefattorini (Alessio Fattorini)]

Any hint on this? I tried to add a line
AND b.id = 136

But it doesn’t work

[simon]

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

[alefattorini (Alessio Fattorini)]

That’s it thank you so much