FEATURE: Add `Top Ignored Users` report (#7153)

FEATURE: Add Top Ignored Users report (#7153)

  • FEATURE: Add Top Ignored Users report

Why?

This is part of the Ability to ignore a user feature, and also part of this PR.

We want to send a System Message daily when a specific count threshold for an ignored is reached. To make this system message informative, we want to link to a report for the Top Ignored Users too.

diff --git a/app/models/report.rb b/app/models/report.rb
index d820ad5..e34efd5 100644
--- a/app/models/report.rb
+++ b/app/models/report.rb
@@ -1548,6 +1548,54 @@ class Report
     end
   end
 
+  def self.report_top_ignored_users(report)
+    report.modes = [:table]
+
+    report.labels = [
+      {
+        type: :user,
+        properties: {
+          id: :ignored_user_id,
+          username: :ignored_username,
+          avatar: :ignored_user_avatar_template,
+        },
+        title: I18n.t("reports.top_ignored_users.labels.ignored_user")
+      },
+      {
+        type: :number,
+        properties: [
+          :ignores_count,
+        ],
+        title: I18n.t("reports.top_ignored_users.labels.ignores_count")
+      }
+    ]
+
+    report.data = []
+
+    sql = <<~SQL
+      SELECT
+      u.id AS user_id,
+      u.username,
+      u.uploaded_avatar_id,
+      COUNT(*) AS ignores_count
+      FROM users AS u
+      INNER JOIN ignored_users AS ig ON ig.ignored_user_id = u.id
+      WHERE ig.created_at >= '#{report.start_date}' AND ig.created_at <= '#{report.end_date}'
+      GROUP BY u.id
+      ORDER BY COUNT(*) DESC
+      LIMIT #{report.limit || 250}
+    SQL
+
+    DB.query(sql).each do |row|
+      report.data << {
+        ignored_user_id: row.user_id,
+        ignored_username: row.username,
+        ignored_user_avatar_template: User.avatar_template(row.username, row.uploaded_avatar_id),
+        ignores_count: row.ignores_count,
+      }
+    end
+  end
+
   DiscourseEvent.on(:site_setting_saved) do |site_setting|
     if ["backup_location", "s3_backup_bucket"].include?(site_setting.name.to_s)
       clear_cache(:storage_stats)
diff --git a/config/locales/server.en.yml b/config/locales/server.en.yml
index 182b0a0..5cd4c04 100644
--- a/config/locales/server.en.yml
+++ b/config/locales/server.en.yml
@@ -1227,6 +1227,12 @@ en:
         author: Author
         filesize: File size
       description: "List all uploads by extension, filesize and author."
+    top_ignored_users:
+      title: "Top Ignored Users"
+      labels:
+        ignored_user: Ignored User
+        ignores_count: Ignores count
+      description: "List top ignored users."
 
   dashboard:
     rails_env_warning: "Your server is running in %{env} mode."
diff --git a/spec/models/report_spec.rb b/spec/models/report_spec.rb
index 7061cdf..42e252e 100644
--- a/spec/models/report_spec.rb
+++ b/spec/models/report_spec.rb
@@ -1094,6 +1094,35 @@ describe Report do
     include_examples "no data"
   end
 
+  describe "report_top_ignored_users" do
+    let(:report) { Report.find("top_ignored_users") }
+    let(:tarek) { Fabricate(:user, username: "tarek") }
+    let(:john) { Fabricate(:user, username: "john") }
+    let(:matt) { Fabricate(:user, username: "matt") }
+
+    context "with data" do
+      before do
+        Fabricate(:ignored_user, user: tarek, ignored_user: john)
+        Fabricate(:ignored_user, user: tarek, ignored_user: matt)
+      end
+
+      it "works" do
+        expect(report.data.length).to eq(2)
+        expect_row_to_be_equal(report.data[0], john)
+        expect_row_to_be_equal(report.data[1], matt)
+      end
+
+      def expect_row_to_be_equal(row, user)
+        expect(row[:ignored_user_id]).to eq(user.id)
+        expect(row[:ignored_username]).to eq(user.username)
+        expect(row[:ignored_user_avatar_template]).to eq(User.avatar_template(user.username, user.uploaded_avatar_id))
+        expect(row[:ignores_count]).to eq(1)
+      end
+    end
+
+    include_examples "no data"
+  end
+
   describe "consolidated_page_views" do
     before do
       freeze_time(Time.now.at_midnight)

GitHub sha: 28384ba6