Is there a way to grant a badge to someone who created a topic which then got many replies? Say, 100 replies?
I’d call it “Popular Topic” and it would encourage people to think of new topic ideas that will generate lots of discussion.
Is there a way to grant a badge to someone who created a topic which then got many replies? Say, 100 replies?
I’d call it “Popular Topic” and it would encourage people to think of new topic ideas that will generate lots of discussion.
I think a simple version would be something like this:
SELECT
t.user_id,
CURRENT_TIMESTAMP AS granted_at,
p.id AS post_id
FROM topics t
JOIN posts p ON p.topic_id = t.id AND p.post_number = 1
WHERE t.posts_count >= 100
AND t.archetype = 'regular'
AND t.deleted_at ISNULL
AND t.user_id > 0
Using the t.post_counts
would currently also include the small action posts (closed, unlisted, etc), so it depends on how truly accurate you want it to be.
You could make it more specfic by doing a count of the ‘eligible’ posts within the query. Something like:
SELECT
t.user_id,
CURRENT_TIMESTAMP AS granted_at,
p.id AS post_id
FROM topics t
JOIN posts p ON p.topic_id = t.id AND p.post_number = 1
WHERE t.id IN (
SELECT
p.topic_id
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_number <> 1
AND p.post_type = 1
AND p.hidden IS FALSE
GROUP BY 1
HAVING COUNT(*) >= 100
)
That is awesome Jam!
Op could also change post counts to have 3 levels bronze(50) Silver(100) gold(200)
Thanks for the how-to and thanks @Shauny for the idea.
Thanks I’ll try it out and get back to you!
I’ve set this up now, but I guess it should be set to update daily? I’ll see if it has worked tomorrow. Does ‘targets posts’ need to be checked?
It worked great, thanks!!
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.