Contest participant get a Badge

Product Support

AI-generated summary

A contest participant is to receive a badge for their involvement. Imperator has found a way to modify existing code to grant the badge when a participant creates a post on a specific topic in a certain category. The code works by liking all participant posts, but Imperator has encountered an issue where disliked posts remain in the query. zogstrip suggested checking the PostAction to see if it was deleted, and later zogstrip provided a solution by adding a check for pa.deleted_at IS NULL. Imperator shared a GIF demonstrating the issue, and Snazzamazza expressed interest in the code modification.

1

Hi,

I make a Contest and will that the participant gain a Badge for participation.
The participant must create a post on a certain topic in a certain categorie.

Also i search here for the something and i found this…

I only must change a little bit the code…

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
JOIN post_actions pa ON 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.user_id = ( SELECT id FROM users WHERE username ilike 'Username' ) 
       )
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'Casual Contest'
) GROUP BY p.user_id

with that i have only to Like all post of the participant.
And it Work!

BUT…

for testing that that work i have like a post and it work. But if i Disslike that post the post stays in that query.

Or have a better Idea for that?
Every month is a new Contest and every contest the participant have to post to a other certain topic.

regards,
Imperator

2

Very interesting, nice job on changing the code. I am a coder, but I am not that advanced yet!

3

@sam

When i Like a post and unlike it. Its still as Like at the database?

4

Why don’t you check it? Like a post, check the latest PostAction. Unlike the post, check the latest PostAction.

5

I already Check this.

I have like a post. The Query Found a Post…
Then i Unlike that and it still found on that query.

but why or how i make that is not still found if i unlike that post.

6

Were they the exact same record? Are you sure there weren’t any differences?

7

I think i am sure…

Here is a GIF

And… by the frist user i like him again. After that i cant unlike… again maybe a bug?
That is the reason why i like the post of a other user.

stillLike.gif804×523 1.71 MB

8

You have to check that the PostAction (the :heart:) hasn’t been deleted by checking that “pa.deleted_at IS NULL”.