use index when checking visibleUserIds and mentions in generateVisibilityQuery

1 files changed, 30 insertions, 3 deletions

diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts
index bf5b0b359f..d488505afd 100644
--- a/packages/backend/src/core/QueryService.ts
+++ b/packages/backend/src/core/QueryService.ts
@@ -160,15 +160,15 @@ export class QueryService {
 					// Reply to me
 					.orWhere(':meId = note.replyUserId')
 					// DM to me
-					.orWhere(':meId = ANY (note.visibleUserIds)')
+					.orWhere(':meIdAsList <@ note.visibleUserIds')
 					// Mentions me
-					.orWhere(':meId = ANY (note.mentions)')
+					.orWhere(':meIdAsList <@ note.mentions')
 					// Followers-only post
 					.orWhere(new Brackets(qb => this
 						.andFollowingUser(qb, ':meId', 'note.userId')
 						.andWhere('note.visibility = \'followers\'')));
 
-				q.setParameters({ meId: me.id });
+				q.setParameters({ meId: me.id, meIdAsList: [me.id] });
 			}
 		}));
 	}
@@ -284,6 +284,33 @@ export class QueryService {
 	};
 
 	/**
+	 * Adds OR condition that followerProp (user ID) is following followeeProp (channel ID).
+	 * Both props should be expressions, not raw values.
+	 */
+	@bindThis
+	public orFollowingChannel<Q extends WhereExpressionBuilder>(q: Q, followerProp: string, followeeProp: string): Q {
+		return this.addFollowingChannel(q, followerProp, followeeProp, 'orWhere');
+	}
+
+	/**
+	 * Adds AND condition that followerProp (user ID) is following followeeProp (channel ID).
+	 * Both props should be expressions, not raw values.
+	 */
+	@bindThis
+	public andFollowingChannel<Q extends WhereExpressionBuilder>(q: Q, followerProp: string, followeeProp: string): Q {
+		return this.addFollowingChannel(q, followerProp, followeeProp, 'andWhere');
+	}
+
+	private addFollowingChannel<Q extends WhereExpressionBuilder>(q: Q, followerProp: string, followeeProp: string, join: 'andWhere' | 'orWhere'): Q {
+		const followingQuery = this.channelFollowingsRepository.createQueryBuilder('following')
+			.select('1')
+			.andWhere(`following.followerId = ${followerProp}`)
+			.andWhere(`following.followeeId = ${followeeProp}`);
+
+		return q[join](`EXISTS (${followingQuery.getQuery()})`, followingQuery.getParameters());
+	}
+
+	/**
 	 * Adds OR condition that blockerProp (user ID) is not blocking blockeeProp (user ID).
 	 * Both props should be expressions, not raw values.
 	 */