aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2018-07-27 15:20:10 +0200
committerChocobozzz <me@florianbigard.com>2018-07-27 15:23:05 +0200
commitdbfd3e9bfef86bf935a24c9a325d9f06e3978b8f (patch)
treef1a7dfa26638b6cad649d12b6aaf0aa027bf0c3c /server/models/video
parenta3c1738eeaa3405d12c1c2f3102a24f3d15ebe3c (diff)
downloadPeerTube-dbfd3e9bfef86bf935a24c9a325d9f06e3978b8f.tar.gz
PeerTube-dbfd3e9bfef86bf935a24c9a325d9f06e3978b8f.tar.zst
PeerTube-dbfd3e9bfef86bf935a24c9a325d9f06e3978b8f.zip
Optimize search SQL query (I hope :p)
Diffstat (limited to 'server/models/video')
-rw-r--r--server/models/video/video.ts32
1 files changed, 16 insertions, 16 deletions
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'
83import { ActorModel } from '../activitypub/actor' 83import { ActorModel } from '../activitypub/actor'
84import { AvatarModel } from '../avatar/avatar' 84import { AvatarModel } from '../avatar/avatar'
85import { ServerModel } from '../server/server' 85import { ServerModel } from '../server/server'
86import { buildTrigramSearchIndex, createSearchTrigramQuery, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils' 86import { buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
87import { TagModel } from './tag' 87import { TagModel } from './tag'
88import { VideoAbuseModel } from './video-abuse' 88import { VideoAbuseModel } from './video-abuse'
89import { VideoChannelModel } from './video-channel' 89import { VideoChannelModel } from './video-channel'
@@ -883,24 +883,24 @@ export class VideoModel extends Model<VideoModel> {
883 } 883 }
884 884
885 const attributesInclude = [] 885 const attributesInclude = []
886 const escapedSearch = VideoModel.sequelize.escape(options.search)
887 const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
886 if (options.search) { 888 if (options.search) {
887 whereAnd.push( 889 whereAnd.push(
888 { 890 {
889 [ Sequelize.Op.or ]: [ 891 id: {
890 createSearchTrigramQuery('VideoModel.name', options.search), 892 [ Sequelize.Op.in ]: Sequelize.literal(
891 893 '(' +
892 { 894 'SELECT "video"."id" FROM "video" WHERE ' +
893 id: { 895 'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
894 [ Sequelize.Op.in ]: Sequelize.literal( 896 'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
895 '(' + 897 'UNION ALL ' +
896 'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' + 898 'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
897 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + 899 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
898 'WHERE "tag"."name" = ' + VideoModel.sequelize.escape(options.search) + 900 'WHERE "tag"."name" = ' + escapedSearch +
899 ')' 901 ')'
900 ) 902 )
901 } 903 }
902 }
903 ]
904 } 904 }
905 ) 905 )
906 906