Implement tsvector search support

3 files changed, 63 insertions, 1 deletions

diff --git a/.config/example.yml b/.config/example.yml
index 07c613f62d..297f794324 100644
--- a/.config/example.yml
+++ b/.config/example.yml
@@ -222,6 +222,14 @@ fulltextSearch:
   #   You need to install pgroonga and configure it as a PostgreSQL extension.
   #   In addition to the above, you need to create a pgroonga index on the text column of the note table.
   #   see: https://pgroonga.github.io/tutorial/
+  # - tsvector
+  #   Use Postgres tsvectors.
+  #   You need to create a generated column and index on the note table to use this, followed by an ANALYZE on the table. Beware, this will take a while to be created and the database will remain locked during this process.
+  #   This also enables advanced search syntax, see documentation of websearch_to_tsquery: https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES
+  #   Support for non-English languages is currently rather poor and will be improved once post languages become a feature.
+  #   ALTER TABLE note ADD COLUMN tsvector_embedding tsvector GENERATED ALWAYS AS ( to_tsvector('english', COALESCE(text, '') || ' ' || COALESCE(cw, '') || ' ' || COALESCE(name, ''))) STORED;
+  #   CREATE INDEX vector_idx ON note USING GIN (tsvector_embedding);
+  #   ANALYZE note;
   # - meilisearch
   #   Use Meilisearch.
   #   You need to install Meilisearch and configure.
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;
@@ -257,6 +260,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,
 		me: MiUser | null,