Skip to content

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

1.7 KB
Raw Blame
# frozen_string_literal: true

module DiscourseGamification
  class DirectoryIntegration
    def self.query
      <<~SQL
        WITH default_leaderboard AS (
          SELECT
            id,
            from_date,
            to_date
          FROM
            gamification_leaderboards
          ORDER BY
            id ASC
          LIMIT 1
        ), total_score AS (
          SELECT
            gls.user_id,
            SUM(gls.score) AS score
          FROM
            gamification_leaderboard_scores gls
          INNER JOIN
            default_leaderboard lb ON gls.leaderboard_id = lb.id
          WHERE
            gls.date >= :since
            AND
            (
              (
                lb.from_date IS NULL
                OR
                gls.date >= lb.from_date
              )
              AND
              (
                lb.to_date IS NULL
                OR
                gls.date <= lb.to_date
              )
            )
          GROUP BY
            1
        ), scored_directory AS (
          SELECT
            directory_items.user_id,
            COALESCE(total_score.score, 0) AS score
          FROM
            directory_items
          LEFT JOIN
            total_score ON total_score.user_id = directory_items.user_id
          WHERE
            directory_items.period_type = :period_type
        )
        UPDATE
          directory_items
        SET
          gamification_score = scored_directory.score
        FROM
          scored_directory
        WHERE
          scored_directory.user_id = directory_items.user_id AND
          directory_items.period_type = :period_type AND
          scored_directory.score != directory_items.gamification_score
      SQL
    end
  end
end