Badge query working in explorer but not in badge SQL?

Data & reporting
1

I have this query below, which works in the data explorer, but I get the error Sorry, an error has occurred. when trying to use the query to assign badges. Is there something I’m missing here? The query should match if someone is a member of a group called “chapter_” or “meta_chapter_” and assigned as their primary group.

SELECT distinct on (u.id)
u.id
FROM users u
INNER JOIN groups g
ON u.primary_group_id = g.id
WHERE g.name LIKE 'chapter_%'
OR g.name LIKE 'meta_chapter_%'
1 Like
2

For the Custom Triggered ones you need to add an extra line at the start and end. There’s an example in a post of mine above, and you can find more info in Create Triggered Custom Badge Queries :+1:

3 Likes
3

Thanks! I think I have to have a look after a good night sleep. SQL has been a while and I guess I have to check some more examples and docs. The thing below is not working, that’s for sure

SELECT ub.id, ub.granted_at, g.name
from user_badges ub
INNER JOIN groups g
ON ub.id = g.id
WHERE g.name LIKE 'chapter_%'
OR g.name LIKE 'meta_chapter_%'
AND (:backfill OR ub.id IN (:user_ids))

It triggers this error, maybe I’m passing throught the wrong info or naming/column.

ERROR:  column q.user_id does not exist
LINE 11:  JOIN users u on u.id = q.user_id

Edit: ah yes, user_id should be used from somewhere, not id.

And as a final result, these badges are assigned when in a group that starts with the name chapter_ or meta_chapter in them.

SELECT distinct on (gu.user_id)
gu.user_id, gu.updated_at as granted_at
FROM group_users gu
INNER JOIN groups g
ON gu.group_id = g.id
WHERE g.name LIKE 'chapter_%'
OR g.name LIKE 'meta_chapter_%'
AND (:backfill OR gu.id IN (:user_ids))

And this one for if you’re in a group that ends with -team in the name.

SELECT distinct on (gu.user_id)
gu.user_id, gu.updated_at as granted_at
FROM group_users gu
INNER JOIN groups g
ON gu.group_id = g.id
WHERE g.name LIKE '%-team'
AND (:backfill OR gu.id IN (:user_ids))
1 Like