Skip to content

A platform for community discussion. Free, open, simple.

11.9 KB
Raw Blame
# frozen_string_literal: true

module NestedReplies
  class HotScoreCalculator
    class InvalidTree < StandardError
    end

    class MissingOriginalPost < StandardError
    end

    HOT_SCORE_FLOOR = 0.0

    def self.formula_settings
      {
        like_weight: SiteSetting.nested_replies_hot_like_weight.to_f,
        reply_weight: SiteSetting.nested_replies_hot_reply_weight.to_f,
        freshness_max_bonus: SiteSetting.nested_replies_hot_freshness_max_bonus.to_f,
        freshness_half_life_seconds:
          SiteSetting.nested_replies_hot_freshness_half_life_hours.to_f.hours.to_f,
        child_penalty: SiteSetting.nested_replies_hot_child_penalty.to_f,
      }
    end

    def self.public_post_types
      [Post.types[:regular], Post.types[:moderator_action]]
    end

    def self.carrier_post_sql(table_name)
      "#{table_name}.post_type IN (#{public_post_types.join(", ")})"
    end

    def self.public_post_sql(table_name)
      <<~SQL.squish
        #{carrier_post_sql(table_name)}
        AND #{table_name}.deleted_at IS NULL
        AND NOT #{table_name}.hidden
        AND NOT #{table_name}.user_deleted
      SQL
    end

    def self.hot_score_sql(
      table_name,
      reply_count_sql: "0",
      now_sql: "CURRENT_TIMESTAMP",
      formula: formula_settings
    )
      engagement_sql =
        "COALESCE(#{table_name}.like_score, 0) * #{formula.fetch(:like_weight)} + " \
          "COALESCE(#{reply_count_sql}, 0) * #{formula.fetch(:reply_weight)}"
      age_seconds_sql = "GREATEST(EXTRACT(EPOCH FROM #{now_sql} - #{table_name}.created_at), 0)"
      freshness_sql =
        "#{formula.fetch(:freshness_max_bonus)} * " \
          "POWER(0.5, #{age_seconds_sql} / #{formula.fetch(:freshness_half_life_seconds)})"

      <<~SQL.squish
        CASE
          WHEN #{public_post_sql(table_name)}
          THEN LN(1 + GREATEST(#{engagement_sql}, 0)) + #{freshness_sql}
          ELSE #{HOT_SCORE_FLOOR}
        END
      SQL
    end

    def self.score_for(post, direct_reply_count: 0, now: Time.current)
      return HOT_SCORE_FLOOR unless public_post?(post)

      formula = formula_settings
      engagement =
        post.like_score.to_f * formula.fetch(:like_weight) +
          direct_reply_count.to_f * formula.fetch(:reply_weight)
      age_seconds = [now.to_f - post.created_at.to_f, 0.0].max
      freshness =
        formula.fetch(:freshness_max_bonus) *
          0.5**(age_seconds / formula.fetch(:freshness_half_life_seconds))
      Math.log(1 + [engagement, 0.0].max) + freshness
    end

    def self.public_post?(post)
      public_post_types.include?(post.post_type) && post.deleted_at.nil? && !post.hidden? &&
        !post.user_deleted?
    end

    def self.recalculate_topic(topic_id, timeout_ms: nil)
      return 0 if topic_id.blank?

      calculated_at = Time.current
      formula = formula_settings
      max_timeout_ms = SiteSetting.nested_replies_hot_max_statement_timeout_ms
      timeout_ms = (timeout_ms || max_timeout_ms).to_i.clamp(1, max_timeout_ms)
      result =
        ActiveRecord::Base.transaction do
          DB.exec "SET LOCAL statement_timeout = #{timeout_ms}"
          DB.exec(
            "SET LOCAL lock_timeout = #{[timeout_ms, SiteSetting.nested_replies_hot_lock_timeout_ms].min}",
          )
          DB.query(refresh_sql, topic_id: topic_id, calculated_at: calculated_at, **formula).first
        end

      raise InvalidTree, "Cycle in nested replies for topic #{topic_id}" if result.invalid_tree
      unless result.snapshot_written
        raise MissingOriginalPost, "Missing original post for topic #{topic_id}"
      end

      result.rows_written.to_i
    end

    def self.refresh_sql
      @refresh_sql ||=
        begin
          own_score_sql =
            hot_score_sql(
              "topic_posts",
              reply_count_sql: "direct_reply_counts.direct_reply_count",
              now_sql: ":calculated_at::timestamp",
              formula: {
                like_weight: ":like_weight",
                reply_weight: ":reply_weight",
                freshness_max_bonus: ":freshness_max_bonus",
                freshness_half_life_seconds: ":freshness_half_life_seconds",
              },
            )
          public_topic_post_sql = public_post_sql("posts")
          carrier_topic_post_sql = carrier_post_sql("posts")
          carrier_parent_post_sql = carrier_post_sql("parent")

          <<~SQL
            WITH RECURSIVE
            topic_posts AS MATERIALIZED (
              SELECT posts.id,
                     posts.post_number,
                     posts.reply_to_post_number,
                     posts.post_type,
                     posts.deleted_at,
                     posts.hidden,
                     posts.user_deleted,
                     posts.like_score,
                     posts.created_at,
                     (#{public_topic_post_sql}) AS is_public,
                     (#{carrier_topic_post_sql}) AS is_carrier
              FROM posts
              WHERE posts.topic_id = :topic_id
            ),
            direct_reply_counts AS (
              SELECT child.reply_to_post_number,
                     COUNT(*) FILTER (WHERE child.is_public) AS direct_reply_count
              FROM topic_posts child
              WHERE child.post_number > 1
                AND child.reply_to_post_number IS NOT NULL
              GROUP BY child.reply_to_post_number
            ),
            scored_posts AS MATERIALIZED (
              SELECT topic_posts.id,
                     topic_posts.post_number,
                     topic_posts.reply_to_post_number,
                     topic_posts.is_public,
                     topic_posts.is_carrier,
                     CASE
                       WHEN topic_posts.post_number = 1 THEN NULL
                       ELSE #{own_score_sql}
                     END AS hot_score
              FROM topic_posts
              LEFT JOIN direct_reply_counts
                ON direct_reply_counts.reply_to_post_number = topic_posts.post_number
            ),
            cycle_candidates AS (
              SELECT topic_posts.post_number,
                     topic_posts.reply_to_post_number
              FROM topic_posts
              WHERE topic_posts.post_number > 1
                AND topic_posts.reply_to_post_number >= topic_posts.post_number
            ),
            cycle_walk (
              current_post_number,
              parent_post_number,
              path,
              cycle
            ) AS (
              SELECT cycle_candidates.post_number,
                     cycle_candidates.reply_to_post_number,
                     ARRAY[cycle_candidates.post_number]::integer[],
                     false
              FROM cycle_candidates

              UNION ALL

              SELECT parent.post_number,
                     parent.reply_to_post_number,
                     cycle_walk.path || parent.post_number,
                     parent.post_number = ANY(cycle_walk.path)
              FROM cycle_walk
              JOIN LATERAL (
                SELECT posts.post_number,
                       posts.reply_to_post_number
                FROM posts
                WHERE posts.topic_id = :topic_id
                  AND posts.post_number = cycle_walk.parent_post_number
                LIMIT 1
              ) parent ON true
              WHERE cycle_walk.parent_post_number > 1
                AND NOT cycle_walk.cycle
            ),
            tree_validation AS MATERIALIZED (
              SELECT NOT EXISTS (
                SELECT 1
                FROM cycle_walk
                WHERE cycle_walk.cycle
              ) AS valid
            ),
            original_post AS MATERIALIZED (
              SELECT topic_posts.id
              FROM topic_posts
              WHERE topic_posts.post_number = 1
              LIMIT 1
            ),
            propagation (
              target_post_id,
              parent_post_number,
              propagated_score,
              path
            ) AS (
              SELECT scored_posts.id,
                     scored_posts.reply_to_post_number,
                     scored_posts.hot_score,
                     ARRAY[scored_posts.post_number]::integer[]
              FROM scored_posts
              CROSS JOIN tree_validation
              WHERE scored_posts.post_number > 1
                AND scored_posts.is_public
                AND tree_validation.valid

              UNION ALL

              SELECT parent.id,
                     parent.reply_to_post_number,
                     propagation.propagated_score - :child_penalty,
                     propagation.path || parent.post_number
              FROM propagation
              JOIN posts parent
                ON parent.topic_id = :topic_id
               AND parent.post_number = propagation.parent_post_number
              WHERE propagation.parent_post_number > 1
                AND (#{carrier_parent_post_sql})
                AND propagation.propagated_score > :child_penalty
                AND NOT parent.post_number = ANY(propagation.path)
            ),
            thread_scores AS (
              SELECT propagation.target_post_id AS post_id,
                     MAX(propagation.propagated_score) AS thread_hot_score
              FROM propagation
              GROUP BY propagation.target_post_id
            ),
            calculated_scores AS MATERIALIZED (
              SELECT scored_posts.id AS post_id,
                     scored_posts.hot_score,
                     GREATEST(
                       scored_posts.hot_score,
                       COALESCE(thread_scores.thread_hot_score, #{HOT_SCORE_FLOOR})
                     ) AS thread_hot_score
              FROM scored_posts
              LEFT JOIN thread_scores ON thread_scores.post_id = scored_posts.id
              WHERE scored_posts.post_number > 1
                AND scored_posts.is_carrier
            ),
            upserted_scores AS (
              INSERT INTO nested_hot_post_scores (
                post_id,
                topic_id,
                hot_score,
                thread_hot_score
              )
              SELECT calculated_scores.post_id,
                     :topic_id,
                     calculated_scores.hot_score,
                     calculated_scores.thread_hot_score
              FROM calculated_scores
              CROSS JOIN tree_validation
              WHERE tree_validation.valid
                AND EXISTS (SELECT 1 FROM original_post)
              ON CONFLICT (post_id) DO UPDATE SET
                topic_id = EXCLUDED.topic_id,
                hot_score = EXCLUDED.hot_score,
                thread_hot_score = EXCLUDED.thread_hot_score
              RETURNING post_id
            ),
            removed_scores AS (
              DELETE FROM nested_hot_post_scores cached_scores
              WHERE cached_scores.topic_id = :topic_id
                AND (SELECT valid FROM tree_validation)
                AND EXISTS (SELECT 1 FROM original_post)
                AND NOT EXISTS (
                  SELECT 1
                  FROM calculated_scores
                  WHERE calculated_scores.post_id = cached_scores.post_id
                )
              RETURNING post_id
            ),
            upserted_snapshot AS (
              INSERT INTO nested_hot_score_snapshots (
                topic_id,
                calculated_at
              )
              SELECT :topic_id,
                     :calculated_at::timestamp
              FROM tree_validation
              WHERE tree_validation.valid
                AND EXISTS (SELECT 1 FROM original_post)
              ON CONFLICT (topic_id) DO UPDATE SET
                calculated_at = EXCLUDED.calculated_at
              RETURNING topic_id
            )
            SELECT (SELECT COUNT(*) FROM upserted_scores) AS rows_written,
                   (SELECT COUNT(*) FROM removed_scores) AS rows_removed,
                   NOT tree_validation.valid AS invalid_tree,
                   EXISTS (SELECT 1 FROM upserted_snapshot) AS snapshot_written
            FROM tree_validation
          SQL
        end
    end
    private_class_method :refresh_sql
  end
end