perf(backend): avoid N+1 selects from `user` table when packing many entities (#13911)

* perf(backend): avoid N+1 selects from `user` table when packing many entities

* perf(backend): use `packMany` instead of mapping to `pack`

7 files changed, 20 insertions, 12 deletions

diff --git a/packages/backend/src/server/api/endpoints/admin/roles/users.ts b/packages/backend/src/server/api/endpoints/admin/roles/users.ts
index 45758d4f50..198166bec2 100644
--- a/packages/backend/src/server/api/endpoints/admin/roles/users.ts
+++ b/packages/backend/src/server/api/endpoints/admin/roles/users.ts
@@ -89,10 +89,13 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				.limit(ps.limit)
 				.getMany();
 
+			const _users = assigns.map(({ user, userId }) => user ?? userId);
+			const _userMap = await this.userEntityService.packMany(_users, me, { schema: 'UserDetailed' })
+				.then(users => new Map(users.map(u => [u.id, u])));
 			return await Promise.all(assigns.map(async assign => ({
 				id: assign.id,
 				createdAt: this.idService.parse(assign.id).date.toISOString(),
-				user: await this.userEntityService.pack(assign.user!, me, { schema: 'UserDetailed' }),
+				user: _userMap.get(assign.userId) ?? await this.userEntityService.pack(assign.user!, me, { schema: 'UserDetailed' }),
 				expiresAt: assign.expiresAt?.toISOString() ?? null,
 			})));
 		});
diff --git a/packages/backend/src/server/api/endpoints/drive/files/find.ts b/packages/backend/src/server/api/endpoints/drive/files/find.ts
index 595a6957b2..502d42f9e0 100644
--- a/packages/backend/src/server/api/endpoints/drive/files/find.ts
+++ b/packages/backend/src/server/api/endpoints/drive/files/find.ts
@@ -54,7 +54,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				folderId: ps.folderId ?? IsNull(),
 			});
 
-			return await Promise.all(files.map(file => this.driveFileEntityService.pack(file, { self: true })));
+			return await this.driveFileEntityService.packMany(files, { self: true });
 		});
 	}
 }
diff --git a/packages/backend/src/server/api/endpoints/following/requests/list.ts b/packages/backend/src/server/api/endpoints/following/requests/list.ts
index 88f559138b..fa59e38976 100644
--- a/packages/backend/src/server/api/endpoints/following/requests/list.ts
+++ b/packages/backend/src/server/api/endpoints/following/requests/list.ts
@@ -71,7 +71,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				.limit(ps.limit)
 				.getMany();
 
-			return await Promise.all(requests.map(req => this.followRequestEntityService.pack(req)));
+			return await this.followRequestEntityService.packMany(requests, me);
 		});
 	}
 }
diff --git a/packages/backend/src/server/api/endpoints/notes/reactions.ts b/packages/backend/src/server/api/endpoints/notes/reactions.ts
index a0a1fd9728..97b12ab7f7 100644
--- a/packages/backend/src/server/api/endpoints/notes/reactions.ts
+++ b/packages/backend/src/server/api/endpoints/notes/reactions.ts
@@ -76,7 +76,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 
 			const reactions = await query.limit(ps.limit).getMany();
 
-			return await Promise.all(reactions.map(reaction => this.noteReactionEntityService.pack(reaction, me)));
+			return await this.noteReactionEntityService.packMany(reactions, me);
 		});
 	}
 }
diff --git a/packages/backend/src/server/api/endpoints/roles/users.ts b/packages/backend/src/server/api/endpoints/roles/users.ts
index 85d100ce1c..48d350af59 100644
--- a/packages/backend/src/server/api/endpoints/roles/users.ts
+++ b/packages/backend/src/server/api/endpoints/roles/users.ts
@@ -92,9 +92,12 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				.limit(ps.limit)
 				.getMany();
 
+			const _users = assigns.map(({ user, userId }) => user ?? userId);
+			const _userMap = await this.userEntityService.packMany(_users, me, { schema: 'UserDetailed' })
+				.then(users => new Map(users.map(u => [u.id, u])));
 			return await Promise.all(assigns.map(async assign => ({
 				id: assign.id,
-				user: await this.userEntityService.pack(assign.user!, me, { schema: 'UserDetailed' }),
+				user: _userMap.get(assign.userId) ?? await this.userEntityService.pack(assign.user!, me, { schema: 'UserDetailed' }),
 			})));
 		});
 	}
diff --git a/packages/backend/src/server/api/endpoints/users/get-frequently-replied-users.ts b/packages/backend/src/server/api/endpoints/users/get-frequently-replied-users.ts
index 02aa037466..9248a2fa68 100644
--- a/packages/backend/src/server/api/endpoints/users/get-frequently-replied-users.ts
+++ b/packages/backend/src/server/api/endpoints/users/get-frequently-replied-users.ts
@@ -118,12 +118,14 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			const repliedUsersSorted = Object.keys(repliedUsers).sort((a, b) => repliedUsers[b] - repliedUsers[a]);
 
 			// Extract top replied users
-			const topRepliedUsers = repliedUsersSorted.slice(0, ps.limit);
+			const topRepliedUserIds = repliedUsersSorted.slice(0, ps.limit);
 
 			// Make replies object (includes weights)
-			const repliesObj = await Promise.all(topRepliedUsers.map(async (user) => ({
-				user: await this.userEntityService.pack(user, me, { schema: 'UserDetailed' }),
-				weight: repliedUsers[user] / peak,
+			const _userMap = await this.userEntityService.packMany(topRepliedUserIds, me, { schema: 'UserDetailed' })
+				.then(users => new Map(users.map(u => [u.id, u])));
+			const repliesObj = await Promise.all(topRepliedUserIds.map(async (userId) => ({
+				user: _userMap.get(userId) ?? await this.userEntityService.pack(userId, me, { schema: 'UserDetailed' }),
+				weight: repliedUsers[userId] / peak,
 			})));
 
 			return repliesObj;
diff --git a/packages/backend/src/server/api/endpoints/users/show.ts b/packages/backend/src/server/api/endpoints/users/show.ts
index 26cfa921c5..062326e28d 100644
--- a/packages/backend/src/server/api/endpoints/users/show.ts
+++ b/packages/backend/src/server/api/endpoints/users/show.ts
@@ -117,9 +117,9 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 					if (user != null) _users.push(user);
 				}
 
-				return await Promise.all(_users.map(u => this.userEntityService.pack(u, me, {
-					schema: 'UserDetailed',
-				})));
+				const _userMap = await this.userEntityService.packMany(_users, me, { schema: 'UserDetailed' })
+					.then(users => new Map(users.map(u => [u.id, u])));
+				return _users.map(u => _userMap.get(u.id)!);
 			} else {
 				// Lookup user
 				if (typeof ps.host === 'string' && typeof ps.username === 'string') {