Grant a badge to everyone having posted at least <POST_COUNT> posts in the category <CATEGORY_NAME> that have received at least <LIKE_COUNT> likes by users in the group <TEAM_NAME>. Similar to the bug reporter badge here, but can require more than one like.
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 '<CATEGORY_NAME>'
) AND (
SELECT count(*)
FROM post_actions pa
WHERE 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_NAME>' )
)
) >= <LIKE_COUNT>
AND p.post_number = 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST_COUNT>
I was fiddling with this, and almost had it working, but I couldn’t get it to apply to all categories the way I wanted it too. Is there a simple way to do that?
Hi @Firepup650 maybe try this one. it worked on my instance.
<CATEGORY NAME> = Case sensitive category name (not slug)
<GROUP> = Group Name (ie: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = minimum # of likes you want to set
<POST COUNT THRESHOLD> = minimum # of posts
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 t.category_id = (
SELECT id FROM categories WHERE name ilike '<CATEGORY NAME>'
) AND (
SELECT count(*)
FROM post_actions pa
WHERE 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 '<GROUP NAME>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
for multiple categories you can do this:
<CATEGORY NAMES> = Case sensitive category names
<GROUP> = Group Name (ie: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = minimum # of likes you want to set
<POST COUNT THRESHOLD> = minimum # of posts
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 t.category_id IN (
SELECT id FROM categories WHERE name ILIKE ANY (ARRAY['<CATEGORY NAME 1>', '<CATEGORY NAME 2>', '<CATEGORY NAME 3>'])
) AND (
SELECT count(*)
FROM post_actions pa
WHERE 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 '<GROUP>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
Heya @Lilly!
Those both look like great queries, but I wanted to just run the query against all categories if possible. When I tried, j kept getting errors about a subquery returning multiple rows, so I came to ask here about it.
Do you mean you want the same query for all categories?
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 (
SELECT count(*)
FROM post_actions pa
WHERE 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 '<GROUP>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
That seems like it would work, but seems to fail when the group you’re running it against is staff. I tried both Staff and staff as the group name, and temporarily set post and like count to 1, and it says no badges would be granted. What am I doing wrong here?
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 (
SELECT count(*)
FROM post_actions pa
WHERE 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 'staff' )
)
) >= 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= 1
Weird It still doesn’t work for me. I’ll try running it against a few other groups to see if I can find the issue.
Edit: Ran against a different group, and the query still failed. I’m not sure what the issue here would be. Does it depend on primary groups by chance?
Edit 2: That wouldn’t work, staff doesn’t seem to be allowed to be set as a primary group.
I think I know why. I will work on this for you after I eat some dinner. I need SQL practice anyways. Badge SQL is more restrictive than postgres. I got past the subquery part.
I’ve not woken up quite yet, and I find badge queries need a solid two cups of tea before being able to tackle them fully, but I’ve been talking to the bot recently about these kind of queries, and I think using the actual post_action_code_id and the group_id code are better than using the nested SELECT queries to find the same thing.
I did this to get necessary schema tables for posts, posts_actions, group_users and groups
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';
Then used this to get all the group IDs:
SELECT name, id FROM groups ORDER BY name
So then I included all the schema tables needed and instructed Lola, er GPTbot to use actual post_action_code id and the group_id code. Then after some arguing back and forth and making some corrections. We came up with this. Again, seems to work in Data Explorer, but I still cannot get anything out of it in the Badge Previewer.
G = group_id
X = minimum number of likes
Y = minimum number of posts
SELECT pa.user_id, MIN(pa.post_id) as post_id, COUNT(pa.post_id) as post_count, COUNT(pa.id) as like_count, MAX(pa.created_at) as granted_at
FROM post_actions pa
JOIN group_users gu ON gu.user_id = pa.user_id
WHERE gu.group_id = G AND pa.post_action_type_id = 2
GROUP BY pa.user_id
HAVING COUNT(pa.post_id) >= Y AND COUNT(pa.id) >= X
I call mine Bert. Though we have a complicated relationship.
I think one other limitation to that type of query is using MIN(p.created_at) granted_at gives me the date of their first one and not, say, the date of their 10th one. It could be changed to MAX, but that would also give a ‘not right’ date if you ran it against historical data where they already had more than 10.
I’m still mulling that one over.
I had some success using ROW_NUMBER(), but nothing concrete as yet.
I’m having fun with this though and it’s helping me re-learn SQL and how to write better queries. Having Lola / GPT4bot as a SQL assistant is helpful, but you have to guide her and ask her the right way. I am tried to figure out ways to give her access to most of the schema table information so I don’t have to do it for every query problem we work on. Feeding the table schema info gives much better results. I tried giving her a link to the available schemas in core but that just made her go fart around on google.
I am interested to work with her when I know the badge query previewer is working. I need to practice SQL and doing badge queries. Incidentally she cannot fix it and she still makes my earl tea grey not hot enough. Although last night’s SQL lesson was the best date I’ve had in years.
Using that query, we seem to have had a weird issue. It seems to have awarded to staff only, and I’m almost certain some non-staff would meet that criteria. Is this something I broke somewhere, or is it a query issue?
After a confusing blip with some deleted Likes throwing my tests off, I think this is a revised version of the one in the OP that fits your criteria:
SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p
WHERE (SELECT COUNT(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = 2
AND deleted_at IS NULL
AND pa.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 3)
) >= 5
AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10
It works off the badge_posts view so it only counts posts from public categories, which you may want to take into consideration depending on your forum/category set up. Also, using CURRENT_TIMESTAMP for granted_at is another option, but is probably down to a matter of taste.
maybe try this one. it worked on my instance.
It still doesn’t work for me. I’ll try running it against a few other groups to see if I can find the issue.
