From dbfd3e9bfef86bf935a24c9a325d9f06e3978b8f Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Fri, 27 Jul 2018 15:20:10 +0200
Subject: Optimize search SQL query (I hope :p)
---
server/models/video/video.ts | 32 ++++++++++++++++----------------
1 file changed, 16 insertions(+), 16 deletions(-)
(limited to 'server/models/video')
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 33d62926b..464dbf597 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -83,7 +83,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate'
import { ActorModel } from '../activitypub/actor'
import { AvatarModel } from '../avatar/avatar'
import { ServerModel } from '../server/server'
-import { buildTrigramSearchIndex, createSearchTrigramQuery, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
+import { buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
import { TagModel } from './tag'
import { VideoAbuseModel } from './video-abuse'
import { VideoChannelModel } from './video-channel'
@@ -883,24 +883,24 @@ export class VideoModel extends Model<VideoModel> {
}
const attributesInclude = []
+ const escapedSearch = VideoModel.sequelize.escape(options.search)
+ const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
if (options.search) {
whereAnd.push(
{
- [ Sequelize.Op.or ]: [
- createSearchTrigramQuery('VideoModel.name', options.search),
-
- {
- id: {
- [ Sequelize.Op.in ]: Sequelize.literal(
- '(' +
- 'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
- 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
- 'WHERE "tag"."name" = ' + VideoModel.sequelize.escape(options.search) +
- ')'
- )
- }
- }
- ]
+ id: {
+ [ Sequelize.Op.in ]: Sequelize.literal(
+ '(' +
+ 'SELECT "video"."id" FROM "video" WHERE ' +
+ 'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
+ 'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
+ 'UNION ALL ' +
+ 'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
+ 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
+ 'WHERE "tag"."name" = ' + escapedSearch +
+ ')'
+ )
+ }
}
)
--
cgit v1.2.3