Use batch database operations instead of one by one to optimze api pulls (#32680)

Resolve #31492

The response time for the Pull Requests API has improved significantly,
dropping from over `2000ms` to about `350ms` on my local machine. It's
about `6` times faster.

A key area for further optimization lies in batch-fetching data for
`apiPullRequest.ChangedFiles, apiPullRequest.Additions, and
apiPullRequest.Deletions`.

Tests `TestAPIViewPulls` does exist and new tests added.

- This PR also fixes some bugs in `GetDiff` functions.
- This PR also fixes data inconsistent in test data. For a pull request,
the head branch's reference should be equal to the reference in
`pull/xxx/head`.

5 files changed, 147 insertions, 39 deletions

diff --git a/models/issues/pull_list.go b/models/issues/pull_list.go
index 9155ea0834..59010aa9d0 100644
--- a/models/issues/pull_list.go
+++ b/models/issues/pull_list.go
@@ -16,6 +16,7 @@ import (
 	"code.gitea.io/gitea/modules/log"
 	"code.gitea.io/gitea/modules/util"
 
+	"xorm.io/builder"
 	"xorm.io/xorm"
 )
 
@@ -240,6 +241,64 @@ func (prs PullRequestList) GetIssueIDs() []int64 {
 	})
 }
 
+func (prs PullRequestList) LoadReviewCommentsCounts(ctx context.Context) (map[int64]int, error) {
+	issueIDs := prs.GetIssueIDs()
+	countsMap := make(map[int64]int, len(issueIDs))
+	counts := make([]struct {
+		IssueID int64
+		Count   int
+	}, 0, len(issueIDs))
+	if err := db.GetEngine(ctx).Select("issue_id, count(*) as count").
+		Table("comment").In("issue_id", issueIDs).And("type = ?", CommentTypeReview).
+		GroupBy("issue_id").Find(&counts); err != nil {
+		return nil, err
+	}
+	for _, c := range counts {
+		countsMap[c.IssueID] = c.Count
+	}
+	return countsMap, nil
+}
+
+func (prs PullRequestList) LoadReviews(ctx context.Context) (ReviewList, error) {
+	issueIDs := prs.GetIssueIDs()
+	reviews := make([]*Review, 0, len(issueIDs))
+
+	subQuery := builder.Select("max(id) as id").
+		From("review").
+		Where(builder.In("issue_id", issueIDs)).
+		And(builder.In("`type`", ReviewTypeApprove, ReviewTypeReject, ReviewTypeRequest)).
+		And(builder.Eq{
+			"dismissed":          false,
+			"original_author_id": 0,
+			"reviewer_team_id":   0,
+		}).
+		GroupBy("issue_id, reviewer_id")
+	// Get latest review of each reviewer, sorted in order they were made
+	if err := db.GetEngine(ctx).In("id", subQuery).OrderBy("review.updated_unix ASC").Find(&reviews); err != nil {
+		return nil, err
+	}
+
+	teamReviewRequests := make([]*Review, 0, 5)
+	subQueryTeam := builder.Select("max(id) as id").
+		From("review").
+		Where(builder.In("issue_id", issueIDs)).
+		And(builder.Eq{
+			"original_author_id": 0,
+		}).And(builder.Neq{
+		"reviewer_team_id": 0,
+	}).
+		GroupBy("issue_id, reviewer_team_id")
+	if err := db.GetEngine(ctx).In("id", subQueryTeam).OrderBy("review.updated_unix ASC").Find(&teamReviewRequests); err != nil {
+		return nil, err
+	}
+
+	if len(teamReviewRequests) > 0 {
+		reviews = append(reviews, teamReviewRequests...)
+	}
+
+	return reviews, nil
+}
+
 // HasMergedPullRequestInRepo returns whether the user(poster) has merged pull-request in the repo
 func HasMergedPullRequestInRepo(ctx context.Context, repoID, posterID int64) (bool, error) {
 	return db.GetEngine(ctx).
diff --git a/models/issues/pull_list_test.go b/models/issues/pull_list_test.go
new file mode 100644
index 0000000000..8b814a0d0f
--- /dev/null
+++ b/models/issues/pull_list_test.go
@@ -0,0 +1,64 @@
+// Copyright 2024 The Gitea Authors. All rights reserved.
+// SPDX-License-Identifier: MIT
+
+package issues_test
+
+import (
+	"testing"
+
+	"code.gitea.io/gitea/models/db"
+	issues_model "code.gitea.io/gitea/models/issues"
+	"code.gitea.io/gitea/models/unittest"
+
+	"github.com/stretchr/testify/assert"
+)
+
+func TestPullRequestList_LoadAttributes(t *testing.T) {
+	assert.NoError(t, unittest.PrepareTestDatabase())
+
+	prs := []*issues_model.PullRequest{
+		unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}),
+		unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}),
+	}
+	assert.NoError(t, issues_model.PullRequestList(prs).LoadAttributes(db.DefaultContext))
+	for _, pr := range prs {
+		assert.NotNil(t, pr.Issue)
+		assert.Equal(t, pr.IssueID, pr.Issue.ID)
+	}
+
+	assert.NoError(t, issues_model.PullRequestList([]*issues_model.PullRequest{}).LoadAttributes(db.DefaultContext))
+}
+
+func TestPullRequestList_LoadReviewCommentsCounts(t *testing.T) {
+	assert.NoError(t, unittest.PrepareTestDatabase())
+
+	prs := []*issues_model.PullRequest{
+		unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}),
+		unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}),
+	}
+	reviewComments, err := issues_model.PullRequestList(prs).LoadReviewCommentsCounts(db.DefaultContext)
+	assert.NoError(t, err)
+	assert.Len(t, reviewComments, 2)
+	for _, pr := range prs {
+		assert.EqualValues(t, reviewComments[pr.IssueID], 1)
+	}
+}
+
+func TestPullRequestList_LoadReviews(t *testing.T) {
+	assert.NoError(t, unittest.PrepareTestDatabase())
+
+	prs := []*issues_model.PullRequest{
+		unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}),
+		unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}),
+	}
+	reviewList, err := issues_model.PullRequestList(prs).LoadReviews(db.DefaultContext)
+	assert.NoError(t, err)
+	// 1, 7, 8, 9, 10, 22
+	assert.Len(t, reviewList, 6)
+	assert.EqualValues(t, 1, reviewList[0].ID)
+	assert.EqualValues(t, 7, reviewList[1].ID)
+	assert.EqualValues(t, 8, reviewList[2].ID)
+	assert.EqualValues(t, 9, reviewList[3].ID)
+	assert.EqualValues(t, 10, reviewList[4].ID)
+	assert.EqualValues(t, 22, reviewList[5].ID)
+}
diff --git a/models/issues/pull_test.go b/models/issues/pull_test.go
index 675c90527d..cb7b47263d 100644
--- a/models/issues/pull_test.go
+++ b/models/issues/pull_test.go
@@ -79,7 +79,7 @@ func TestPullRequestsNewest(t *testing.T) {
 func TestLoadRequestedReviewers(t *testing.T) {
 	assert.NoError(t, unittest.PrepareTestDatabase())
 
-	pull := unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1})
+	pull := unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2})
 	assert.NoError(t, pull.LoadIssue(db.DefaultContext))
 	issue := pull.Issue
 	assert.NoError(t, issue.LoadRepo(db.DefaultContext))
@@ -93,7 +93,7 @@ func TestLoadRequestedReviewers(t *testing.T) {
 	assert.NotNil(t, comment)
 
 	assert.NoError(t, pull.LoadRequestedReviewers(db.DefaultContext))
-	assert.Len(t, pull.RequestedReviewers, 1)
+	assert.Len(t, pull.RequestedReviewers, 6)
 
 	comment, err = issues_model.RemoveReviewRequest(db.DefaultContext, issue, user1, &user_model.User{})
 	assert.NoError(t, err)
@@ -234,22 +234,6 @@ func TestPullRequest_UpdateCols(t *testing.T) {
 	unittest.CheckConsistencyFor(t, pr)
 }
 
-func TestPullRequestList_LoadAttributes(t *testing.T) {
-	assert.NoError(t, unittest.PrepareTestDatabase())
-
-	prs := []*issues_model.PullRequest{
-		unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}),
-		unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}),
-	}
-	assert.NoError(t, issues_model.PullRequestList(prs).LoadAttributes(db.DefaultContext))
-	for _, pr := range prs {
-		assert.NotNil(t, pr.Issue)
-		assert.Equal(t, pr.IssueID, pr.Issue.ID)
-	}
-
-	assert.NoError(t, issues_model.PullRequestList([]*issues_model.PullRequest{}).LoadAttributes(db.DefaultContext))
-}
-
 // TODO TestAddTestPullRequestTask
 
 func TestPullRequest_IsWorkInProgress(t *testing.T) {
diff --git a/models/issues/review_list.go b/models/issues/review_list.go
index a5ceb21791..bc7d7ec0f0 100644
--- a/models/issues/review_list.go
+++ b/models/issues/review_list.go
@@ -47,14 +47,9 @@ func (reviews ReviewList) LoadReviewersTeams(ctx context.Context) error {
 		}
 	}
 
-	teamsMap := make(map[int64]*organization_model.Team, 0)
-	for _, teamID := range reviewersTeamsIDs {
-		team, err := organization_model.GetTeamByID(ctx, teamID)
-		if err != nil {
-			return err
-		}
-
-		teamsMap[teamID] = team
+	teamsMap, err := organization_model.GetTeamsByIDs(ctx, reviewersTeamsIDs)
+	if err != nil {
+		return err
 	}
 
 	for _, review := range reviews {
diff --git a/models/issues/review_test.go b/models/issues/review_test.go
index 942121fd8f..50330e3ff2 100644
--- a/models/issues/review_test.go
+++ b/models/issues/review_test.go
@@ -126,42 +126,48 @@ func TestGetReviewersByIssueID(t *testing.T) {
 	user2 := unittest.AssertExistsAndLoadBean(t, &user_model.User{ID: 2})
 	org3 := unittest.AssertExistsAndLoadBean(t, &user_model.User{ID: 3})
 	user4 := unittest.AssertExistsAndLoadBean(t, &user_model.User{ID: 4})
+	user5 := unittest.AssertExistsAndLoadBean(t, &user_model.User{ID: 5})
 
 	expectedReviews := []*issues_model.Review{}
 	expectedReviews = append(expectedReviews,
 		&issues_model.Review{
+			ID:          7,
 			Reviewer:    org3,
 			Type:        issues_model.ReviewTypeReject,
 			UpdatedUnix: 946684812,
 		},
 		&issues_model.Review{
+			ID:          8,
 			Reviewer:    user4,
 			Type:        issues_model.ReviewTypeApprove,
 			UpdatedUnix: 946684813,
 		},
 		&issues_model.Review{
+			ID:          9,
 			Reviewer:    user2,
 			Type:        issues_model.ReviewTypeReject,
 			UpdatedUnix: 946684814,
-		})
+		},
+		&issues_model.Review{
+			ID:          10,
+			Reviewer:    user_model.NewGhostUser(),
+			Type:        issues_model.ReviewTypeReject,
+			UpdatedUnix: 946684815,
+		},
+		&issues_model.Review{
+			ID:          22,
+			Reviewer:    user5,
+			Type:        issues_model.ReviewTypeRequest,
+			UpdatedUnix: 946684817,
+		},
+	)
 
 	allReviews, err := issues_model.GetReviewsByIssueID(db.DefaultContext, issue.ID)
 	assert.NoError(t, err)
 	for _, review := range allReviews {
 		assert.NoError(t, review.LoadReviewer(db.DefaultContext))
 	}
-	if assert.Len(t, allReviews, 3) {
-		for i, review := range allReviews {
-			assert.Equal(t, expectedReviews[i].Reviewer, review.Reviewer)
-			assert.Equal(t, expectedReviews[i].Type, review.Type)
-			assert.Equal(t, expectedReviews[i].UpdatedUnix, review.UpdatedUnix)
-		}
-	}
-
-	allReviews, err = issues_model.GetReviewsByIssueID(db.DefaultContext, issue.ID)
-	assert.NoError(t, err)
-	assert.NoError(t, allReviews.LoadReviewers(db.DefaultContext))
-	if assert.Len(t, allReviews, 3) {
+	if assert.Len(t, allReviews, 5) {
 		for i, review := range allReviews {
 			assert.Equal(t, expectedReviews[i].Reviewer, review.Reviewer)
 			assert.Equal(t, expectedReviews[i].Type, review.Type)