Hi guys, I managed to test this query up there and it worked! Now, I was wondering if anyone can help me with another challenge. This is the query:
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
SELECT id FROM categories WHERE name ilike 'Arts'
) AND p.post_number = 1
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id
HAVING SUM (views) >= 100
On this example: The user who creates stories in the Arts category and those stories reach 100 views, will receive the badge. The problem is that:
I will need to change this logic over time, because the number of views will increase.
This badge requires 100 views today, but can require 1000 views 6 months from now.
The problem is that when I change the query, it runs again every day and the users who already received it would lose it.
And this would be a disaster, imagine that you reached (now at the beginning of the platform), 10 views and won a Level 2 Badge in Technology. But some months later the platform grows and now Level 2 requires no more 10 views, but 100. You would lose the badge.
SELECT distinct p.user_id, p.created_at granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND
post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND
pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'team' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike 'bug'
) AND p.post_number = 1
Change team to the group you want to use and change bug to the category you want to use it on.
How to exclude rights to a custom badge if a higher tier has already been granted? The objective is to grant only the highest tier a particular user is eligible forā¦
Letās say I have 3 badges:
Tier 1: 5 Likes Given, 5 Received
Tier 2: 100 Likes Given, 100 Received
Tier 3: 250 Likes Given, 250 Received
Code below is for Tier 2. Other tiers have nothing different apart from the number ā100ā
SELECT us.user_id, current_timestamp AS granted_at
FROM user_stats AS us
WHERE us.likes_received >= 100
AND us.likes_given >= 100
AND (:backfill OR us.user_id IN (:user_ids))
In the /admin/badges/BADGE-ID settings, you simply tick the option for Run revocation query daily. The badge will be removed if they no longer meet the SQL criteria.
You might also want to make them Update Daily too so that the granting / revoking happen at around the same time (midnight local time for your instance I think).
Would this also need an extra line in the badge SQL to balance it out? Something like:
SELECT us.user_id, current_timestamp AS granted_at
FROM user_stats AS us
WHERE us.likes_received >= 100
AND us.likes_given >= 100
AND us.likes_given < 250
AND (:backfill OR us.user_id IN (:user_ids))
(possibly use a BETWEEN or similar, though I havenāt tested that yet )
And then when the query runs it will give the badge for 100 Likes the first time, ignore it for 101 through 249, and then revoke at 250 (where the next badge will pick up from).
Update: Iāve had a little practice play with BETWEEN, and something like this seems to catch all the right people in the sample test:
SELECT us.user_id
FROM user_stats AS us
WHERE us.likes_received BETWEEN 100 AND 249
ORDER BY us.likes_received DESC
So something like this should work if converted into a triggered badge:
SELECT us.user_id, current_timestamp AS granted_at
FROM user_stats AS us
WHERE us.likes_received BETWEEN 100 AND 249
AND us.likes_given BETWEEN 100 AND 249
AND (:backfill OR us.user_id IN (:user_ids))
This is almost perfect. One rare event may occur: User over-delivers in āgivenā while lagging in āreceivedā (or vice-versa), so the badge could be lost before the next is granted (revocation routine).
Looked around and discovered one interesting property: something.badge_id = 123 where āsomethingā is the user-defined variable and ā123ā the ID of another badge. Iāll give a try and supplement the original SQLs of Tier 1 and Tier 2 with disqualifying checks against the higher tier badges.
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_%'
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 Triggered custom badge queries
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))
I tried this for users who have created a new Topic in a specific Category that satisfied some conditions.
SELECT DISTINCT ON (p.user_id) p.user_id, p.id post_id, p.created_at granted_at
FROM posts p
INNER JOIN topics t
ON p.topic_id=t.id
WHERE t.category_id = 99 AND p.post_number=1 AND <conditions here> AND
(:backfill OR p.id IN (:post_ids) )
ok so my forum is interested in custom āBadges of Shameā for users who get flagged the most in a period of X days. what sort of query can i use for this? i donāt think iāve seen a query that accesses user flag counts in a period.
Doesnāt answer your question but what does a badge like that aim to achieve? Surely, if a user is getting many flags it would make more sense to follow the moderation guide to deal with them.
i donāt disagree. but humor is also a big part of our forum. iām not overly disappointed if i donāt find a solution to this (iām sure it can be done though).
It can definitely be done. I donāt know the exact SQL off the top of my head, but if itās stored in the database it can be used to grant a custom badge. A friend actually has custom badges based on the number of topics a user has muted.
we donāt actually have a lot of problem users and our forum is quite tame that way. banning and silencing is not a daily event for mods by any means. we have a decent number of users, but i think itās been a few weeks since someone was disciplined in any way. mostly an older age group. thus flagged posts arenāt exactly common and are generally mild, mostly passive personal attacks or slightly combative.
)
The dual nature does make it a little more prickly. 
Doesnāt answer your question but what does a badge like that aim to achieve? Surely, if a user is getting many flags it would make more sense to follow the