Discourse Post Voting

I think this also. :slight_smile: But Sam is adamant that it is a thing:

It doesn’t look like there are any badges knocking around for this yet.

If you have specific badge criteria for any you’d like to create you should fire up a Data & reporting topic for each one. :+1:

I think a couple of examples could be along the lines of:

get 10 votes on a post voting reply

SELECT p.user_id, p.created_at AS granted_at, p.id AS post_id
FROM post_voting_votes pvv
  JOIN posts p ON p.id = pvv.votable_id
WHERE pvv.direction = 'up'
  AND p.post_number <> 1
  AND (:backfill OR p.id IN (:post_ids))
GROUP BY p.user_id, p.id
HAVING COUNT(*) >=10

Or a slightly more nuanced one where you accounted for ‘up - down’ votes:

WITH vote_totals AS (

SELECT 
    p.user_id,
    p.id AS post_id,
    p.created_at AS granted_at,
    (COUNT(*) FILTER (WHERE pvv.direction = 'up') - COUNT(*) FILTER (WHERE pvv.direction = 'down')) AS total
FROM post_voting_votes pvv
  JOIN posts p ON p.id = pvv.votable_id
WHERE p.post_number <> 1
GROUP BY 1, 2

)

SELECT *
FROM vote_totals 
WHERE total >= 10
  AND (:backfill OR post_id IN (:post_ids))

:red_exclamation_mark: These will need some further testing to make sure they’re working as intended. :slight_smile:

Though one thing to consider is that the total is dynamic so a ‘10’ today could turn into a ‘5’ tomorrow if more downvotes are added (for the second example). And if the badge is just based on upvotes like the first example then you may have got 10, but the UI only shows 5 when the downvotes are totalled in.

4 Likes