merge: prepare release 2025.4.3 (!1125)

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

Approved-by: Marie <github@yuugi.dev>
Approved-by: Julia <julia@insertdomain.name>

2 files changed, 23 insertions, 20 deletions

diff --git a/packages/backend/src/core/chart/charts/federation.ts b/packages/backend/src/core/chart/charts/federation.ts
index bf702884ca..4bbb5437cc 100644
--- a/packages/backend/src/core/chart/charts/federation.ts
+++ b/packages/backend/src/core/chart/charts/federation.ts
@@ -44,10 +44,7 @@ export default class FederationChart extends Chart<typeof schema> { // eslint-di
 	}
 
 	protected async tickMinor(): Promise<Partial<KVs<typeof schema>>> {
-		const suspendedInstancesQuery = this.instancesRepository.createQueryBuilder('instance')
-			.select('instance.host')
-			.where('instance.suspensionState != \'none\'');
-
+		// TODO optimization: replace these with exists()
 		const pubsubSubQuery = this.followingsRepository.createQueryBuilder('f')
 			.select('f.followerHost')
 			.where('f.followerHost IS NOT NULL');
@@ -64,22 +61,25 @@ export default class FederationChart extends Chart<typeof schema> { // eslint-di
 			this.followingsRepository.createQueryBuilder('following')
 				.select('COUNT(DISTINCT following.followeeHost)')
 				.where('following.followeeHost IS NOT NULL')
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followeeHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
-				.andWhere(`following.followeeHost NOT IN (${ suspendedInstancesQuery.getQuery() })`)
+				.innerJoin('following.followeeInstance', 'followeeInstance')
+				.andWhere('followeeInstance.suspensionState = \'none\'')
+				.andWhere('followeeInstance.isBlocked = false')
 				.getRawOne()
 				.then(x => parseInt(x.count, 10)),
 			this.followingsRepository.createQueryBuilder('following')
 				.select('COUNT(DISTINCT following.followerHost)')
 				.where('following.followerHost IS NOT NULL')
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followerHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
-				.andWhere(`following.followerHost NOT IN (${ suspendedInstancesQuery.getQuery() })`)
+				.innerJoin('following.followerInstance', 'followerInstance')
+				.andWhere('followerInstance.isBlocked = false')
+				.andWhere('followerInstance.suspensionState = \'none\'')
 				.getRawOne()
 				.then(x => parseInt(x.count, 10)),
 			this.followingsRepository.createQueryBuilder('following')
 				.select('COUNT(DISTINCT following.followeeHost)')
 				.where('following.followeeHost IS NOT NULL')
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followeeHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
-				.andWhere(`following.followeeHost NOT IN (${ suspendedInstancesQuery.getQuery() })`)
+				.innerJoin('following.followeeInstance', 'followeeInstance')
+				.andWhere('followeeInstance.isBlocked = false')
+				.andWhere('followeeInstance.suspensionState = \'none\'')
 				.andWhere(`following.followeeHost IN (${ pubsubSubQuery.getQuery() })`)
 				.setParameters(pubsubSubQuery.getParameters())
 				.getRawOne()
@@ -87,7 +87,7 @@ export default class FederationChart extends Chart<typeof schema> { // eslint-di
 			this.instancesRepository.createQueryBuilder('instance')
 				.select('COUNT(instance.id)')
 				.where(`instance.host IN (${ subInstancesQuery.getQuery() })`)
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || instance.host) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
+				.andWhere('instance.isBlocked = false')
 				.andWhere('instance.suspensionState = \'none\'')
 				.andWhere('instance.isNotResponding = false')
 				.getRawOne()
@@ -95,7 +95,7 @@ export default class FederationChart extends Chart<typeof schema> { // eslint-di
 			this.instancesRepository.createQueryBuilder('instance')
 				.select('COUNT(instance.id)')
 				.where(`instance.host IN (${ pubInstancesQuery.getQuery() })`)
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || instance.host) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
+				.andWhere('instance.isBlocked = false')
 				.andWhere('instance.suspensionState = \'none\'')
 				.andWhere('instance.isNotResponding = false')
 				.getRawOne()
diff --git a/packages/backend/src/core/chart/charts/per-user-following.ts b/packages/backend/src/core/chart/charts/per-user-following.ts
index 588ac638de..8d75a30e9a 100644
--- a/packages/backend/src/core/chart/charts/per-user-following.ts
+++ b/packages/backend/src/core/chart/charts/per-user-following.ts
@@ -15,6 +15,7 @@ import Chart from '../core.js';
 import { ChartLoggerService } from '../ChartLoggerService.js';
 import { name, schema } from './entities/per-user-following.js';
 import type { KVs } from '../core.js';
+import { CacheService } from '@/core/CacheService.js';
 
 /**
  * ユーザーごとのフォローに関するチャート
@@ -31,23 +32,25 @@ export default class PerUserFollowingChart extends Chart<typeof schema> { // esl
 		private appLockService: AppLockService,
 		private userEntityService: UserEntityService,
 		private chartLoggerService: ChartLoggerService,
+		private readonly cacheService: CacheService,
 	) {
 		super(db, (k) => appLockService.getChartInsertLock(k), chartLoggerService.logger, name, schema, true);
 	}
 
 	protected async tickMajor(group: string): Promise<Partial<KVs<typeof schema>>> {
 		const [
-			localFollowingsCount,
-			localFollowersCount,
-			remoteFollowingsCount,
-			remoteFollowersCount,
+			followees,
+			followers,
 		] = await Promise.all([
-			this.followingsRepository.countBy({ followerId: group, followeeHost: IsNull() }),
-			this.followingsRepository.countBy({ followeeId: group, followerHost: IsNull() }),
-			this.followingsRepository.countBy({ followerId: group, followeeHost: Not(IsNull()) }),
-			this.followingsRepository.countBy({ followeeId: group, followerHost: Not(IsNull()) }),
+			this.cacheService.userFollowingsCache.fetch(group).then(fs => Array.from(fs.values())),
+			this.cacheService.userFollowersCache.fetch(group).then(fs => Array.from(fs.values())),
 		]);
 
+		const localFollowingsCount = followees.reduce((sum, f) => sum + (f.followeeHost == null ? 1 : 0), 0);
+		const localFollowersCount = followers.reduce((sum, f) => sum + (f.followerHost == null ? 1 : 0), 0);
+		const remoteFollowingsCount = followees.reduce((sum, f) => sum + (f.followeeHost == null ? 0 : 1), 0);
+		const remoteFollowersCount = followers.reduce((sum, f) => sum + (f.followerHost == null ? 0 : 1), 0);
+
 		return {
 			'local.followings.total': localFollowingsCount,
 			'local.followers.total': localFollowersCount,