Badges for reading [ ⚠ intensive]

Community Building Data & reporting

AI-generated summary

Badges for Reading

A SQL query is provided to grant badges for reading on a Discourse forum. The query counts the number of posts made by a user and grants a badge when the count reaches a certain threshold (e.g., 3^9). The query is intensive and should not be run more frequently than daily.

alefattorini asked why the pow function is used, and PJH explained that it’s for documentation purposes, as the badges are based on powers of 3. For badges based on powers of 10, a simpler approach can be used.

alefattorini also asked about granting badges for reading whole topics or categories. PJH provided modified queries for these cases, noting that they are expensive and should be run at most once a day.

Later, alefattorini asked about granting badges for reading a specific topic, and PJH pointed to a previous discussion on a similar topic.

1

#badges for reading!

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
WHERE :backfill
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10)  /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC

I highly recommend not running this query more frequently than daily - our forum’s only been running for 10 months and the table referenced here has 11,949,028 rows making this query rather time intensive (6 seconds per badge - most of our badge queries are sub-second.)

And yes - as it stands - PMs, flags and non-public posts are counted in this - trying to exclude those pushes the execution time of the query even higher… (ref 1, ref 2)

2

Why are you using pow? Is it faster than say just 20000 posts?
Can I do the same thing for whole topics?

3

No. It’s to make them self documenting, since those particular badges on our instance are based on 3^3, 3^4, 3^5, 3^6 etc, (i.e. because 9, 27, 81, 243 and higher are less readable when glancing at the query.)

If you’re basing yours on powers of 10, then simply use 10000, 20000, 30000 or whatever.

Yes, but

  1. Do you mean badges for one single topic?
  • All topics within a single category?

If the first, then that’s easy, the topic_id is in post_timings:

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
WHERE :backfill
   AND topic_id=<TOPICNum>
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10)  /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC

But you’d need a separate set of badges for each topic you want to count posts in.

If it’s the second - all topics within a category then you’re going to need to join against the topics table to get the category_id you’re looking for:

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
JOIN topics t on t.id=pt.topic_id
WHERE :backfill
   AND  t.category_id=<CATEGORYNum>
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10)  /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC

Ditto for the above though - you’d need a separate set of badges for each category you want badges for (unless you do AND t.category_id in (<CATNO1>, <CATNO2>, <CATNO3>))

Note, however, that these queries are expensive in the time they take to execute. Run them at most once a day.

4

Amazing! Thanks very much!

5

Sorry, I mean check if someone has read a specific topic (like the welcoming topic). just the first post or whole discussion

6

We’ve already sorta covered that - start reading near this post:

https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/39?u=pjh