Badge for posts with Likes from a specific group

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?

1 Like

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:

3 Likes

4 posts were split to a new topic: Query ‘Preview’ not working in SQL badge page

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.

1 Like

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

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.

2 Likes

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

3 Likes

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:

2 Likes

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?

2 Likes

yea I know something is wrong, I’m going to work on this as soon as my instance gets updated with the previewer fix.

2 Likes

Just to have a reset, as I think the back and forth has confused me. :slight_smile:

Is the aim of this to grant a badge for a certain amount of posts across all categories that have been liked by @staff at least once?

1 Like

I intended it to be granted to users who have X number of posts with Y likes by staff, across all categories. In my case, 10 posts, 5 likes.

3 Likes

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: :slight_smile:

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.

2 Likes

I feel like I was getting close. :upside_down_face: well done. :clap:

3 Likes

I’m trying to figure out if a CTE is more efficient, but it’s melting my brain :melting_face:


WITH staff_liked AS (

       SELECT COUNT(*), pa.post_id
       FROM post_actions pa 
       JOIN group_users gu ON gu.user_id = pa.user_id
       WHERE post_action_type_id = 2
       AND gu.group_id = 3 
       AND deleted_at IS NULL
       GROUP BY pa.post_id
       HAVING COUNT(*) >= 5

)

SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p 
WHERE p.id IN (SELECT post_id FROM staff_liked)
   AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10
2 Likes

3 posts were split to a new topic: What is the 'backfill`?

Hm. That means it won’t count, for example, our TL0 locked category?

1 Like

Yeah, the badge_post view pre-filters some things out to simplify the queries. You can adjust it to use the posts table instead which would include all categories, but it may need an extra line or two to exclude deleted posts, or deleted topics, etc (though this may not be necessary if you want to keep it simple and just let people keep the badge once they’ve earned it, even if their posts are deleted).

1 Like