add new note search file types (module, flash) and optimize file type query

3 files changed, 65 insertions, 51 deletions

diff --git a/packages/backend/src/core/SearchService.ts b/packages/backend/src/core/SearchService.ts
index 6dc3e85fc8..a8c6ac61f3 100644
--- a/packages/backend/src/core/SearchService.ts
+++ b/packages/backend/src/core/SearchService.ts
@@ -61,6 +61,60 @@ function compileQuery(q: Q): string {
 	}
 }
 
+const fileTypes = {
+	image: [
+		'image/webp',
+		'image/png',
+		'image/jpeg',
+		'image/avif',
+		'image/apng',
+		'image/gif',
+	],
+	video: [
+		'video/mp4',
+		'video/webm',
+		'video/mpeg',
+		'video/x-m4v',
+	],
+	audio: [
+		'audio/mpeg',
+		'audio/flac',
+		'audio/wav',
+		'audio/aac',
+		'audio/webm',
+		'audio/opus',
+		'audio/ogg',
+		'audio/x-m4a',
+		'audio/mod',
+		'audio/s3m',
+		'audio/xm',
+		'audio/it',
+		'audio/x-mod',
+		'audio/x-s3m',
+		'audio/x-xm',
+		'audio/x-it',
+	],
+	// Keep in sync with frontend-shared/js/const.ts
+	module: [
+		'audio/mod',
+		'audio/x-mod',
+		'audio/s3m',
+		'audio/x-s3m',
+		'audio/xm',
+		'audio/x-xm',
+		'audio/it',
+		'audio/x-it',
+	],
+	flash: [
+		'application/x-shockwave-flash',
+		'application/vnd.adobe.flash.movie',
+	],
+};
+
+// Make sure to regenerate misskey-js and check search.note.vue after changing these
+export const fileTypeCategories = ['image', 'video', 'audio', 'module', 'flash'] as const;
+export type FileTypeCategory = typeof fileTypeCategories[number];
+
 @Injectable()
 export class SearchService {
 	private readonly meilisearchIndexScope: 'local' | 'global' | string[] = 'local';
@@ -163,7 +217,7 @@ export class SearchService {
 		userId?: MiNote['userId'] | null;
 		channelId?: MiNote['channelId'] | null;
 		host?: string | null;
-		filetype?: string | null;
+		filetype?: FileTypeCategory | null;
 		order?: string | null;
 		disableMeili?: boolean | null;
 	}, pagination: {
@@ -188,42 +242,8 @@ export class SearchService {
 				}
 			}
 			if (opts.filetype) {
-				if (opts.filetype === 'image') {
-					filter.qs.push({ op: 'or', qs: [
-						{ op: '=', k: 'attachedFileTypes', v: 'image/webp' }, 
-						{ op: '=', k: 'attachedFileTypes', v: 'image/png' },
-						{ op: '=', k: 'attachedFileTypes', v: 'image/jpeg' },
-						{ op: '=', k: 'attachedFileTypes', v: 'image/avif' },
-						{ op: '=', k: 'attachedFileTypes', v: 'image/apng' },
-						{ op: '=', k: 'attachedFileTypes', v: 'image/gif' },
-					] });
-				} else if (opts.filetype === 'video') {
-					filter.qs.push({ op: 'or', qs: [
-						{ op: '=', k: 'attachedFileTypes', v: 'video/mp4' }, 
-						{ op: '=', k: 'attachedFileTypes', v: 'video/webm' },
-						{ op: '=', k: 'attachedFileTypes', v: 'video/mpeg' },
-						{ op: '=', k: 'attachedFileTypes', v: 'video/x-m4v' },
-					] });
-				} else if (opts.filetype === 'audio') {
-					filter.qs.push({ op: 'or', qs: [
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/mpeg' }, 
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/flac' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/wav' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/aac' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/webm' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/opus' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/ogg' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/x-m4a' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/mod' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/s3m' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/xm' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/it' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/x-mod' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/x-s3m' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/x-xm' },
-						{ op: '=', k: 'attachedFileTypes', v: 'audio/x-it' },
-					] });
-				}
+				const filters = fileTypes[opts.filetype].map(mime => ({ op: '=' as const, k: 'attachedFileTypes', v: mime }));
+				filter.qs.push({ op: 'or', qs: filters });
 			}
 			const res = await this.meilisearchNoteIndex!.search(q, {
 				sort: [`createdAt:${opts.order ? opts.order : 'desc'}`],
@@ -274,18 +294,7 @@ export class SearchService {
 			}
 
 			if (opts.filetype) {
-				/* this is very ugly, but the "correct" solution would
-				  be `and exists (select 1 from
-				  unnest(note."attachedFileTypes") x(t) where t like
-				  :type)` and I can't find a way to get TypeORM to
-				  generate that; this hack works because `~*` is
-				  "regexp match, ignoring case" and the stringified
-				  version of an array of varchars (which is what
-				  `attachedFileTypes` is) looks like `{foo,bar}`, so
-				  we're looking for opts.filetype as the first half of
-				  a MIME type, either at start of the array (after the
-				  `{`) or later (after a `,`) */
-				query.andWhere(`note."attachedFileTypes"::varchar ~* :type`, { type: `[{,]${opts.filetype}/` });
+				query.andWhere('note."attachedFileTypes" && :types', { types: fileTypes[opts.filetype] });
 			}
 
 			this.queryService.generateVisibilityQuery(query, me);
diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts
index 408e023ff7..8b5265e8fe 100644
--- a/packages/backend/src/models/Note.ts
+++ b/packages/backend/src/models/Note.ts
@@ -143,6 +143,7 @@ export class MiNote {
 	})
 	public fileIds: MiDriveFile['id'][];
 
+	@Index('IDX_NOTE_ATTACHED_FILE_TYPES', { synchronize: false })
 	@Column('varchar', {
 		length: 256, array: true, default: '{}',
 	})
diff --git a/packages/backend/src/server/api/endpoints/notes/search.ts b/packages/backend/src/server/api/endpoints/notes/search.ts
index eca55cd085..f46f4d2adb 100644
--- a/packages/backend/src/server/api/endpoints/notes/search.ts
+++ b/packages/backend/src/server/api/endpoints/notes/search.ts
@@ -5,7 +5,7 @@
 
 import { Injectable } from '@nestjs/common';
 import { Endpoint } from '@/server/api/endpoint-base.js';
-import { SearchService } from '@/core/SearchService.js';
+import { fileTypeCategories, SearchService } from '@/core/SearchService.js';
 import { NoteEntityService } from '@/core/entities/NoteEntityService.js';
 import { RoleService } from '@/core/RoleService.js';
 import { ApiError } from '../../error.js';
@@ -52,7 +52,11 @@ export const paramDef = {
 			type: 'string',
 			description: 'The local host is represented with `.`.',
 		},
-		filetype: { type: 'string', nullable: true },
+		filetype: {
+			type: 'string',
+			nullable: true,
+			enum: fileTypeCategories,
+		},
 		userId: { type: 'string', format: 'misskey:id', nullable: true, default: null },
 		channelId: { type: 'string', format: 'misskey:id', nullable: true, default: null },
 		order: { type: 'string' },