From d92aaf81c42dc91a915d38168996536d19d36cf8 Mon Sep 17 00:00:00 2001 From: YS <47836716+yszkst@users.noreply.github.com> Date: Mon, 15 Jan 2024 08:19:27 +0900 Subject: refactor: noteテーブルのインデックス整理と配列カラムへのクエリでインデックスを使うように (#12993) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit * Optimize note model index * enhance(backend): ANY()をやめる (MisskeyIO#239) * add small e2e test drive endpoint --------- Co-authored-by: まっちゃとーにゅ <17376330+u1-liquid@users.noreply.github.com> --- packages/backend/src/server/api/endpoints/notes/mentions.ts | 6 +++--- packages/backend/src/server/api/endpoints/notes/search-by-tag.ts | 4 ++-- 2 files changed, 5 insertions(+), 5 deletions(-) (limited to 'packages/backend/src/server/api/endpoints/notes') diff --git a/packages/backend/src/server/api/endpoints/notes/mentions.ts b/packages/backend/src/server/api/endpoints/notes/mentions.ts index 2317f8f7b2..323c6c946b 100644 --- a/packages/backend/src/server/api/endpoints/notes/mentions.ts +++ b/packages/backend/src/server/api/endpoints/notes/mentions.ts @@ -61,9 +61,9 @@ export default class extends Endpoint { // eslint- const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId) .andWhere(new Brackets(qb => { - qb - .where(`'{"${me.id}"}' <@ note.mentions`) - .orWhere(`'{"${me.id}"}' <@ note.visibleUserIds`); + qb // このmeIdAsListパラメータはqueryServiceのgenerateVisibilityQueryでセットされる + .where(':meIdAsList <@ note.mentions') + .orWhere(':meIdAsList <@ note.visibleUserIds'); })) // Avoid scanning primary key index .orderBy('CONCAT(note.id)', 'DESC') 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 b00f5207d8..0d7aca6621 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 @@ -87,14 +87,14 @@ export default class extends Endpoint { // eslint- try { if (ps.tag) { if (!safeForSql(normalizeForSearch(ps.tag))) throw new Error('Injection'); - query.andWhere(`'{"${normalizeForSearch(ps.tag)}"}' <@ note.tags`); + query.andWhere(':tag <@ note.tags', { tag: [normalizeForSearch(ps.tag)] }); } else { query.andWhere(new Brackets(qb => { for (const tags of ps.query!) { qb.orWhere(new Brackets(qb => { for (const tag of tags) { if (!safeForSql(normalizeForSearch(tag))) throw new Error('Injection'); - qb.andWhere(`'{"${normalizeForSearch(tag)}"}' <@ note.tags`); + qb.andWhere(':tag <@ note.tags', { tag: [normalizeForSearch(tag)] }); } })); } -- cgit v1.2.3-freya