Cooties engaged: Part trois - INB4 badges


  • Discourse touched me in a no-no place

    Continuing the discussion from Cooties engaged: Part Deux:

    @abarker said:

    @fbmac said:
    @abarker could setup a script to keep closing and opening the topic, and give some gamification badges for people that can get their posts between the closed and opened messages. That should work.

    Actually, the INB4 badges require that the topic be flagged with a special GUID by an authorized badge grantor who also happens to be an admin. Also, they cannot be granted in One Post.

    And they only get awarded for the first closure. I think. Might be the last (while withdrawing previous ones.)

    WITH auto_closed_topics AS ( /* this clause will change */
            SELECT user_id, id, topic_id, post_number
            FROM posts
            WHERE raw LIKE '%UUID%' AND
            user_id IN  (
                    SELECT gu.user_id
                    FROM group_users gu
                    WHERE group_id IN(
                            SELECT g.id
                            FROM groups g
                            WHERE g.name IN ('badge_granters_admin')
                    )
            )
    ),
    closing_post AS (
        SELECT MAX(post_number), id post_id, topic_id, created_at
            FROM posts p
            WHERE topic_id IN (
                SELECT topic_id FROM auto_closed_topics
            ) AND
            post_type=3 AND
            raw ILIKE 'This topic was automatically closed after %'
            GROUP BY id, topic_id, created_at
    ),
    nate_posts AS (
        SELECT p.user_id, p.post_number, p.id post_id, p.topic_id, p.created_at, p.raw, row_number() OVER (PARTITION BY p.topic_id ORDER BY p.created_at DESC) as rank
        FROM posts p
        JOIN closing_post cp ON p.topic_id=cp.topic_id
            AND p.created_at > cp.created_at - INTERVAL '1 minute'
            AND p.id != cp.post_id
    )
    SELECT user_id, post_id, created_at granted_at  FROM nate_posts WHERE rank=2
    

    That last rank=2 determines the badge, 1 for gold, 2 silver, 3 bronze.

    Either way it would certainly only be one set of badges per topic.

    In fact I've not tested that since they changed the style of the closing posts to see if they've broken things under the hood.


  • Java Dev

    Reading that, I don't see why you couldn't get multiple closing_posts per topic. Where every post less than 1 minute before or any time after the first closing post gets one badge. Every post less than 1 minute before or any time after the second closing post gets two badges. Etc.


  • Discourse touched me in a no-no place

    Because it orders the posts by time, gives them a rank based on row number and only awards a badge to row 1 (or 2, or 3.)

    You can't have more than one row 1 (or 2, or 3.)


  • Discourse touched me in a no-no place

    @PJH said:

    In fact I've not tested that since they changed the style of the closing posts to see if they've broken things under the hood.

    IIRC they did and they needed fixing to regrant the ones granted previously.

    edit: No, that was the renaming of the groups.


  • Java Dev

    Right, missed that bit. A post can still be in nate_posts multiple times, but the ranking happens per topic regardless of which closing post selected the row. So extra posts in such a topic after the close (or a reopen) would cause badges to move. A second close could cause duplicate badges, depending on how do analytic functions in postgres handle duplicates?


Log in to reply