From f27e4033a6599203ba37efe01edbd988699b4068 Mon Sep 17 00:00:00 2001 From: syuilo Date: Sat, 20 Mar 2021 13:54:59 +0900 Subject: perf(server): Reduce database query Related: #6813 --- src/server/api/endpoints/antennas/notes.ts | 4 ++++ src/server/api/endpoints/channels/timeline.ts | 4 ++++ src/server/api/endpoints/clips/notes.ts | 4 ++++ src/server/api/endpoints/i/notifications.ts | 6 +++++- src/server/api/endpoints/notes.ts | 6 +++++- src/server/api/endpoints/notes/children.ts | 6 +++++- src/server/api/endpoints/notes/featured.ts | 6 +++++- src/server/api/endpoints/notes/global-timeline.ts | 6 +++++- src/server/api/endpoints/notes/hybrid-timeline.ts | 4 ++++ src/server/api/endpoints/notes/local-timeline.ts | 6 +++++- src/server/api/endpoints/notes/mentions.ts | 6 +++++- src/server/api/endpoints/notes/renotes.ts | 6 +++++- src/server/api/endpoints/notes/replies.ts | 6 +++++- src/server/api/endpoints/notes/search-by-tag.ts | 6 +++++- src/server/api/endpoints/notes/search.ts | 6 +++++- src/server/api/endpoints/notes/timeline.ts | 4 ++++ src/server/api/endpoints/notes/user-list-timeline.ts | 4 ++++ src/server/api/endpoints/users/notes.ts | 6 +++++- 18 files changed, 84 insertions(+), 12 deletions(-) (limited to 'src/server/api/endpoints') diff --git a/src/server/api/endpoints/antennas/notes.ts b/src/server/api/endpoints/antennas/notes.ts index 750fc080cf..2ea3e43745 100644 --- a/src/server/api/endpoints/antennas/notes.ts +++ b/src/server/api/endpoints/antennas/notes.ts @@ -74,6 +74,10 @@ export default define(meta, async (ps, user) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId) .andWhere(`note.id IN (${ antennaQuery.getQuery() })`) .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') .setParameters(antennaQuery.getParameters()); generateVisibilityQuery(query, user); diff --git a/src/server/api/endpoints/channels/timeline.ts b/src/server/api/endpoints/channels/timeline.ts index acb34f124d..292f21b63d 100644 --- a/src/server/api/endpoints/channels/timeline.ts +++ b/src/server/api/endpoints/channels/timeline.ts @@ -88,6 +88,10 @@ export default define(meta, async (ps, user) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId, ps.sinceDate, ps.untilDate) .andWhere('note.channelId = :channelId', { channelId: channel.id }) .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') .leftJoinAndSelect('note.channel', 'channel'); //#endregion diff --git a/src/server/api/endpoints/clips/notes.ts b/src/server/api/endpoints/clips/notes.ts index 6a507e2036..86d1e507f4 100644 --- a/src/server/api/endpoints/clips/notes.ts +++ b/src/server/api/endpoints/clips/notes.ts @@ -72,6 +72,10 @@ export default define(meta, async (ps, user) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId) .andWhere(`note.id IN (${ clipQuery.getQuery() })`) .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') .setParameters(clipQuery.getParameters()); if (user) { diff --git a/src/server/api/endpoints/i/notifications.ts b/src/server/api/endpoints/i/notifications.ts index af49549d37..812a4bd1dd 100644 --- a/src/server/api/endpoints/i/notifications.ts +++ b/src/server/api/endpoints/i/notifications.ts @@ -87,7 +87,11 @@ export default define(meta, async (ps, user) => { .andWhere(`notification.notifieeId = :meId`, { meId: user.id }) .leftJoinAndSelect('notification.notifier', 'notifier') .leftJoinAndSelect('notification.note', 'note') - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); query.andWhere(`notification.notifierId NOT IN (${ mutingQuery.getQuery() })`); query.setParameters(mutingQuery.getParameters()); diff --git a/src/server/api/endpoints/notes.ts b/src/server/api/endpoints/notes.ts index fab8455d78..708ce38c0e 100644 --- a/src/server/api/endpoints/notes.ts +++ b/src/server/api/endpoints/notes.ts @@ -76,7 +76,11 @@ export default define(meta, async (ps) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId) .andWhere(`note.visibility = 'public'`) .andWhere(`note.localOnly = FALSE`) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); if (ps.local) { query.andWhere('note.userHost IS NULL'); diff --git a/src/server/api/endpoints/notes/children.ts b/src/server/api/endpoints/notes/children.ts index 0875e0f240..a8b239e445 100644 --- a/src/server/api/endpoints/notes/children.ts +++ b/src/server/api/endpoints/notes/children.ts @@ -64,7 +64,11 @@ export default define(meta, async (ps, user) => { })); })); })) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateVisibilityQuery(query, user); if (user) generateMutedUserQuery(query, user); diff --git a/src/server/api/endpoints/notes/featured.ts b/src/server/api/endpoints/notes/featured.ts index 4dda7d0edb..6c416b1c04 100644 --- a/src/server/api/endpoints/notes/featured.ts +++ b/src/server/api/endpoints/notes/featured.ts @@ -49,7 +49,11 @@ export default define(meta, async (ps, user) => { .andWhere(`note.score > 0`) .andWhere(`note.createdAt > :date`, { date: new Date(Date.now() - day) }) .andWhere(`note.visibility = 'public'`) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); if (user) generateMutedUserQuery(query, user); diff --git a/src/server/api/endpoints/notes/global-timeline.ts b/src/server/api/endpoints/notes/global-timeline.ts index 6d99f1fdbc..64c2f5851c 100644 --- a/src/server/api/endpoints/notes/global-timeline.ts +++ b/src/server/api/endpoints/notes/global-timeline.ts @@ -81,7 +81,11 @@ export default define(meta, async (ps, user) => { ps.sinceId, ps.untilId, ps.sinceDate, ps.untilDate) .andWhere('note.visibility = \'public\'') .andWhere('note.channelId IS NULL') - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateRepliesQuery(query, user); if (user) generateMutedUserQuery(query, user); diff --git a/src/server/api/endpoints/notes/hybrid-timeline.ts b/src/server/api/endpoints/notes/hybrid-timeline.ts index 2b91b8c67b..c01e170aa9 100644 --- a/src/server/api/endpoints/notes/hybrid-timeline.ts +++ b/src/server/api/endpoints/notes/hybrid-timeline.ts @@ -130,6 +130,10 @@ export default define(meta, async (ps, user) => { .orWhere('(note.visibility = \'public\') AND (note.userHost IS NULL)'); })) .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') .setParameters(followingQuery.getParameters()); generateChannelQuery(query, user); diff --git a/src/server/api/endpoints/notes/local-timeline.ts b/src/server/api/endpoints/notes/local-timeline.ts index 51e35e6241..31abb26882 100644 --- a/src/server/api/endpoints/notes/local-timeline.ts +++ b/src/server/api/endpoints/notes/local-timeline.ts @@ -98,7 +98,11 @@ export default define(meta, async (ps, user) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId, ps.sinceDate, ps.untilDate) .andWhere('(note.visibility = \'public\') AND (note.userHost IS NULL)') - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateChannelQuery(query, user); generateRepliesQuery(query, user); diff --git a/src/server/api/endpoints/notes/mentions.ts b/src/server/api/endpoints/notes/mentions.ts index 1e3014bd46..83e890d9ad 100644 --- a/src/server/api/endpoints/notes/mentions.ts +++ b/src/server/api/endpoints/notes/mentions.ts @@ -63,7 +63,11 @@ export default define(meta, async (ps, user) => { .where(`:meId = ANY(note.mentions)`, { meId: user.id }) .orWhere(`:meId = ANY(note.visibleUserIds)`, { meId: user.id }); })) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateVisibilityQuery(query, user); generateMutedUserQuery(query, user); diff --git a/src/server/api/endpoints/notes/renotes.ts b/src/server/api/endpoints/notes/renotes.ts index 31c24f294a..f528197220 100644 --- a/src/server/api/endpoints/notes/renotes.ts +++ b/src/server/api/endpoints/notes/renotes.ts @@ -68,7 +68,11 @@ export default define(meta, async (ps, user) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId) .andWhere(`note.renoteId = :renoteId`, { renoteId: note.id }) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateVisibilityQuery(query, user); if (user) generateMutedUserQuery(query, user); diff --git a/src/server/api/endpoints/notes/replies.ts b/src/server/api/endpoints/notes/replies.ts index 9fad74c78e..5ac663d77a 100644 --- a/src/server/api/endpoints/notes/replies.ts +++ b/src/server/api/endpoints/notes/replies.ts @@ -59,7 +59,11 @@ export const meta = { export default define(meta, async (ps, user) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId) .andWhere('note.replyId = :replyId', { replyId: ps.noteId }) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateVisibilityQuery(query, user); if (user) generateMutedUserQuery(query, user); diff --git a/src/server/api/endpoints/notes/search-by-tag.ts b/src/server/api/endpoints/notes/search-by-tag.ts index e0f7f4d62c..d878f379c3 100644 --- a/src/server/api/endpoints/notes/search-by-tag.ts +++ b/src/server/api/endpoints/notes/search-by-tag.ts @@ -95,7 +95,11 @@ export const meta = { export default define(meta, async (ps, me) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateVisibilityQuery(query, me); if (me) generateMutedUserQuery(query, me); diff --git a/src/server/api/endpoints/notes/search.ts b/src/server/api/endpoints/notes/search.ts index 1aca056299..ae0e9242a6 100644 --- a/src/server/api/endpoints/notes/search.ts +++ b/src/server/api/endpoints/notes/search.ts @@ -79,7 +79,11 @@ export default define(meta, async (ps, me) => { query .andWhere('note.text ILIKE :q', { q: `%${ps.query}%` }) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateVisibilityQuery(query, me); if (me) generateMutedUserQuery(query, me); diff --git a/src/server/api/endpoints/notes/timeline.ts b/src/server/api/endpoints/notes/timeline.ts index f09f3d1733..c91bea0c1f 100644 --- a/src/server/api/endpoints/notes/timeline.ts +++ b/src/server/api/endpoints/notes/timeline.ts @@ -123,6 +123,10 @@ export default define(meta, async (ps, user) => { if (hasFollowing) qb.orWhere(`note.userId IN (${ followingQuery.getQuery() })`); })) .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') .setParameters(followingQuery.getParameters()); generateChannelQuery(query, user); diff --git a/src/server/api/endpoints/notes/user-list-timeline.ts b/src/server/api/endpoints/notes/user-list-timeline.ts index b0ff499d95..040f017fd5 100644 --- a/src/server/api/endpoints/notes/user-list-timeline.ts +++ b/src/server/api/endpoints/notes/user-list-timeline.ts @@ -131,6 +131,10 @@ export default define(meta, async (ps, user) => { const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId) .andWhere(`note.userId IN (${ listQuery.getQuery() })`) .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') .setParameters(listQuery.getParameters()); generateVisibilityQuery(query, user); diff --git a/src/server/api/endpoints/users/notes.ts b/src/server/api/endpoints/users/notes.ts index 33e3ecb03f..91d1d7f4dd 100644 --- a/src/server/api/endpoints/users/notes.ts +++ b/src/server/api/endpoints/users/notes.ts @@ -131,7 +131,11 @@ export default define(meta, async (ps, me) => { //#region Construct query const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId, ps.sinceDate, ps.untilDate) .andWhere('note.userId = :userId', { userId: user.id }) - .leftJoinAndSelect('note.user', 'user'); + .leftJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); generateVisibilityQuery(query, me); if (me) generateMutedUserQuery(query, me, user); -- cgit v1.2.3-freya