aboutsummaryrefslogtreecommitdiffhomepage
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
parenta3c1738eeaa3405d12c1c2f3102a24f3d15ebe3c (diff)
downloadPeerTube-dbfd3e9bfef86bf935a24c9a325d9f06e3978b8f.tar.gz
PeerTube-dbfd3e9bfef86bf935a24c9a325d9f06e3978b8f.tar.zst
PeerTube-dbfd3e9bfef86bf935a24c9a325d9f06e3978b8f.zip
Optimize search SQL query (I hope :p)
-rw-r--r--server/models/utils.ts14
-rw-r--r--server/models/video/video.ts32
2 files changed, 17 insertions, 29 deletions
diff --git a/server/models/utils.ts b/server/models/utils.ts
index 99e146583..58a18c97a 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -51,17 +51,6 @@ function createSimilarityAttribute (col: string, value: string) {
51 ) 51 )
52} 52}
53 53
54function createSearchTrigramQuery (col: string, value: string) {
55 return {
56 [ Sequelize.Op.or ]: [
57 // FIXME: use word_similarity instead of just similarity?
58 Sequelize.where(searchTrigramNormalizeCol(col), ' % ', searchTrigramNormalizeValue(value)),
59
60 Sequelize.where(searchTrigramNormalizeCol(col), ' LIKE ', searchTrigramNormalizeValue(`%${value}%`))
61 ]
62 }
63}
64
65// --------------------------------------------------------------------------- 54// ---------------------------------------------------------------------------
66 55
67export { 56export {
@@ -69,8 +58,7 @@ export {
69 getSortOnModel, 58 getSortOnModel,
70 createSimilarityAttribute, 59 createSimilarityAttribute,
71 throwIfNotValid, 60 throwIfNotValid,
72 buildTrigramSearchIndex, 61 buildTrigramSearchIndex
73 createSearchTrigramQuery
74} 62}
75 63
76// --------------------------------------------------------------------------- 64// ---------------------------------------------------------------------------
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