PERF: Cache ranks for featured badges, to simplify user serialization (#8698)

PERF: Cache ranks for featured badges, to simplify user serialization (#8698)

diff --git a/app/jobs/scheduled/badge_grant.rb b/app/jobs/scheduled/badge_grant.rb
index b4fd367c69..8e40939291 100644
--- a/app/jobs/scheduled/badge_grant.rb
+++ b/app/jobs/scheduled/badge_grant.rb
@@ -22,6 +22,7 @@ module Jobs
       end
 
       BadgeGranter.revoke_ungranted_titles!
+      UserBadge.ensure_consistency! # Badge granter sometimes uses raw SQL, so hooks do not run. Clean up data
     end
 
   end
diff --git a/app/models/badge.rb b/app/models/badge.rb
index 10e228bbfa..7ff7ce0710 100644
--- a/app/models/badge.rb
+++ b/app/models/badge.rb
@@ -115,6 +115,7 @@ class Badge < ActiveRecord::Base
   after_commit do
     SvgSprite.expire_cache
     UserStat.update_distinct_badge_count if saved_change_to_enabled?
+    UserBadge.ensure_consistency! if saved_change_to_enabled?
   end
 
   # fields that can not be edited on system badges
diff --git a/app/models/user.rb b/app/models/user.rb
index 8a7d48fcba..2a6f303c92 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -20,8 +20,14 @@ class User < ActiveRecord::Base
   has_many :user_actions
   has_many :post_actions
 
-  has_many :user_badges, -> { where('user_badges.badge_id IN (SELECT id FROM badges WHERE enabled)') }, dependent: :destroy
+  DEFAULT_FEATURED_BADGE_COUNT = 3
+
+  has_many :user_badges, -> { for_enabled_badges }, dependent: :destroy
   has_many :badges, through: :user_badges
+  has_many :default_featured_user_badges,
+            -> { for_enabled_badges.grouped_with_count.where("featured_rank <= ?", DEFAULT_FEATURED_BADGE_COUNT) },
+            class_name: "UserBadge"
+
   has_many :email_logs, dependent: :delete_all
   has_many :incoming_emails, dependent: :delete_all
   has_many :post_timings
@@ -961,28 +967,12 @@ class User < ActiveRecord::Base
     user_stat&.distinct_badge_count
   end
 
-  def featured_user_badges(limit = 3)
-    tl_badge_ids = Badge.trust_level_badge_ids
-
-    query = user_badges
-      .group(:badge_id)
-      .select(UserBadge.attribute_names.map { |x| "MAX(user_badges.#{x}) AS #{x}" },
-                      'COUNT(*) AS "count"',
-                      'MAX(badges.badge_type_id) AS badges_badge_type_id',
-                      'MAX(badges.grant_count) AS badges_grant_count')
-      .joins(:badge)
-      .order('badges_badge_type_id ASC, badges_grant_count ASC, badge_id DESC')
-      .includes(:user, :granted_by, { badge: :badge_type }, post: :topic)
-
-    tl_badge = query.where("user_badges.badge_id IN (:tl_badge_ids)",
-                           tl_badge_ids: tl_badge_ids)
-      .limit(1)
-
-    other_badges = query.where("user_badges.badge_id NOT IN (:tl_badge_ids)",
-                               tl_badge_ids: tl_badge_ids)
-      .limit(limit)
-
-    (tl_badge + other_badges).take(limit)
+  def featured_user_badges(limit = DEFAULT_FEATURED_BADGE_COUNT)
+    if limit == DEFAULT_FEATURED_BADGE_COUNT
+      default_featured_user_badges
+    else
+      user_badges.grouped_with_count.where("featured_rank <= ?", limit)
+    end
   end
 
   def self.count_by_signup_date(start_date = nil, end_date = nil, group_id = nil)
diff --git a/app/models/user_badge.rb b/app/models/user_badge.rb
index c9b84787f9..5f4a3fcd07 100644
--- a/app/models/user_badge.rb
+++ b/app/models/user_badge.rb
@@ -7,6 +7,16 @@ class UserBadge < ActiveRecord::Base
   belongs_to :notification, dependent: :destroy
   belongs_to :post
 
+  scope :grouped_with_count, -> {
+    group(:badge_id, :user_id)
+      .select(UserBadge.attribute_names.map { |x| "MAX(user_badges.#{x}) AS #{x}" },
+              'COUNT(*) AS "count"')
+      .order('MAX(featured_rank) ASC')
+      .includes(:user, :granted_by, { badge: :badge_type }, post: :topic)
+  }
+
+  scope :for_enabled_badges, -> { where('user_badges.badge_id IN (SELECT id FROM badges WHERE enabled)') }
+
   validates :badge_id,
     presence: true,
     uniqueness: { scope: :user_id },
@@ -19,15 +29,58 @@ class UserBadge < ActiveRecord::Base
   after_create do
     Badge.increment_counter 'grant_count', self.badge_id
     UserStat.update_distinct_badge_count self.user_id
+    UserBadge.update_featured_ranks! self.user_id
     DiscourseEvent.trigger(:user_badge_granted, self.badge_id, self.user_id)
   end
 
   after_destroy do
     Badge.decrement_counter 'grant_count', self.badge_id
     UserStat.update_distinct_badge_count self.user_id
+    UserBadge.update_featured_ranks! self.user_id
     DiscourseEvent.trigger(:user_badge_removed, self.badge_id, self.user_id)
   end
 
+  def self.ensure_consistency!
+    self.update_featured_ranks!
+  end
+
+  def self.update_featured_ranks!(user_id = nil)
+    query = <<~SQL
+      WITH featured_tl_badge AS -- Find the best trust level badge for each user
+      (
+        SELECT user_id, max(badge_id) as badge_id
+        FROM user_badges
+        WHERE badge_id IN (1,2,3,4)
+        #{"AND user_id = #{user_id.to_i}" if user_id}
+        GROUP BY user_id
+      ),
+      ranks AS ( -- Take all user badges, group by user_id and badge_id, and calculate a rank for each one
+        SELECT
+          user_badges.user_id,
+          user_badges.badge_id,
+          RANK() OVER (
+            PARTITION BY user_badges.user_id -- Do a separate rank for each user
+            ORDER BY BOOL_OR(badges.enabled) DESC, -- Disabled badges last
+                    MAX(featured_tl_badge.user_id) NULLS LAST, -- Best tl badge first
+                    CASE WHEN user_badges.badge_id IN (1,2,3,4) THEN 1 ELSE 0 END ASC, -- Non-featured tl badges last
+                    MAX(badges.badge_type_id) ASC,
+                    MAX(badges.grant_count) ASC,
+                    user_badges.badge_id DESC
+          ) rank_number
+        FROM user_badges
+        INNER JOIN badges ON badges.id = user_badges.badge_id
+        LEFT JOIN featured_tl_badge ON featured_tl_badge.user_id = user_badges.user_id AND featured_tl_badge.badge_id = user_badges.badge_id
+        #{"WHERE user_badges.user_id = #{user_id.to_i}" if user_id}
+        GROUP BY user_badges.user_id, user_badges.badge_id
+      )
+      -- Now use that data to update the featured_rank column
+      UPDATE user_badges SET featured_rank = rank_number
+      FROM ranks WHERE ranks.badge_id = user_badges.badge_id AND ranks.user_id = user_badges.user_id AND featured_rank IS DISTINCT FROM rank_number
+    SQL
+
+    DB.exec query
+  end
+
   private
 
   def single_grant_badge?
@@ -47,6 +100,7 @@ end
 #  post_id         :integer
 #  notification_id :integer
 #  seq             :integer          default(0), not null
+#  featured_rank   :integer
 #
 # Indexes
 #
diff --git a/db/migrate/20200107161405_add_featured_rank_to_user_badges.rb b/db/migrate/20200107161405_add_featured_rank_to_user_badges.rb
new file mode 100644
index 0000000000..a480d7b231
--- /dev/null
+++ b/db/migrate/20200107161405_add_featured_rank_to_user_badges.rb
@@ -0,0 +1,37 @@
+# frozen_string_literal: true
+class AddFeaturedRankToUserBadges < ActiveRecord::Migration[6.0]
+  def change
+    add_column :user_badges, :featured_rank, :integer, null: true
+
+    execute <<~SQL
+      WITH featured_tl_badge AS -- Find the best trust level badge for each user
+      (
+        SELECT user_id, max(badge_id) as badge_id
+        FROM user_badges
+        WHERE badge_id IN (1,2,3,4)
+        GROUP BY user_id
+      ),
+      ranks AS ( -- Take all user badges, group by user_id and badge_id, and calculate a rank for each one
+        SELECT
+          user_badges.user_id,
+          user_badges.badge_id,
+          RANK() OVER (
+            PARTITION BY user_badges.user_id -- Do a separate rank for each user
+            ORDER BY BOOL_OR(badges.enabled) DESC, -- Disabled badges last
+                    MAX(featured_tl_badge.user_id) NULLS LAST, -- Best tl badge first
+                    CASE WHEN user_badges.badge_id IN (1,2,3,4) THEN 1 ELSE 0 END ASC, -- Non-featured tl badges last
+                    MAX(badges.badge_type_id) ASC,
+                    MAX(badges.grant_count) ASC,
+                    user_badges.badge_id DESC
+          ) rank_number

[... diff too long, it was truncated ...]

GitHub sha: cff6e941

FIX: Update featured badge ranking when mass-awarding badges