As already mentioned here: Grant a badge to everyone having posted at least posts in the category that have received at least likes by users in the group . 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 ' ' ) 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 ' ' ) ) ) >= AND p.post_number = 1 AND p.user_id >= 0 GROUP BY p.user_id HAVING count(*) >=

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 ' ' ) ) ) >= AND p.user_id >= 0 GROUP BY p.user_id HAVING count(*) >=

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?

hmm i used lower case staff and it worked for me. :thinking: 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 :face_with_spiral_eyes: 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. :slight_smile:

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 = ' '; 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 yes I named GP4bot Lola

I call mine Bert. :slight_smile: 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.

yea I agree. something still doesn’t feel right. I’m going to bed. :sweat_smile:

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. :facepalm:

Badge for posts with Likes from a specific group

Community Building Data & reporting

Topic		Replies	Views
What is the bug reporter badge? Data & reporting sql-triggered-badge	33	10038	September 17, 2023
Badges for posting Data & reporting sql-triggered-badge	4	592	August 27, 2024
Badges for specific category Feature	1	1328	June 13, 2018
Some common badge queries Data & reporting sql-triggered-badge	0	9148	August 7, 2015
Query to create some groups based on activity Data & reporting sql-query	11	1095	September 5, 2023

Badge for posts with Likes from a specific group

Related topics