refactor QueryService to use EXISTS instead of IN for most queries

1 files changed, 218 insertions, 152 deletions

diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts
index cf2419a9eb..548887959d 100644
--- a/packages/backend/src/core/QueryService.ts
+++ b/packages/backend/src/core/QueryService.ts
@@ -4,13 +4,13 @@
  */
 
 import { Inject, Injectable } from '@nestjs/common';
-import { Brackets, ObjectLiteral } from 'typeorm';
+import { Brackets, Not, WhereExpressionBuilder } from 'typeorm';
 import { DI } from '@/di-symbols.js';
 import type { MiUser } from '@/models/User.js';
-import type { UserProfilesRepository, FollowingsRepository, ChannelFollowingsRepository, BlockingsRepository, NoteThreadMutingsRepository, MutingsRepository, RenoteMutingsRepository, MiMeta } from '@/models/_.js';
+import type { UserProfilesRepository, FollowingsRepository, ChannelFollowingsRepository, BlockingsRepository, NoteThreadMutingsRepository, MutingsRepository, RenoteMutingsRepository, MiMeta, InstancesRepository, MiInstance } from '@/models/_.js';
 import { bindThis } from '@/decorators.js';
 import { IdService } from '@/core/IdService.js';
-import type { SelectQueryBuilder } from 'typeorm';
+import type { SelectQueryBuilder, FindOptionsWhere, ObjectLiteral } from 'typeorm';
 
 @Injectable()
 export class QueryService {
@@ -36,6 +36,9 @@ export class QueryService {
 		@Inject(DI.renoteMutingsRepository)
 		private renoteMutingsRepository: RenoteMutingsRepository,
 
+		@Inject(DI.instancesRepository)
+		private readonly instancesRepository: InstancesRepository,
+
 		@Inject(DI.meta)
 		private meta: MiMeta,
 
@@ -72,215 +75,278 @@ export class QueryService {
 
 	// ここでいうBlockedは被Blockedの意
 	@bindThis
-	public generateBlockedUserQueryForNotes(q: SelectQueryBuilder<any>, me: { id: MiUser['id'] }): void {
-		const blockingQuery = this.blockingsRepository.createQueryBuilder('blocking')
-			.select('blocking.blockerId')
-			.where('blocking.blockeeId = :blockeeId', { blockeeId: me.id });
-
+	public generateBlockedUserQueryForNotes<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, me: { id: MiUser['id'] }): SelectQueryBuilder<E> {
 		// 投稿の作者にブロックされていない かつ
 		// 投稿の返信先の作者にブロックされていない かつ
 		// 投稿の引用元の作者にブロックされていない
-		q
-			.andWhere(`note.userId NOT IN (${ blockingQuery.getQuery() })`)
+		return this.excludeBlockingUser(q, 'note.userId', ':meId')
 			.andWhere(new Brackets(qb => {
-				qb
-					.where('note.replyUserId IS NULL')
-					.orWhere(`note.replyUserId NOT IN (${ blockingQuery.getQuery() })`);
+				this.excludeBlockingUser(qb, 'note.replyUserId', ':meId')
+					.orWhere('note.replyUserId IS NULL');
 			}))
 			.andWhere(new Brackets(qb => {
-				qb
-					.where('note.renoteUserId IS NULL')
-					.orWhere(`note.renoteUserId NOT IN (${ blockingQuery.getQuery() })`);
-			}));
-
-		q.setParameters(blockingQuery.getParameters());
+				this.excludeBlockingUser(qb, 'note.renoteUserId', ':meId')
+					.orWhere('note.renoteUserId IS NULL');
+			}))
+			.setParameters({ meId: me.id });
 	}
 
 	@bindThis
-	public generateBlockQueryForUsers(q: SelectQueryBuilder<any>, me: { id: MiUser['id'] }): void {
-		const blockingQuery = this.blockingsRepository.createQueryBuilder('blocking')
-			.select('blocking.blockeeId')
-			.where('blocking.blockerId = :blockerId', { blockerId: me.id });
-
-		const blockedQuery = this.blockingsRepository.createQueryBuilder('blocking')
-			.select('blocking.blockerId')
-			.where('blocking.blockeeId = :blockeeId', { blockeeId: me.id });
-
-		q.andWhere(`user.id NOT IN (${ blockingQuery.getQuery() })`);
-		q.setParameters(blockingQuery.getParameters());
-
-		q.andWhere(`user.id NOT IN (${ blockedQuery.getQuery() })`);
-		q.setParameters(blockedQuery.getParameters());
+	public generateBlockQueryForUsers<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, me: { id: MiUser['id'] }): SelectQueryBuilder<E> {
+		this.excludeBlockingUser(q, ':meId', 'user.id');
+		this.excludeBlockingUser(q, 'user.id', ':me.id');
+		return q.setParameters({ meId: me.id });
 	}
 
 	@bindThis
-	public generateMutedNoteThreadQuery(q: SelectQueryBuilder<any>, me: { id: MiUser['id'] }): void {
-		const mutedQuery = this.noteThreadMutingsRepository.createQueryBuilder('threadMuted')
-			.select('threadMuted.threadId')
-			.where('threadMuted.userId = :userId', { userId: me.id });
-
-		q.andWhere(`note.id NOT IN (${ mutedQuery.getQuery() })`);
-		q.andWhere(new Brackets(qb => {
-			qb
-				.where('note.threadId IS NULL')
-				.orWhere(`note.threadId NOT IN (${ mutedQuery.getQuery() })`);
-		}));
-
-		q.setParameters(mutedQuery.getParameters());
+	public generateMutedNoteThreadQuery<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, me: { id: MiUser['id'] }): SelectQueryBuilder<E> {
+		return this.excludeMutingThread(q, ':meId', 'note.id')
+			.andWhere(new Brackets(qb => {
+				this.excludeMutingThread(qb, ':meId', 'note.threadId')
+					.orWhere('note.threadId IS NULL');
+			}))
+			.setParameters({ meId: me.id });
 	}
 
 	@bindThis
-	public generateMutedUserQueryForNotes(q: SelectQueryBuilder<any>, me: { id: MiUser['id'] }, exclude?: { id: MiUser['id'] }): void {
-		const mutingQuery = this.mutingsRepository.createQueryBuilder('muting')
-			.select('muting.muteeId')
-			.where('muting.muterId = :muterId', { muterId: me.id });
-
-		if (exclude) {
-			mutingQuery.andWhere('muting.muteeId != :excludeId', { excludeId: exclude.id });
-		}
-
-		const mutingInstanceQuery = this.userProfilesRepository.createQueryBuilder('user_profile')
-			.select('user_profile.mutedInstances')
-			.where('user_profile.userId = :muterId', { muterId: me.id });
-
+	public generateMutedUserQueryForNotes<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, me: { id: MiUser['id'] }, exclude?: { id: MiUser['id'] }): SelectQueryBuilder<E> {
 		// 投稿の作者をミュートしていない かつ
 		// 投稿の返信先の作者をミュートしていない かつ
 		// 投稿の引用元の作者をミュートしていない
-		q
-			.andWhere(`note.userId NOT IN (${ mutingQuery.getQuery() })`)
+		this.excludeMutingUser(q, ':meId', 'note.userId', exclude)
 			.andWhere(new Brackets(qb => {
-				qb
-					.where('note.replyUserId IS NULL')
-					.orWhere(`note.replyUserId NOT IN (${ mutingQuery.getQuery() })`);
-			}))
-			.andWhere(new Brackets(qb => {
-				qb
-					.where('note.renoteUserId IS NULL')
-					.orWhere(`note.renoteUserId NOT IN (${ mutingQuery.getQuery() })`);
+				this.excludeMutingUser(qb, ':meId', 'note.replyUserId', exclude)
+					.orWhere('note.replyUserId IS NULL');
 			}))
-			// mute instances
 			.andWhere(new Brackets(qb => {
-				qb
-					.andWhere('note.userHost IS NULL')
-					.orWhere(`NOT ((${ mutingInstanceQuery.getQuery() })::jsonb ? note.userHost)`);
-			}))
+				this.excludeMutingUser(qb, ':meId', 'note.renoteUserId', exclude)
+					.orWhere('note.renoteUserId IS NULL');
+			}));
+
+		// mute instances
+		this.excludeMutingInstance(q, ':meId', 'note.userHost')
 			.andWhere(new Brackets(qb => {
-				qb
-					.where('note.replyUserHost IS NULL')
-					.orWhere(`NOT ((${ mutingInstanceQuery.getQuery() })::jsonb ? note.replyUserHost)`);
+				this.excludeMutingInstance(qb, ':meId', 'note.replyUserHost')
+					.orWhere('note.replyUserHost IS NULL');
 			}))
 			.andWhere(new Brackets(qb => {
-				qb
-					.where('note.renoteUserHost IS NULL')
-					.orWhere(`NOT ((${ mutingInstanceQuery.getQuery() })::jsonb ? note.renoteUserHost)`);
+				this.excludeMutingInstance(qb, ':meId', 'note.renoteUserHost')
+					.orWhere('note.renoteUserHost IS NULL');
 			}));
 
-		q.setParameters(mutingQuery.getParameters());
-		q.setParameters(mutingInstanceQuery.getParameters());
+		return q.setParameters({ meId: me.id });
 	}
 
 	@bindThis
-	public generateMutedUserQueryForUsers(q: SelectQueryBuilder<any>, me: { id: MiUser['id'] }): void {
-		const mutingQuery = this.mutingsRepository.createQueryBuilder('muting')
-			.select('muting.muteeId')
-			.where('muting.muterId = :muterId', { muterId: me.id });
-
-		q.andWhere(`user.id NOT IN (${ mutingQuery.getQuery() })`);
-
-		q.setParameters(mutingQuery.getParameters());
+	public generateMutedUserQueryForUsers<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, me: { id: MiUser['id'] }): SelectQueryBuilder<E> {
+		return this.excludeMutingUser(q, ':meId', 'user.id')
+			.setParameters({ meId: me.id });
 	}
 
+	// This intentionally skips isSuspended, isDeleted, makeNotesFollowersOnlyBefore, makeNotesHiddenBefore, and requireSigninToViewContents.
+	// NoteEntityService checks these automatically and calls hideNote() to hide them without breaking threads.
+	// For moderation purposes, you can set isSilenced to forcibly hide existing posts by a user.
 	@bindThis
-	public generateVisibilityQuery(q: SelectQueryBuilder<any>, me?: { id: MiUser['id'] } | null): void {
+	public generateVisibilityQuery<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, me?: { id: MiUser['id'] } | null): SelectQueryBuilder<E> {
 		// This code must always be synchronized with the checks in Notes.isVisibleForMe.
-		if (me == null) {
-			q.andWhere(new Brackets(qb => {
-				qb
-					.where('note.visibility = \'public\'')
-					.orWhere('note.visibility = \'home\'');
-			}));
-		} else {
-			const followingQuery = this.followingsRepository.createQueryBuilder('following')
-				.select('following.followeeId')
-				.where('following.followerId = :meId');
+		return q.andWhere(new Brackets(qb => {
+			// Public post
+			qb.orWhere('note.visibility = \'public\'')
+				.orWhere('note.visibility = \'home\'');
 
-			q.andWhere(new Brackets(qb => {
+			if (me != null) {
 				qb
-				// 公開投稿である
-					.where(new Brackets(qb => {
-						qb
-							.where('note.visibility = \'public\'')
-							.orWhere('note.visibility = \'home\'');
-					}))
-				// または 自分自身
-					.orWhere('note.userId = :meId')
-				// または 自分宛て
-					.orWhere(':meIdAsList <@ note.visibleUserIds')
+					// My post
+					.orWhere(':meId = note.userId')
+					// Reply to me
+					.orWhere(':meId = note.replyUserId')
+					// DM to me
+					.orWhere(':meId = ANY (note.visibleUserIds)')
+					// Mentions me
+					.orWhere(':meId = ANY (note.mentions)')
+					// Followers-only post
 					.orWhere(new Brackets(qb => {
-						qb
 						// または フォロワー宛ての投稿であり、
-							.where('note.visibility = \'followers\'')
-							.andWhere(new Brackets(qb => {
-								qb
-								// 自分がフォロワーである
-									.where(`note.userId IN (${ followingQuery.getQuery() })`)
-								// または 自分の投稿へのリプライ
-									.orWhere('note.replyUserId = :meId')
-									.orWhere(':meIdAsList <@ note.mentions');
-							}));
+						this.addFollowingUser(qb, ':meId', 'note.userId')
+							.andWhere('note.visibility = \'followers\'');
 					}));
-			}));
 
-			q.setParameters({ meId: me.id, meIdAsList: [me.id] });
-		}
+				q.setParameters({ meId: me.id });
+			}
+		}));
 	}
 
 	@bindThis
-	public generateMutedUserRenotesQueryForNotes(q: SelectQueryBuilder<any>, me: { id: MiUser['id'] }): void {
-		const mutingQuery = this.renoteMutingsRepository.createQueryBuilder('renote_muting')
-			.select('renote_muting.muteeId')
-			.where('renote_muting.muterId = :muterId', { muterId: me.id });
-
-		q.andWhere(new Brackets(qb => {
-			qb
+	public generateMutedUserRenotesQueryForNotes<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, me: { id: MiUser['id'] }): SelectQueryBuilder<E> {
+		return q.andWhere(new Brackets(qb => {
+			this.excludeMutingRenote(qb, ':meId', 'note.userId')
 				.orWhere('note.renoteId IS NULL')
 				.orWhere('note.text IS NOT NULL')
 				.orWhere('note.cw IS NOT NULL')
 				.orWhere('note.replyId IS NOT NULL')
-				.orWhere('note.hasPoll = false')
-				.orWhere('note.fileIds != \'{}\'')
-				.orWhere(`note.userId NOT IN (${ mutingQuery.getQuery() })`);
-		}));
-
-		q.setParameters(mutingQuery.getParameters());
+				.orWhere('note.hasPoll = true')
+				.orWhere('note.fileIds != \'{}\'');
+		}))
+			.setParameters({ meId: me.id });
 	}
 
+	// TODO replace allowSilenced with matchingHostQuery
 	@bindThis
-	public generateBlockedHostQueryForNote(q: SelectQueryBuilder<any>, excludeAuthor?: boolean, allowSilenced = true): void {
-		function checkFor(key: 'user' | 'replyUser' | 'renoteUser') {
-			q.leftJoin(`note.${key}Instance`, `${key}Instance`);
+	public generateBlockedHostQueryForNote<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, excludeAuthor?: boolean, allowSilenced = true): SelectQueryBuilder<E> {
+		const checkFor = (key: 'user' | 'replyUser' | 'renoteUser') => {
 			q.andWhere(new Brackets(qb => {
-				qb.orWhere(`note.${key}Id IS NULL`) // no corresponding user
-					.orWhere(`note.${key}Host IS NULL`); // local
+				qb.orWhere(`note.${key}Host IS NULL`); // local
 
-				if (allowSilenced) {
-					qb.orWhere(`${key}Instance.isBlocked = false`); // not blocked
-				} else {
-					qb.orWhere(new Brackets(qbb => qbb
-						.andWhere(`${key}Instance.isBlocked = false`) // not blocked
-						.andWhere(`${key}Instance.isSilenced = false`))); // not silenced
+				if (key !== 'user') {
+					// note.userId always exists and is non-null
+					qb.orWhere(`note.${key}Id IS NULL`); // no corresponding user
+
+					// note.userId always equals note.userId
+					if (excludeAuthor) {
+						qb.orWhere(`note.userId = note.${key}Id`); // author
+					}
 				}
 
-				if (excludeAuthor) {
-					qb.orWhere(`note.userId = note.${key}Id`); // author
+				if (allowSilenced) {
+					// not blocked
+					this.excludeInstanceWhere(qb, `note.${key}Host`, {
+						isBlocked: false,
+					}, 'orWhere');
+				} else {
+					// not blocked or silenced
+					this.excludeInstanceWhere(qb, `note.${key}Host`, {
+						isBlocked: false,
+						isSilenced: false,
+					}, 'orWhere');
 				}
 			}));
-		}
+		};
 
 		if (!excludeAuthor) {
 			checkFor('user');
 		}
 		checkFor('replyUser');
 		checkFor('renoteUser');
+
+		return q;
+	}
+
+	@bindThis
+	public generateMatchingHostQueryForNote<E extends ObjectLiteral>(q: SelectQueryBuilder<E>, filters: FindOptionsWhere<MiInstance> | FindOptionsWhere<MiInstance>[], hostProp = 'note.userHost'): SelectQueryBuilder<E> {
+		return this.includeInstanceWhere(q, hostProp, filters);
+	}
+
+	/**
+	 * Adds condition that hostProp (instance host) matches the given filters.
+	 * The prop should be an expression, not raw values.
+	 */
+	@bindThis
+	public includeInstanceWhere<Q extends WhereExpressionBuilder>(q: Q, hostProp: string, filters: FindOptionsWhere<MiInstance> | FindOptionsWhere<MiInstance>[], join: 'andWhere' | 'orWhere' = 'andWhere'): Q {
+		const instancesQuery = this.instancesRepository.createQueryBuilder('instance')
+			.select('1')
+			.andWhere(`instance.host = ${hostProp}`)
+			.andWhere(filters);
+
+		return q[join](`EXISTS (${instancesQuery.getQuery()})`, instancesQuery.getParameters());
+	}
+
+	/**
+	 * Adds condition that hostProp (instance host) matches the given filters.
+	 * The prop should be an expression, not raw values.
+	 */
+	@bindThis
+	public excludeInstanceWhere<Q extends WhereExpressionBuilder>(q: Q, hostProp: string, filters: FindOptionsWhere<MiInstance> | FindOptionsWhere<MiInstance>[], join: 'andWhere' | 'orWhere' = 'andWhere'): Q {
+		const instancesQuery = this.instancesRepository.createQueryBuilder('instance')
+			.select('1')
+			.andWhere(`instance.host = ${hostProp}`)
+			.andWhere(filters);
+
+		return q[join](`NOT EXISTS (${instancesQuery.getQuery()})`, instancesQuery.getParameters());
+	}
+
+	/**
+	 * Adds condition that followerProp (user ID) is following followeeProp (user ID).
+	 * Both props should be expressions, not raw values.
+	 */
+	public addFollowingUser<Q extends WhereExpressionBuilder>(q: Q, followerProp: string, followeeProp: string): Q {
+		const followingQuery = this.followingsRepository.createQueryBuilder('following')
+			.select('1')
+			.andWhere(`following.followerId = ${followerProp}`)
+			.andWhere(`following.followeeId = ${followeeProp}`);
+
+		return q.andWhere(`EXISTS (${followingQuery.getQuery()})`, followingQuery.getParameters());
+	};
+
+	/**
+	 * Adds condition that blockerProp (user ID) is not blocking blockeeProp (user ID).
+	 * Both props should be expressions, not raw values.
+	 */
+	@bindThis
+	public excludeBlockingUser<Q extends WhereExpressionBuilder>(q: Q, blockerProp: string, blockeeProp: string): Q {
+		const blockingQuery = this.blockingsRepository.createQueryBuilder('blocking')
+			.select('1')
+			.andWhere(`blocking.blockerId = ${blockerProp}`)
+			.andWhere(`blocking.blockeeId = ${blockeeProp}`);
+
+		return q.andWhere(`NOT EXISTS (${blockingQuery.getQuery()})`, blockingQuery.getParameters());
+	};
+
+	/**
+	 * Adds condition that muterProp (user ID) is not muting muteeProp (user ID).
+	 * Both props should be expressions, not raw values.
+	 */
+	@bindThis
+	public excludeMutingUser<Q extends WhereExpressionBuilder>(q: Q, muterProp: string, muteeProp: string, exclude?: { id: MiUser['id'] }): Q {
+		const mutingQuery = this.mutingsRepository.createQueryBuilder('muting')
+			.select('1')
+			.andWhere(`muting.muterId = ${muterProp}`)
+			.andWhere(`muting.muteeId = ${muteeProp}`);
+
+		if (exclude) {
+			mutingQuery.andWhere({ muteeId: Not(exclude.id) });
+		}
+
+		return q.andWhere(`NOT EXISTS (${mutingQuery.getQuery()})`, mutingQuery.getParameters());
+	}
+
+	/**
+	 * Adds condition that muterProp (user ID) is not muting renotes by muteeProp (user ID).
+	 * Both props should be expressions, not raw values.
+	 */
+	public excludeMutingRenote<Q extends WhereExpressionBuilder>(q: Q, muterProp: string, muteeProp: string): Q {
+		const mutingQuery = this.renoteMutingsRepository.createQueryBuilder('renote_muting')
+			.select('1')
+			.andWhere(`renote_muting.muterId = ${muterProp}`)
+			.andWhere(`renote_muting.muteeId = ${muteeProp}`);
+
+		return q.andWhere(`NOT EXISTS (${mutingQuery.getQuery()})`, mutingQuery.getParameters());
+	};
+
+	/**
+	 * Adds condition that muterProp (user ID) is not muting muteeProp (instance host).
+	 * Both props should be expressions, not raw values.
+	 */
+	@bindThis
+	public excludeMutingInstance<Q extends WhereExpressionBuilder>(q: Q, muterProp: string, muteeProp: string): Q {
+		const mutingInstanceQuery = this.userProfilesRepository.createQueryBuilder('user_profile')
+			.select('1')
+			.andWhere(`user_profile.userId = ${muterProp}`)
+			.andWhere(`"user_profile"."mutedInstances"::jsonb ? ${muteeProp}`);
+
+		return q.andWhere(`NOT EXISTS (${mutingInstanceQuery.getQuery()})`, mutingInstanceQuery.getParameters());
+	}
+
+	/**
+	 * Adds condition that muterProp (user ID) is not muting muteeProp (note ID).
+	 * Both props should be expressions, not raw values.
+	 */
+	@bindThis
+	public excludeMutingThread<Q extends WhereExpressionBuilder>(q: Q, muterProp: string, muteeProp: string): Q {
+		const threadMutedQuery = this.noteThreadMutingsRepository.createQueryBuilder('threadMuted')
+			.select('1')
+			.andWhere(`threadMuted.userId = ${muterProp}`)
+			.andWhere(`threadMuted.threadId = ${muteeProp}`);
+
+		return q.andWhere(`NOT EXISTS (${threadMutedQuery.getQuery()})`, threadMutedQuery.getParameters());
 	}
 }