perf(backend): cache avatar and banner url to db

13 files changed, 12 insertions, 84 deletions

diff --git a/packages/backend/src/server/api/endpoints/notes/children.ts b/packages/backend/src/server/api/endpoints/notes/children.ts
index ea7a825f9d..26f2d6772d 100644
--- a/packages/backend/src/server/api/endpoints/notes/children.ts
+++ b/packages/backend/src/server/api/endpoints/notes/children.ts
@@ -57,16 +57,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 					}));
 				}))
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			this.queryService.generateVisibilityQuery(query, me);
 			if (me) {
diff --git a/packages/backend/src/server/api/endpoints/notes/featured.ts b/packages/backend/src/server/api/endpoints/notes/featured.ts
index 6bf17b222a..bdb06498bc 100644
--- a/packages/backend/src/server/api/endpoints/notes/featured.ts
+++ b/packages/backend/src/server/api/endpoints/notes/featured.ts
@@ -53,16 +53,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 				.andWhere('note.createdAt > :date', { date: new Date(Date.now() - day) })
 				.andWhere('note.visibility = \'public\'')
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			if (ps.channelId) query.andWhere('note.channelId = :channelId', { channelId: ps.channelId });
 
diff --git a/packages/backend/src/server/api/endpoints/notes/global-timeline.ts b/packages/backend/src/server/api/endpoints/notes/global-timeline.ts
index 9118d33936..c11c1eac40 100644
--- a/packages/backend/src/server/api/endpoints/notes/global-timeline.ts
+++ b/packages/backend/src/server/api/endpoints/notes/global-timeline.ts
@@ -73,16 +73,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 				.andWhere('note.visibility = \'public\'')
 				.andWhere('note.channelId IS NULL')
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			this.queryService.generateRepliesQuery(query, me);
 			if (me) {
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 8a7ec65ab4..89abd91c7e 100644
--- a/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts
+++ b/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts
@@ -91,16 +91,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 						.orWhere('(note.visibility = \'public\') AND (note.userHost IS NULL)');
 				}))
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
 				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner')
 				.setParameters(followingQuery.getParameters());
 
 			this.queryService.generateChannelQuery(query, me);
diff --git a/packages/backend/src/server/api/endpoints/notes/local-timeline.ts b/packages/backend/src/server/api/endpoints/notes/local-timeline.ts
index 8c1c07a9f4..afdafc7c55 100644
--- a/packages/backend/src/server/api/endpoints/notes/local-timeline.ts
+++ b/packages/backend/src/server/api/endpoints/notes/local-timeline.ts
@@ -80,16 +80,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 				.andWhere('note.id > :minId', { minId: this.idService.genId(new Date(Date.now() - (1000 * 60 * 60 * 24 * 10))) }) // 10日前まで
 				.andWhere('(note.visibility = \'public\') AND (note.userHost IS NULL)')
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			this.queryService.generateChannelQuery(query, me);
 			this.queryService.generateRepliesQuery(query, me);
diff --git a/packages/backend/src/server/api/endpoints/notes/mentions.ts b/packages/backend/src/server/api/endpoints/notes/mentions.ts
index dcb0d0adcb..4e9f604d8d 100644
--- a/packages/backend/src/server/api/endpoints/notes/mentions.ts
+++ b/packages/backend/src/server/api/endpoints/notes/mentions.ts
@@ -60,16 +60,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 					.orWhere(`'{"${me.id}"}' <@ note.visibleUserIds`);
 				}))
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			this.queryService.generateVisibilityQuery(query, me);
 			this.queryService.generateMutedUserQuery(query, me);
diff --git a/packages/backend/src/server/api/endpoints/notes/reactions.ts b/packages/backend/src/server/api/endpoints/notes/reactions.ts
index f758bfe9b1..4772c4f809 100644
--- a/packages/backend/src/server/api/endpoints/notes/reactions.ts
+++ b/packages/backend/src/server/api/endpoints/notes/reactions.ts
@@ -75,7 +75,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 				order: {
 					id: -1,
 				},
-				relations: ['user', 'user.avatar', 'user.banner', 'note'],
+				relations: ['user', 'note'],
 			});
 
 			return await Promise.all(reactions.map(reaction => this.noteReactionEntityService.pack(reaction, me)));
diff --git a/packages/backend/src/server/api/endpoints/notes/renotes.ts b/packages/backend/src/server/api/endpoints/notes/renotes.ts
index 026a1baa3e..d406855660 100644
--- a/packages/backend/src/server/api/endpoints/notes/renotes.ts
+++ b/packages/backend/src/server/api/endpoints/notes/renotes.ts
@@ -4,8 +4,8 @@ import { Endpoint } from '@/server/api/endpoint-base.js';
 import { QueryService } from '@/core/QueryService.js';
 import { NoteEntityService } from '@/core/entities/NoteEntityService.js';
 import { DI } from '@/di-symbols.js';
-import { ApiError } from '../../error.js';
 import { GetterService } from '@/server/api/GetterService.js';
+import { ApiError } from '../../error.js';
 
 export const meta = {
 	tags: ['notes'],
@@ -62,16 +62,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId)
 				.andWhere('note.renoteId = :renoteId', { renoteId: note.id })
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			this.queryService.generateVisibilityQuery(query, me);
 			if (me) this.queryService.generateMutedUserQuery(query, me);
diff --git a/packages/backend/src/server/api/endpoints/notes/replies.ts b/packages/backend/src/server/api/endpoints/notes/replies.ts
index 4df95962c8..f2af71d55f 100644
--- a/packages/backend/src/server/api/endpoints/notes/replies.ts
+++ b/packages/backend/src/server/api/endpoints/notes/replies.ts
@@ -46,16 +46,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId)
 				.andWhere('note.replyId = :replyId', { replyId: ps.noteId })
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			this.queryService.generateVisibilityQuery(query, me);
 			if (me) this.queryService.generateMutedUserQuery(query, me);
diff --git a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
index da1a4bcc46..2956bf1cbd 100644
--- a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
+++ b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
@@ -71,16 +71,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 		super(meta, paramDef, async (ps, me) => {
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId)
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			this.queryService.generateVisibilityQuery(query, me);
 			if (me) this.queryService.generateMutedUserQuery(query, me);
diff --git a/packages/backend/src/server/api/endpoints/notes/search.ts b/packages/backend/src/server/api/endpoints/notes/search.ts
index 5db5b6267f..fb5abd917f 100644
--- a/packages/backend/src/server/api/endpoints/notes/search.ts
+++ b/packages/backend/src/server/api/endpoints/notes/search.ts
@@ -85,16 +85,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 			query
 				.andWhere('note.text ILIKE :q', { q: `%${ sqlLikeEscape(ps.query) }%` })
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			this.queryService.generateVisibilityQuery(query, me);
 			if (me) this.queryService.generateMutedUserQuery(query, me);
diff --git a/packages/backend/src/server/api/endpoints/notes/timeline.ts b/packages/backend/src/server/api/endpoints/notes/timeline.ts
index d9e72d2603..c6ee1e5c2b 100644
--- a/packages/backend/src/server/api/endpoints/notes/timeline.ts
+++ b/packages/backend/src/server/api/endpoints/notes/timeline.ts
@@ -70,16 +70,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 				ps.sinceId, ps.untilId, ps.sinceDate, ps.untilDate)
 				.andWhere('note.id > :minId', { minId: this.idService.genId(new Date(Date.now() - (1000 * 60 * 60 * 24 * 10))) }) // 10日前まで
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
-				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner');
+				.leftJoinAndSelect('renote.user', 'renoteUser');
 
 			if (followees.length > 0) {
 				const meOrFolloweeIds = [me.id, ...followees.map(f => f.followeeId)];
diff --git a/packages/backend/src/server/api/endpoints/notes/user-list-timeline.ts b/packages/backend/src/server/api/endpoints/notes/user-list-timeline.ts
index 9b23103fd4..afc9bc4213 100644
--- a/packages/backend/src/server/api/endpoints/notes/user-list-timeline.ts
+++ b/packages/backend/src/server/api/endpoints/notes/user-list-timeline.ts
@@ -84,16 +84,10 @@ export default class extends Endpoint<typeof meta, typeof paramDef> {
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId)
 				.innerJoin(this.userListJoiningsRepository.metadata.targetName, 'userListJoining', 'userListJoining.userId = note.userId')
 				.innerJoinAndSelect('note.user', 'user')
-				.leftJoinAndSelect('user.avatar', 'avatar')
-				.leftJoinAndSelect('user.banner', 'banner')
 				.leftJoinAndSelect('note.reply', 'reply')
 				.leftJoinAndSelect('note.renote', 'renote')
 				.leftJoinAndSelect('reply.user', 'replyUser')
-				.leftJoinAndSelect('replyUser.avatar', 'replyUserAvatar')
-				.leftJoinAndSelect('replyUser.banner', 'replyUserBanner')
 				.leftJoinAndSelect('renote.user', 'renoteUser')
-				.leftJoinAndSelect('renoteUser.avatar', 'renoteUserAvatar')
-				.leftJoinAndSelect('renoteUser.banner', 'renoteUserBanner')
 				.andWhere('userListJoining.userListId = :userListId', { userListId: list.id });
 
 			this.queryService.generateVisibilityQuery(query, me);