Amend badge query to include Unlisted topics?

Data & reporting
1

Hi I’m trying to make a Badge Query that gives a badge when someone replies to an UNLISTED post.

Sadly it seems the regular Query just ignores “UNLISTED” posts for some reason and doesn’t give the badges.

Here’s the Query in Question, it’s the same as above:

SELECT
DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 81 AND -- 81 is the topic I want
  (:backfill OR p.id IN (:post_ids) )

And here’s the Error I get:

No Badges to be assigned.

Unique  (cost=25.87..25.88 rows=2 width=16)
  ->  Sort  (cost=25.87..25.87 rows=2 width=16)
        Sort Key: p.user_id
        ->  Hash Join  (cost=6.73..25.86 rows=2 width=16)
              Hash Cond: (t.category_id = c.id)
              ->  Nested Loop  (cost=4.32..23.44 rows=4 width=20)
                    ->  Index Scan using topics_pkey on topics t  (cost=0.14..8.16 rows=1 width=8)
                          Index Cond: (id = 81)
                          Filter: ((deleted_at IS NULL) AND visible)
                    ->  Bitmap Heap Scan on posts p  (cost=4.18..15.24 rows=4 width=20)
                          Recheck Cond: (topic_id = 81)
                          Filter: ((deleted_at IS NULL) AND (post_type = ANY ('{1,2,3}'::integer[])))
                          ->  Bitmap Index Scan on index_posts_on_topic_id_and_sort_order  (cost=0.00..4.17 rows=4 width=0)
                                Index Cond: (topic_id = 81)
              ->  Hash  (cost=2.21..2.21 rows=16 width=4)
                    ->  Seq Scan on categories c  (cost=0.00..2.21 rows=16 width=4)
                          Filter: (allow_badges AND (NOT read_restricted))

How can I modify my query to NOT ignore Unlisted posts?

Thank you very much for your time.

1 Like
2

Try changing

FROM badge_posts p

to

FROM posts p

The badge_posts table doesn’t include unlisted posts, or posts that have been created in private categories.

6 Likes