Grant Badge every time someone gives out 5 likes


(cpradio) #1

Okay, so I’m trying to get our community to start using the like feature, and what better way than to gamify it?

So the idea is, every time they hand out 5 likes, they get a badge. Below is the query I believe does this, but I’d like to improve on it (and I need help).

SELECT pa.user_id, 0 post_id, current_timestamp granted_at
      FROM post_actions pa
      WHERE post_action_type_id = 2 AND
        (:backfill OR pa.post_id IN (:post_ids) )
      GROUP BY pa.user_id
      HAVING COUNT(pa.post_id) % 5 = 0

As you can tell, it doesn’t associate to a post, so that’s problem number 1 (not sure if that can actually be resolved though).

Problem number 2 is I don’t know if I did this correctly. On my dev environment, I sort of have a mixed bag. I got the badge once, but when I liked 10 posts. I partially think that is because of how I initially setup the badge when I created like #5 and thus the first 5 were a “lesson learned”.

I originally had the badge setup to run daily, and then I switched it to when a user acts on a post (after I made my 5th like). Then I revoked a like and re-liked the same post to try and invoke the badge, and that didn’t work, so I liked 5 more posts and that gave me the badge immediately.

So now for the goodies: (here is how I have my badge setup in my dev environment)

Two additional questions (based on the UI):
What does “Run revocation query daily” mean? If I unlike a post and drop down to 9 likes, will it remove the 2 badge I received?

What does “Query target posts” mean? It seems if you can associate a post with the badge, it will go out and query that post? But what does it return?

Lastly, will the creation of this badge go out and grant people badges for the likes they’ve already given? As I don’t want that to happen. I simply want it based on likes given after the badge is created.


(Kane York) #2

Hmm, I don’t think this query will work like you want it to.

That means that it runs the query with :backfill and removes any badges not in the output.

It means that the badge system expects a post_id column in the result, which it will use to assosciate the badge with a post.


(Kane York) #3

Here’s one that definitely works, but it’s a awarded-once badge after your first 10 likes:

WITH user_like_count AS (
    SELECT pa.user_id, count(pa.user_id)
    FROM post_actions pa
    WHERE post_action_type_id = 2
    -- Limit to the users who just casted likes
    AND (:backfill OR (pa.user_id IN (
      SELECT user_id FROM post_actions pa
      WHERE pa.post_id IN (:post_ids)
    )))
    GROUP BY pa.user_id
    HAVING count(pa.user_id) > 10
)
SELECT user_like_count.user_id, CURRENT_TIMESTAMP granted_at
FROM user_like_count
;

(Dean Taylor) #4

@riking Any chance you can screenshot the badge editing page for this one so I can understand exactly what settings work with this query?


(Kane York) #5

Good news @cpradio, I think I got something that works.

WITH RECURSIVE Counter AS (
  SELECT 5 i 
  UNION ALL
  SELECT i + 5
  FROM Counter
  WHERE i < 10000
),
user_like_count AS (
    SELECT pa.user_id, count(pa.user_id) count
    FROM post_actions pa
    WHERE post_action_type_id = 2
    -- Limit to the users who just casted likes
    AND (:backfill OR (pa.user_id IN (
      SELECT user_id FROM post_actions pa
      WHERE pa.post_id IN (:post_ids)
    )))
    GROUP BY pa.user_id
    HAVING count(pa.user_id) > 4
)
SELECT user_like_count.user_id, current_timestamp granted_at, Counter.i post_id
FROM user_like_count
JOIN Counter ON user_like_count.count > Counter.i
-- ORDER BY Counter DESC, user_id ASC
;

@DeanMarkTaylor I’m not using the badge page to write these, I’m using 0xDBE from JetBrains.

This query should trigger on “user acts on a post”, should target posts, should be multiple grant, should not show granting post, no need for revocation query.

You can see that the user_id repeats when i decreases.


(cpradio) #6

Okay, so I’ve altered it to award itself every 5 likes

WITH user_like_count AS (
    SELECT pa.user_id, count(pa.user_id)
    FROM post_actions pa
    WHERE post_action_type_id = 2
    AND TRUE -- (:backfill OR pa.post_id in :post_ids)
    GROUP BY pa.user_id
    HAVING count(pa.user_id) > 1 AND count(pa.user_id) % 5 = 0
)
SELECT user_like_count.user_id,  CURRENT_TIMESTAMP granted_at
FROM user_like_count

But for my test user, it only shows one badge, even though I set it for multiple, and on the badge admin area, it shows it was granted to 2 users, and when I run the Preview Query, it says it will grant it to testuser1 for a second time (but never does)

Edit: @riking, just saw your post, currently looking it over.


(Kane York) #7

Hmm, nope, just checked - it’s not filtering out correctly. Let me take another look.

Edit 1: My fans are complaining.

Edit 2: Figured it out: user_like_count.count > Counter.i, not less than.

Edit 3: Replaced screenshot. Can anyone guess who user ID 32 is? And 1995?


(cpradio) #8

Okay, so it “sort of” works. It only works once you reach 15 likes because of the > 10 on the CTE (at least that is what we generally call WITH statements in SQL Server).


(Kane York) #9

Just edited the query, try it again :smile:


(cpradio) #10

First off, thank you SO much for your help! I really appreciate this.

With that said, I’m getting the following error:

PG::SyntaxError: ERROR:  syntax error at or near "ON"
LINE 22: CROSS JOIN Counter ON user_like_count.count > Counter.i) q W...
                            ^
: SELECT COUNT(*) count FROM (WITH RECURSIVE Counter AS (
  SELECT 10 i 
  UNION ALL
  SELECT i + 5
  FROM Counter
  WHERE i < 10000
),
user_like_count AS (
    SELECT pa.user_id, count(pa.user_id) count
    FROM post_actions pa
    WHERE post_action_type_id = 2
    -- Limit to the users who just casted likes
    AND ('t' OR (pa.user_id IN (
      SELECT user_id FROM post_actions pa
      WHERE pa.post_id IN (NULL)
    )))
    GROUP BY pa.user_id
    HAVING count(pa.user_id) > 4
)
SELECT user_like_count.user_id, current_timestamp granted_at --, user_like_count.count, Counter.i
FROM user_like_count
CROSS JOIN Counter ON user_like_count.count > Counter.i) q WHERE 't' = 't'

(Kane York) #11

Serves me right for editing the two separately. Remove the CROSS so it’s just JOIN ON.

Here’s the current ‘debug’ version, which runs, but has the backfill/trigger condition commented, and returns extra columns:

WITH RECURSIVE Counter AS (
  SELECT 5 i
  UNION ALL
  SELECT i + 5
  FROM Counter
  WHERE i < 5000
),
user_like_count AS (
    SELECT pa.user_id, count(pa.user_id) count
    FROM post_actions pa
    WHERE post_action_type_id = 2
    -- Limit to the users who just casted likes
    AND TRUE -- (:backfill OR (pa.user_id IN (
      -- SELECT user_id FROM post_actions pa
      -- WHERE pa.post_id IN (:post_ids)
    -- )))
    GROUP BY pa.user_id
    HAVING count(pa.user_id) > 4
)
SELECT user_like_count.user_id, current_timestamp - (Counter.i * INTERVAL '0.0001 seconds') granted_at, user_like_count.count, Counter.i
FROM user_like_count
JOIN Counter ON user_like_count.count > Counter.i
ORDER BY Counter.i ASC, user_id ASC
;

(cpradio) #12

Weird, it still only gave it out once there was 15 likes from a new user… Maybe I should delete the badge and set it up again…


(cpradio) #13

Ah, I see the update where it was changed from SELECT 10 i to SELECT 5 i, that’s exactly what I just did. :smile:

Okay, last issue, it doesn’t actually seem to give a second badge. I see that it should grant the second badge, but it doesn’t actually do it… (from what I can tell)


(cpradio) #14

Here is what shows when I run Preview Query

And the query, in case I entered it wrong…

WITH RECURSIVE Counter AS (
  SELECT 5 i 
  UNION ALL
  SELECT i + 5
  FROM Counter
  WHERE i < 10000
),
user_like_count AS (
    SELECT pa.user_id, count(pa.user_id) count
    FROM post_actions pa
    WHERE post_action_type_id = 2
    -- Limit to the users who just casted likes
    AND (:backfill OR (pa.user_id IN (
      SELECT user_id FROM post_actions pa
      WHERE pa.post_id IN (:post_ids)
    )))
    GROUP BY pa.user_id
    HAVING count(pa.user_id) > 4
)
SELECT user_like_count.user_id, current_timestamp granted_at --, user_like_count.count, Counter.i
FROM user_like_count
JOIN Counter ON user_like_count.count > Counter.i

(Kane York) #15

Do you have the checkboxes set up like this?


(cpradio) #16

Yep.


(Kane York) #17

What happens if you do this:

SELECT user_like_count.user_id, current_timestamp - (Counter.i * INTERVAL '0.0001 seconds') granted_at

I think it’s a unique filter on the user ID, granted_at, and post_id.


(Kane York) #18

Okay, there’s a problem. The badge granter doesn’t like me returning multiple rows, even with slightly different timestamps.


(Kane York) #19

Figured out a solution - Use Counter.i as the post_id, but leave “Show post granting badge on the badges page” unchecked.

Updated the query, of course


(cpradio) #20

K. I’ll do more testing tomorrow but that seems to match my suspicions which is good to know.