1 files changed, 20 insertions, 0 deletions
diff --git a/app/models/abuse_report.rb b/app/models/abuse_report.rb
index 1d2eee82827..75c90d370c3 100644
--- a/app/models/abuse_report.rb
+++ b/app/models/abuse_report.rb
@@ -18,6 +18,8 @@ class AbuseReport < ApplicationRecord
   belongs_to :assignee, class_name: 'User', inverse_of: :assigned_abuse_reports
 
   has_many :events, class_name: 'ResourceEvents::AbuseReportEvent', inverse_of: :abuse_report
+  has_many :label_links, as: :target, inverse_of: :target
+  has_many :labels, through: :label_links
 
   has_many :abuse_events, class_name: 'Abuse::Event', inverse_of: :abuse_report
 
@@ -214,6 +216,24 @@ class AbuseReport < ApplicationRecord
         extension_list: valid_image_extensions.to_sentence(last_word_connector: ' or '))
     )
   end
+
+  def self.aggregated_by_user_and_category(sort_by_count = false)
+    sub_query = self
+      .select('user_id, category, COUNT(id) as count', 'MIN(id) as min')
+      .group(:user_id, :category)
+
+    reports = AbuseReport.with_users
+      .open
+      .select('aggregated.*, status, id, reporter_id, created_at, updated_at')
+      .from(sub_query, :aggregated)
+      .joins('INNER JOIN abuse_reports on aggregated.min = abuse_reports.id')
+
+    if sort_by_count
+      reports.order(count: :desc, created_at: :desc)
+    else
+      reports
+    end
+  end
 end
 
 AbuseReport.prepend_mod