Hide hidden threads from homepage cache

[LegendBegins]

Function findCategoriesWithForums in server/db/index.ts retrieves the latest post in a given subforum for homepage activity previews, even if that post was on a thread that was hidden.

-> e.g. I create thread titled "Starting the red giant of all flame wars, gif is pronounced jif"

--> A mod deletes my thread

-> My thread and the latest post still show up on the homepage

DB should really only fetch the latest thread where is_hidden is false. This requires some SQL modification that I'm too sleepy to validate today.

[danneu]

too tired to do anything more than post the function we use to load the homepage cache.

export const findCategoriesWithForums = async function () {
  const categories = await pool
    .query(
      `
SELECT
  c.*,
  array_agg(
    json_build_object(
      'id', f.id,
      'title', f.title,
      'pos', f.pos,
      'posts_count', f.posts_count,
      'topics_count', f.topics_count,
      'description', f.description,
      'category_id', f.category_id,
      'parent_forum_id', f.parent_forum_id,
      'latest_user', (
        SELECT
          CASE
            WHEN p.user_id IS NOT NULL
            THEN
              json_build_object(
                'uname', u.uname,
                'slug', u.slug,
                'avatar_url', u.avatar_url
              )
          END
        FROM users u
        WHERE u.id = p.user_id
      ),
      'latest_topic', (
        SELECT
          CASE
            WHEN p.topic_id IS NOT NULL
            THEN json_build_object('id', t.id, 'title', t.title)
          END
        FROM topics t
        WHERE t.id = p.topic_id
      ),
      'latest_post', (
        SELECT
          CASE
            WHEN p.id IS NOT NULL
            THEN
              json_build_object(
                'id', p.id,
                'created_at', p.created_at
              )
          END
      )
    )
  ) "forums"
FROM categories c
JOIN forums f ON c.id = f.category_id
LEFT OUTER JOIN posts p ON f.latest_post_id = p.id
LEFT OUTER JOIN topics t ON p.topic_id = t.id
WHERE f.is_hidden = false AND c.pos >= 0 AND (p.id IS NULL OR t.is_hidden = false)
GROUP BY c.id
ORDER BY c.pos
  `,
    )
    .then((res) => res.rows);

  categories.forEach((c) => {
    c.forums = _.sortBy(c.forums, "pos");
  });

  return categories;
};

[danneu]

WHERE f.is_hidden = false AND c.pos >= 0 AND (p.id IS NULL OR t.is_hidden = false)

hmm, so it seems we do filter out hidden topics (t.is_hidden = false)?

Edit: Oh, I think it's a bug and not behaving how it seems: the filter is on the where clause for categories. no wonder i never caught this.

we could use a CTE like

WITH latest_visible_posts AS (
  SELECT DISTINCT ON (p.forum_id)
    p.forum_id,
    p.id as post_id,
    p.user_id,
    p.topic_id,
    p.created_at
  FROM posts p
  JOIN topics t ON p.topic_id = t.id
  WHERE t.is_hidden = false
  ORDER BY p.forum_id, p.created_at DESC
)
SELECT
  c.*,
  -- ...

[danneu]

weird, the code i posted at first isn't on master. i must have let claude vibe code a fix and closed the terminal tab. but it left that non-fix in the code that has the dumb behavior of filtering out categories, not latest post candidates per forum.

anyways, i CTE is the solution