Merge branch '34510-board-issues-sql-speedup' into 'master'

fix #34510 add association preloading for issue boards

Closes #34510

See merge request gitlab-org/gitlab-ce!14198

4 files changed, 31 insertions, 5 deletions

diff --git a/app/controllers/boards/issues_controller.rb b/app/controllers/boards/issues_controller.rb
index 8d4ec2d6d9d..0d74078645a 100644
--- a/app/controllers/boards/issues_controller.rb
+++ b/app/controllers/boards/issues_controller.rb
@@ -11,9 +11,15 @@ module Boards
       issues = Boards::Issues::ListService.new(board_parent, current_user, filter_params).execute
       issues = issues.page(params[:page]).per(params[:per] || 20)
       make_sure_position_is_set(issues)
+      issues = issues.preload(:project,
+                              :milestone,
+                              :assignees,
+                              labels: [:priorities],
+                              notes: [:award_emoji, :author]
+                             )
 
       render json: {
-        issues: serialize_as_json(issues.preload(:project)),
+        issues: serialize_as_json(issues),
         size: issues.total_count
       }
     end
@@ -76,14 +82,13 @@ module Boards
 
     def serialize_as_json(resource)
       resource.as_json(
-        labels: true,
         only: [:id, :iid, :project_id, :title, :confidential, :due_date, :relative_position],
+        labels: true,
         include: {
           project: { only: [:id, :path] },
           assignees: { only: [:id, :name, :username], methods: [:avatar_url] },
           milestone: { only: [:id, :title] }
-        },
-        user: current_user
+        }
       )
     end
   end
diff --git a/app/models/label.rb b/app/models/label.rb
index 958141a7358..899028a01a0 100644
--- a/app/models/label.rb
+++ b/app/models/label.rb
@@ -127,7 +127,12 @@ class Label < ActiveRecord::Base
   end
 
   def priority(project)
-    priorities.find_by(project: project).try(:priority)
+    priority = if priorities.loaded?
+                 priorities.first { |p| p.project == project }
+               else
+                 priorities.find_by(project: project)
+               end
+    priority.try(:priority)
   end
 
   def template?
diff --git a/changelogs/unreleased/34510-board-issues-sql-speedup.yml b/changelogs/unreleased/34510-board-issues-sql-speedup.yml
new file mode 100644
index 00000000000..244ff7e9dfa
--- /dev/null
+++ b/changelogs/unreleased/34510-board-issues-sql-speedup.yml
@@ -0,0 +1,5 @@
+---
+title: Optimize the boards' issues fetching.
+merge_request: 14198
+author:
+type: other
diff --git a/spec/controllers/boards/issues_controller_spec.rb b/spec/controllers/boards/issues_controller_spec.rb
index dfa06c78d46..5163099cd98 100644
--- a/spec/controllers/boards/issues_controller_spec.rb
+++ b/spec/controllers/boards/issues_controller_spec.rb
@@ -45,6 +45,17 @@ describe Boards::IssuesController do
           expect(parsed_response.length).to eq 2
           expect(development.issues.map(&:relative_position)).not_to include(nil)
         end
+
+        it 'avoids N+1 database queries' do
+          create(:labeled_issue, project: project, labels: [development])
+          control_count = ActiveRecord::QueryRecorder.new { list_issues(user: user, board: board, list: list2) }.count
+
+          # 25 issues is bigger than the page size
+          # the relative position will ignore the `#make_sure_position_set` queries
+          create_list(:labeled_issue, 25, project: project, labels: [development], assignees: [johndoe], relative_position: 1)
+
+          expect { list_issues(user: user, board: board, list: list2) }.not_to exceed_query_limit(control_count)
+        end
       end
 
       context 'with invalid list id' do