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.
) hasn’t been deleted by checking that “