Contest participant get a Badge

Support
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

1 Like
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”.

2 Likes