Experiment: Time-corrected Mediocre Posts
Hey @PJH can we try out this alternate query for Mediocre Post for a few days?
The goal is to have the badge notifications for old posts actually show up.
WITH TenthLikes AS ( SELECT * FROM ( SELECT post_id, created_at, ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY created_at DESC) row_num FROM post_actions pa WHERE pa.post_action_type_id = 2 AND (:backfill OR pa.post_id IN (:post_ids)) ) tmp WHERE row_num = 10 ) SELECT p.user_id, p.id post_id, TenthLikes.created_at granted_at -- Here's the beef! FROM badge_posts p LEFT OUTER JOIN TenthLikes ON p.id = TenthLikes.post_id WHERE p.post_number > 1 AND p.like_count >= 10 AND (:backfill OR p.id IN (:post_ids)) ;
.Actually trigger daily until we see if it's slow
That would then make it different from the Good Post badge. The notification problem was my not selecting the right trigger:
Nah, this is something that Discourse does where if you get a bronze badge that wasn't
granted_atthe last week or something, it doesn't notify you.
But the timestamp is, from my reading of the queries(
updated_at) , of necessity when the 10th like is given so should be less than a week?
meta_discourse=# SELECT p.id FROM posts p LEFT JOIN post_actions pa ON pa.post_id = p.id WHERE p.updated_at < (pa.created_at + INTERVAL '1 minute') AND p.updated_at > (pa.created_at - INTERVAL '1 minute'); (1650 rows)
Guest last edited by
This topic was automatically closed after 14 days. New replies are no longer allowed.