merge: Avoid more N+1 queries in NoteEntityService and UserEntityService (!1099)

View MR for information: https://activitypub.software/TransFem-org/Sharkey/-/merge_requests/1099

Approved-by: dakkar <dakkar@thenautilus.net>
Approved-by: Marie <github@yuugi.dev>

18 files changed, 34 insertions, 38 deletions

diff --git a/packages/backend/src/server/api/endpoints/admin/nsfw-user.ts b/packages/backend/src/server/api/endpoints/admin/nsfw-user.ts
index 194e793eda..f6c4f0b635 100644
--- a/packages/backend/src/server/api/endpoints/admin/nsfw-user.ts
+++ b/packages/backend/src/server/api/endpoints/admin/nsfw-user.ts
@@ -47,7 +47,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				alwaysMarkNsfw: true,
 			});
 
-			await this.cacheService.userProfileCache.refresh(ps.userId);
+			await this.cacheService.userProfileCache.delete(ps.userId);
 		});
 	}
 }
diff --git a/packages/backend/src/server/api/endpoints/following/delete.ts b/packages/backend/src/server/api/endpoints/following/delete.ts
index ba146b6703..442352a4d2 100644
--- a/packages/backend/src/server/api/endpoints/following/delete.ts
+++ b/packages/backend/src/server/api/endpoints/following/delete.ts
@@ -12,6 +12,7 @@ import { UserFollowingService } from '@/core/UserFollowingService.js';
 import { DI } from '@/di-symbols.js';
 import { GetterService } from '@/server/api/GetterService.js';
 import { ApiError } from '../../error.js';
+import { CacheService } from '@/core/CacheService.js';
 
 export const meta = {
 	tags: ['following', 'users'],
@@ -69,6 +70,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		private userEntityService: UserEntityService,
 		private getterService: GetterService,
 		private userFollowingService: UserFollowingService,
+		private readonly cacheService: CacheService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
 			const follower = me;
@@ -85,12 +87,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			});
 
 			// Check not following
-			const exist = await this.followingsRepository.exists({
-				where: {
-					followerId: follower.id,
-					followeeId: followee.id,
-				},
-			});
+			const exist = await this.cacheService.userFollowingsCache.fetch(follower.id).then(f => f.has(followee.id));
 
 			if (!exist) {
 				throw new ApiError(meta.errors.notFollowing);
diff --git a/packages/backend/src/server/api/endpoints/following/invalidate.ts b/packages/backend/src/server/api/endpoints/following/invalidate.ts
index b45d21410b..3809bf29b0 100644
--- a/packages/backend/src/server/api/endpoints/following/invalidate.ts
+++ b/packages/backend/src/server/api/endpoints/following/invalidate.ts
@@ -11,6 +11,7 @@ import { UserEntityService } from '@/core/entities/UserEntityService.js';
 import { UserFollowingService } from '@/core/UserFollowingService.js';
 import { DI } from '@/di-symbols.js';
 import { GetterService } from '@/server/api/GetterService.js';
+import { CacheService } from '@/core/CacheService.js';
 import { ApiError } from '../../error.js';
 
 export const meta = {
@@ -69,6 +70,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		private userEntityService: UserEntityService,
 		private getterService: GetterService,
 		private userFollowingService: UserFollowingService,
+		private readonly cacheService: CacheService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
 			const followee = me;
@@ -85,12 +87,9 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			});
 
 			// Check not following
-			const exist = await this.followingsRepository.findOneBy({
-				followerId: follower.id,
-				followeeId: followee.id,
-			});
+			const isFollowing = await this.cacheService.userFollowingsCache.fetch(follower.id).then(f => f.has(followee.id));
 
-			if (exist == null) {
+			if (!isFollowing) {
 				throw new ApiError(meta.errors.notFollowing);
 			}
 
diff --git a/packages/backend/src/server/api/endpoints/following/update-all.ts b/packages/backend/src/server/api/endpoints/following/update-all.ts
index c953feb393..a02b51cc79 100644
--- a/packages/backend/src/server/api/endpoints/following/update-all.ts
+++ b/packages/backend/src/server/api/endpoints/following/update-all.ts
@@ -12,6 +12,7 @@ import { UserFollowingService } from '@/core/UserFollowingService.js';
 import { DI } from '@/di-symbols.js';
 import { GetterService } from '@/server/api/GetterService.js';
 import { ApiError } from '../../error.js';
+import { CacheService } from '@/core/CacheService.js';
 
 export const meta = {
 	tags: ['following', 'users'],
@@ -39,6 +40,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 	constructor(
 		@Inject(DI.followingsRepository)
 		private followingsRepository: FollowingsRepository,
+		private readonly cacheService: CacheService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
 			await this.followingsRepository.update({
@@ -48,6 +50,8 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				withReplies: ps.withReplies != null ? ps.withReplies : undefined,
 			});
 
+			await this.cacheService.refreshFollowRelationsFor(me.id);
+
 			return;
 		});
 	}
diff --git a/packages/backend/src/server/api/endpoints/following/update.ts b/packages/backend/src/server/api/endpoints/following/update.ts
index d62cf210ed..f4ca21856f 100644
--- a/packages/backend/src/server/api/endpoints/following/update.ts
+++ b/packages/backend/src/server/api/endpoints/following/update.ts
@@ -11,6 +11,7 @@ import { UserEntityService } from '@/core/entities/UserEntityService.js';
 import { UserFollowingService } from '@/core/UserFollowingService.js';
 import { DI } from '@/di-symbols.js';
 import { GetterService } from '@/server/api/GetterService.js';
+import { CacheService } from '@/core/CacheService.js';
 import { ApiError } from '../../error.js';
 
 export const meta = {
@@ -71,6 +72,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		private userEntityService: UserEntityService,
 		private getterService: GetterService,
 		private userFollowingService: UserFollowingService,
+		private readonly cacheService: CacheService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
 			const follower = me;
@@ -87,10 +89,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			});
 
 			// Check not following
-			const exist = await this.followingsRepository.findOneBy({
-				followerId: follower.id,
-				followeeId: followee.id,
-			});
+			const exist = await this.cacheService.userFollowingsCache.fetch(follower.id).then(f => f.get(followee.id));
 
 			if (exist == null) {
 				throw new ApiError(meta.errors.notFollowing);
@@ -103,6 +102,8 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				withReplies: ps.withReplies != null ? ps.withReplies : undefined,
 			});
 
+			await this.cacheService.refreshFollowRelationsFor(follower.id);
+
 			return await this.userEntityService.pack(follower.id, me);
 		});
 	}
diff --git a/packages/backend/src/server/api/endpoints/i/update.ts b/packages/backend/src/server/api/endpoints/i/update.ts
index dda42ce0e4..e632915f62 100644
--- a/packages/backend/src/server/api/endpoints/i/update.ts
+++ b/packages/backend/src/server/api/endpoints/i/update.ts
@@ -617,7 +617,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 
 			const updatedProfile = await this.userProfilesRepository.findOneByOrFail({ userId: user.id });
 
-			this.cacheService.userProfileCache.set(user.id, updatedProfile);
+			await this.cacheService.userProfileCache.set(user.id, updatedProfile);
 
 			// Publish meUpdated event
 			this.globalEventService.publishMainStream(user.id, 'meUpdated', iObj);
diff --git a/packages/backend/src/server/api/endpoints/notes/create.ts b/packages/backend/src/server/api/endpoints/notes/create.ts
index 3dd90c3dca..461910543f 100644
--- a/packages/backend/src/server/api/endpoints/notes/create.ts
+++ b/packages/backend/src/server/api/endpoints/notes/create.ts
@@ -348,7 +348,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 					throw new ApiError(meta.errors.noSuchReplyTarget);
 				} else if (isRenote(reply) && !isQuote(reply)) {
 					throw new ApiError(meta.errors.cannotReplyToPureRenote);
-				} else if (!await this.noteEntityService.isVisibleForMe(reply, me.id)) {
+				} else if (!await this.noteEntityService.isVisibleForMe(reply, me.id, { me })) {
 					throw new ApiError(meta.errors.cannotReplyToInvisibleNote);
 				} else if (reply.visibility === 'specified' && ps.visibility !== 'specified') {
 					throw new ApiError(meta.errors.cannotReplyToSpecifiedVisibilityNoteWithExtendedVisibility);
diff --git a/packages/backend/src/server/api/endpoints/notes/edit.ts b/packages/backend/src/server/api/endpoints/notes/edit.ts
index 2c01b26584..bd70cb7835 100644
--- a/packages/backend/src/server/api/endpoints/notes/edit.ts
+++ b/packages/backend/src/server/api/endpoints/notes/edit.ts
@@ -402,7 +402,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 					throw new ApiError(meta.errors.noSuchReplyTarget);
 				} else if (isRenote(reply) && !isQuote(reply)) {
 					throw new ApiError(meta.errors.cannotReplyToPureRenote);
-				} else if (!await this.noteEntityService.isVisibleForMe(reply, me.id)) {
+				} else if (!await this.noteEntityService.isVisibleForMe(reply, me.id, { me })) {
 					throw new ApiError(meta.errors.cannotReplyToInvisibleNote);
 				} else if (reply.visibility === 'specified' && ps.visibility !== 'specified') {
 					throw new ApiError(meta.errors.cannotReplyToSpecifiedVisibilityNoteWithExtendedVisibility);
diff --git a/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts b/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts
index a7b104e198..a5623d1f03 100644
--- a/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts
+++ b/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts
@@ -164,7 +164,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				excludeBots: !ps.withBots,
 				noteFilter: note => {
 					if (note.reply && note.reply.visibility === 'followers') {
-						if (!Object.hasOwn(followings, note.reply.userId) && note.reply.userId !== me.id) return false;
+						if (!followings.has(note.reply.userId) && note.reply.userId !== me.id) return false;
 					}
 
 					return true;
diff --git a/packages/backend/src/server/api/endpoints/notes/timeline.ts b/packages/backend/src/server/api/endpoints/notes/timeline.ts
index 8cf7bb5795..44c539eaad 100644
--- a/packages/backend/src/server/api/endpoints/notes/timeline.ts
+++ b/packages/backend/src/server/api/endpoints/notes/timeline.ts
@@ -115,7 +115,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				excludePureRenotes: !ps.withRenotes,
 				noteFilter: note => {
 					if (note.reply && note.reply.visibility === 'followers') {
-						if (!Object.hasOwn(followings, note.reply.userId) && note.reply.userId !== me.id) return false;
+						if (!followings.has(note.reply.userId) && note.reply.userId !== me.id) return false;
 					}
 					if (!ps.withBots && note.user?.isBot) return false;
 
diff --git a/packages/backend/src/server/api/endpoints/notes/translate.ts b/packages/backend/src/server/api/endpoints/notes/translate.ts
index e55168e296..5ebd5ef362 100644
--- a/packages/backend/src/server/api/endpoints/notes/translate.ts
+++ b/packages/backend/src/server/api/endpoints/notes/translate.ts
@@ -91,7 +91,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				throw err;
 			});
 
-			if (!(await this.noteEntityService.isVisibleForMe(note, me?.id ?? null))) {
+			if (!(await this.noteEntityService.isVisibleForMe(note, me?.id ?? null, { me }))) {
 				throw new ApiError(meta.errors.cannotTranslateInvisibleNote);
 			}
 
diff --git a/packages/backend/src/server/api/endpoints/sw/register.ts b/packages/backend/src/server/api/endpoints/sw/register.ts
index f447b5598b..2f72e6ce1d 100644
--- a/packages/backend/src/server/api/endpoints/sw/register.ts
+++ b/packages/backend/src/server/api/endpoints/sw/register.ts
@@ -104,7 +104,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				sendReadMessage: ps.sendReadMessage,
 			});
 
-			this.pushNotificationService.refreshCache(me.id);
+			await this.pushNotificationService.refreshCache(me.id);
 
 			return {
 				state: 'subscribed' as const,
diff --git a/packages/backend/src/server/api/endpoints/sw/unregister.ts b/packages/backend/src/server/api/endpoints/sw/unregister.ts
index aa7e03dceb..f43a2cce28 100644
--- a/packages/backend/src/server/api/endpoints/sw/unregister.ts
+++ b/packages/backend/src/server/api/endpoints/sw/unregister.ts
@@ -46,7 +46,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			});
 
 			if (me) {
-				this.pushNotificationService.refreshCache(me.id);
+				await this.pushNotificationService.refreshCache(me.id);
 			}
 		});
 	}
diff --git a/packages/backend/src/server/api/endpoints/sw/update-registration.ts b/packages/backend/src/server/api/endpoints/sw/update-registration.ts
index 78b9323b7b..0cbed273e8 100644
--- a/packages/backend/src/server/api/endpoints/sw/update-registration.ts
+++ b/packages/backend/src/server/api/endpoints/sw/update-registration.ts
@@ -86,7 +86,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				sendReadMessage: swSubscription.sendReadMessage,
 			});
 
-			this.pushNotificationService.refreshCache(me.id);
+			await this.pushNotificationService.refreshCache(me.id);
 
 			return {
 				userId: swSubscription.userId,
diff --git a/packages/backend/src/server/api/endpoints/users/followers.ts b/packages/backend/src/server/api/endpoints/users/followers.ts
index c1617e14e5..82ce282bfc 100644
--- a/packages/backend/src/server/api/endpoints/users/followers.ts
+++ b/packages/backend/src/server/api/endpoints/users/followers.ts
@@ -12,6 +12,7 @@ import { FollowingEntityService } from '@/core/entities/FollowingEntityService.j
 import { UtilityService } from '@/core/UtilityService.js';
 import { DI } from '@/di-symbols.js';
 import { RoleService } from '@/core/RoleService.js';
+import { CacheService } from '@/core/CacheService.js';
 import { ApiError } from '../../error.js';
 
 export const meta = {
@@ -89,6 +90,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		private followingEntityService: FollowingEntityService,
 		private queryService: QueryService,
 		private roleService: RoleService,
+		private readonly cacheService: CacheService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
 			const user = await this.usersRepository.findOneBy(ps.userId != null
@@ -110,12 +112,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 					if (me == null) {
 						throw new ApiError(meta.errors.forbidden);
 					} else if (me.id !== user.id) {
-						const isFollowing = await this.followingsRepository.exists({
-							where: {
-								followeeId: user.id,
-								followerId: me.id,
-							},
-						});
+						const isFollowing = await this.cacheService.userFollowingsCache.fetch(me.id).then(f => f.has(user.id));
 						if (!isFollowing) {
 							throw new ApiError(meta.errors.forbidden);
 						}
diff --git a/packages/backend/src/server/api/endpoints/users/following.ts b/packages/backend/src/server/api/endpoints/users/following.ts
index c292c6d6a3..80f0b0c484 100644
--- a/packages/backend/src/server/api/endpoints/users/following.ts
+++ b/packages/backend/src/server/api/endpoints/users/following.ts
@@ -13,6 +13,7 @@ import { FollowingEntityService } from '@/core/entities/FollowingEntityService.j
 import { UtilityService } from '@/core/UtilityService.js';
 import { DI } from '@/di-symbols.js';
 import { RoleService } from '@/core/RoleService.js';
+import { CacheService } from '@/core/CacheService.js';
 import { ApiError } from '../../error.js';
 
 export const meta = {
@@ -98,6 +99,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		private followingEntityService: FollowingEntityService,
 		private queryService: QueryService,
 		private roleService: RoleService,
+		private readonly cacheService: CacheService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
 			const user = await this.usersRepository.findOneBy(ps.userId != null
@@ -119,12 +121,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 					if (me == null) {
 						throw new ApiError(meta.errors.forbidden);
 					} else if (me.id !== user.id) {
-						const isFollowing = await this.followingsRepository.exists({
-							where: {
-								followeeId: user.id,
-								followerId: me.id,
-							},
-						});
+						const isFollowing = await this.cacheService.userFollowingsCache.fetch(me.id).then(f => f.has(user.id));
 						if (!isFollowing) {
 							throw new ApiError(meta.errors.forbidden);
 						}
diff --git a/packages/backend/src/server/api/endpoints/users/notes.ts b/packages/backend/src/server/api/endpoints/users/notes.ts
index 66b50e0633..4602709067 100644
--- a/packages/backend/src/server/api/endpoints/users/notes.ts
+++ b/packages/backend/src/server/api/endpoints/users/notes.ts
@@ -134,7 +134,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			if (ps.withReplies) redisTimelines.push(`userTimelineWithReplies:${ps.userId}`);
 			if (ps.withChannelNotes) redisTimelines.push(`userTimelineWithChannel:${ps.userId}`);
 
-			const isFollowing = me && Object.hasOwn(await this.cacheService.userFollowingsCache.fetch(me.id), ps.userId);
+			const isFollowing = me && (await this.cacheService.userFollowingsCache.fetch(me.id)).has(ps.userId);
 
 			const timeline = await this.fanoutTimelineEndpointService.timeline({
 				untilId,
diff --git a/packages/backend/src/server/api/endpoints/users/recommendation.ts b/packages/backend/src/server/api/endpoints/users/recommendation.ts
index 642d788459..52dd2197b2 100644
--- a/packages/backend/src/server/api/endpoints/users/recommendation.ts
+++ b/packages/backend/src/server/api/endpoints/users/recommendation.ts
@@ -71,6 +71,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			this.queryService.generateBlockQueryForUsers(query, me);
 			this.queryService.generateBlockedUserQueryForNotes(query, me);
 
+			// TODO optimization: replace with exists()
 			const followingQuery = this.followingsRepository.createQueryBuilder('following')
 				.select('following.followeeId')
 				.where('following.followerId = :followerId', { followerId: me.id });