From d82c8e8e971d1e563dd93c6f8dea6edcba2337ae Mon Sep 17 00:00:00 2001
From: PrivateGER <privateger@privateger.me>
Date: Wed, 19 Feb 2025 14:55:50 +0100
Subject: Implement tsvector search support

---
 packages/backend/src/config.ts             |  2 +-
 packages/backend/src/core/SearchService.ts | 54 ++++++++++++++++++++++++++++++
 2 files changed, 55 insertions(+), 1 deletion(-)

(limited to 'packages')

diff --git a/packages/backend/src/config.ts b/packages/backend/src/config.ts
index 3c76c76469..938f44c024 100644
--- a/packages/backend/src/config.ts
+++ b/packages/backend/src/config.ts
@@ -254,7 +254,7 @@ export type Config = {
 	};
 };
 
-export type FulltextSearchProvider = 'sqlLike' | 'sqlPgroonga' | 'meilisearch';
+export type FulltextSearchProvider = 'sqlLike' | 'sqlPgroonga' | 'meilisearch' | 'tsvector';
 
 const _filename = fileURLToPath(import.meta.url);
 const _dirname = dirname(_filename);
diff --git a/packages/backend/src/core/SearchService.ts b/packages/backend/src/core/SearchService.ts
index 6e46fb798c..efef87cfb7 100644
--- a/packages/backend/src/core/SearchService.ts
+++ b/packages/backend/src/core/SearchService.ts
@@ -248,6 +248,9 @@ export class SearchService {
 			case 'meilisearch': {
 				return this.searchNoteByMeiliSearch(q, me, opts, pagination);
 			}
+			case 'tsvector': {
+				return this.searchNoteByTsvector(q, me, opts, pagination);
+			}
 			default: {
 				// eslint-disable-next-line @typescript-eslint/no-unused-vars
 				const typeCheck: never = this.provider;
@@ -256,6 +259,57 @@ export class SearchService {
 		}
 	}
 
+	@bindThis
+	private async searchNoteByTsvector(q: string,
+		me: MiUser | null,
+		opts: SearchOpts,
+		pagination: SearchPagination,
+	): Promise<MiNote[]> {
+		const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), pagination.sinceId, pagination.untilId);
+
+		if (opts.userId) {
+			query.andWhere('note.userId = :userId', { userId: opts.userId });
+		} else if (opts.channelId) {
+			query.andWhere('note.channelId = :channelId', { channelId: opts.channelId });
+		}
+
+		query
+			.innerJoinAndSelect('note.user', 'user')
+			.leftJoinAndSelect('note.reply', 'reply')
+			.leftJoinAndSelect('note.renote', 'renote')
+			.leftJoinAndSelect('reply.user', 'replyUser')
+			.leftJoinAndSelect('renote.user', 'renoteUser');
+
+		query.andWhere('note.tsvector @@ websearch_to_tsquery(:q)', { q });
+
+		if (opts.order === 'asc') {
+			query
+				.addSelect('ts_rank_cd(note.tsvector_embedding, websearch_to_tsquery(:q))', 'rank')
+				.orderBy('rank', 'DESC');
+		} else {
+			query
+				.orderBy('note.created_at', 'DESC');
+		}
+
+		if (opts.host) {
+			if (opts.host === '.') {
+				query.andWhere('note.userHost IS NULL');
+			} else {
+				query.andWhere('note.userHost = :host', { host: opts.host });
+			}
+		}
+
+		if (opts.filetype) {
+			query.andWhere('note."attachedFileTypes" && :types', { types: fileTypes[opts.filetype] });
+		}
+
+		this.queryService.generateVisibilityQuery(query, me);
+		if (me) this.queryService.generateMutedUserQuery(query, me);
+		if (me) this.queryService.generateBlockedUserQuery(query, me);
+
+		return await query.limit(pagination.limit).getMany();
+	}
+
 	@bindThis
 	private async searchNoteByLike(
 		q: string,
-- 
cgit v1.2.3-freya