aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/video-query-builder.ts
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2020-03-05 15:04:57 +0100
committerChocobozzz <me@florianbigard.com>2020-03-10 14:03:58 +0100
commit5f3e2425f1c64d93860a0c3341de9b361b3c1f1f (patch)
treea95a64e865ac047b403a6c40ff4161ac0c07ba7c /server/models/video/video-query-builder.ts
parentf8cce49c3f36e03edd93ce141b93c49c7d6bfe58 (diff)
downloadPeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.tar.gz
PeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.tar.zst
PeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.zip
Rewrite video list in raw SQL
Diffstat (limited to 'server/models/video/video-query-builder.ts')
-rw-r--r--server/models/video/video-query-builder.ts359
1 files changed, 359 insertions, 0 deletions
diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts
new file mode 100644
index 000000000..c4b31e58e
--- /dev/null
+++ b/server/models/video/video-query-builder.ts
@@ -0,0 +1,359 @@
1import { VideoFilter, VideoPrivacy, VideoState } from '@shared/models'
2import { buildDirectionAndField, createSafeIn } from '@server/models/utils'
3import { Model } from 'sequelize-typescript'
4import { MUserAccountId, MUserId } from '@server/typings/models'
5import validator from 'validator'
6
7export type BuildVideosQueryOptions = {
8 attributes?: string[]
9
10 serverAccountId: number
11 followerActorId: number
12 includeLocalVideos: boolean
13
14 count: number
15 start: number
16 sort: string
17
18 filter?: VideoFilter
19 categoryOneOf?: number[]
20 nsfw?: boolean
21 licenceOneOf?: number[]
22 languageOneOf?: string[]
23 tagsOneOf?: string[]
24 tagsAllOf?: string[]
25
26 withFiles?: boolean
27
28 accountId?: number
29 videoChannelId?: number
30
31 videoPlaylistId?: number
32
33 trendingDays?: number
34 user?: MUserAccountId
35 historyOfUser?: MUserId
36
37 startDate?: string // ISO 8601
38 endDate?: string // ISO 8601
39 originallyPublishedStartDate?: string
40 originallyPublishedEndDate?: string
41
42 durationMin?: number // seconds
43 durationMax?: number // seconds
44
45 search?: string
46
47 isCount?: boolean
48
49 group?: string
50 having?: string
51}
52
53function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) {
54 const and: string[] = []
55 const cte: string[] = []
56 const joins: string[] = []
57 const replacements: any = {}
58
59 let attributes: string[] = options.attributes || [ '"video"."id"' ]
60 let group = options.group || ''
61 const having = options.having || ''
62
63 joins.push(
64 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"' +
65 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"' +
66 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id"'
67 )
68
69 and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")')
70
71 if (options.serverAccountId) {
72 const blockerIds = [ options.serverAccountId ]
73 if (options.user) blockerIds.push(options.user.Account.id)
74
75 cte.push(
76 '"mutedAccount" AS (' +
77 ' SELECT "targetAccountId" AS "id"' +
78 ' FROM "accountBlocklist"' +
79 ' WHERE "accountId" IN (' + createSafeIn(model, blockerIds) + ')' +
80 ' UNION ALL' +
81 ' SELECT "account"."id" AS "id"' +
82 ' FROM account' +
83 ' INNER JOIN "actor" ON account."actorId" = actor.id' +
84 ' INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId"' +
85 ' WHERE "serverBlocklist"."accountId" IN (' + createSafeIn(model, blockerIds) + ')' +
86 ')'
87 )
88
89 cte.push(
90 '"mutedChannel" AS (' +
91 ' SELECT "videoChannel"."id"' +
92 ' FROM "videoChannel"' +
93 ' INNER JOIN "mutedAccount" ON "mutedAccount"."id" = "videoChannel"."accountId"' +
94 ' )'
95 )
96
97 and.push(
98 '"video"."channelId" NOT IN (SELECT "id" FROM "mutedChannel")'
99 )
100
101 replacements.videoChannelId = options.videoChannelId
102 }
103
104 // Only list public/published videos
105 if (!options.filter || options.filter !== 'all-local') {
106 and.push(
107 `("video"."state" = ${VideoState.PUBLISHED} OR ` +
108 `("video"."state" = ${VideoState.TO_TRANSCODE} AND "video"."waitTranscoding" IS false))`
109 )
110
111 if (options.user) {
112 and.push(
113 `("video"."privacy" = ${VideoPrivacy.PUBLIC} OR "video"."privacy" = ${VideoPrivacy.INTERNAL})`
114 )
115 } else { // Or only public videos
116 and.push(
117 `"video"."privacy" = ${VideoPrivacy.PUBLIC}`
118 )
119 }
120 }
121
122 if (options.videoPlaylistId) {
123 joins.push(
124 'INNER JOIN "videoPlaylistElement" "video"."id" = "videoPlaylistElement"."videoId" ' +
125 'AND "videoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
126 )
127
128 replacements.videoPlaylistId = options.videoPlaylistId
129 }
130
131 if (options.filter && (options.filter === 'local' || options.filter === 'all-local')) {
132 and.push('"video"."remote" IS FALSE')
133 }
134
135 if (options.accountId) {
136 and.push('"account"."id" = :accountId')
137 replacements.accountId = options.accountId
138 }
139
140 if (options.videoChannelId) {
141 and.push('"videoChannel"."id" = :videoChannelId')
142 replacements.videoChannelId = options.videoChannelId
143 }
144
145 if (options.followerActorId) {
146 let query =
147 '(' +
148 ' EXISTS (' +
149 ' SELECT 1 FROM "videoShare" ' +
150 ' INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId" ' +
151 ' AND "actorFollowShare"."actorId" = :followerActorId WHERE "videoShare"."videoId" = "video"."id"' +
152 ' )' +
153 ' OR' +
154 ' EXISTS (' +
155 ' SELECT 1 from "actorFollow" ' +
156 ' WHERE "actorFollow"."targetActorId" = "actor"."id" AND "actorFollow"."actorId" = :followerActorId' +
157 ' )'
158
159 if (options.includeLocalVideos) {
160 query += ' OR "video"."remote" IS FALSE'
161 }
162
163 query += ')'
164
165 and.push(query)
166 replacements.followerActorId = options.followerActorId
167 }
168
169 if (options.withFiles === true) {
170 and.push('EXISTS (SELECT 1 FROM "videoFile" WHERE "videoFile"."videoId" = "video"."id")')
171 }
172
173 if (options.tagsOneOf) {
174 const tagsOneOfLower = options.tagsOneOf.map(t => t.toLowerCase())
175
176 and.push(
177 'EXISTS (' +
178 ' SELECT 1 FROM "videoTag" ' +
179 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
180 ' WHERE lower("tag"."name") IN (' + createSafeIn(model, tagsOneOfLower) + ') ' +
181 ' AND "video"."id" = "videoTag"."videoId"' +
182 ')'
183 )
184 }
185
186 if (options.tagsAllOf) {
187 const tagsAllOfLower = options.tagsAllOf.map(t => t.toLowerCase())
188
189 and.push(
190 'EXISTS (' +
191 ' SELECT 1 FROM "videoTag" ' +
192 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
193 ' WHERE lower("tag"."name") IN (' + createSafeIn(model, tagsAllOfLower) + ') ' +
194 ' AND "video"."id" = "videoTag"."videoId" ' +
195 ' GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
196 ')'
197 )
198 }
199
200 if (options.nsfw === true) {
201 and.push('"video"."nsfw" IS TRUE')
202 }
203
204 if (options.nsfw === false) {
205 and.push('"video"."nsfw" IS FALSE')
206 }
207
208 if (options.categoryOneOf) {
209 and.push('"video"."category" IN (:categoryOneOf)')
210 replacements.categoryOneOf = options.categoryOneOf
211 }
212
213 if (options.licenceOneOf) {
214 and.push('"video"."licence" IN (:licenceOneOf)')
215 replacements.licenceOneOf = options.licenceOneOf
216 }
217
218 if (options.languageOneOf) {
219 replacements.languageOneOf = options.languageOneOf.filter(l => l && l !== '_unknown')
220
221 let languagesQuery = '("video"."language" IN (:languageOneOf) OR '
222
223 if (options.languageOneOf.includes('_unknown')) {
224 languagesQuery += '"video"."language" IS NULL OR '
225 }
226
227 and.push(
228 languagesQuery +
229 ' EXISTS (' +
230 ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' +
231 ' IN (' + createSafeIn(model, options.languageOneOf) + ') AND ' +
232 ' "videoCaption"."videoId" = "video"."id"' +
233 ' )' +
234 ')'
235 )
236 }
237
238 // We don't exclude results in this if so if we do a count we don't need to add this complex clauses
239 if (options.trendingDays && options.isCount !== true) {
240 const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays)
241
242 joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate')
243 replacements.viewsGteDate = viewsGteDate
244
245 group = 'GROUP BY "video"."id"'
246 }
247
248 if (options.historyOfUser) {
249 joins.push('INNER JOIN "userVideoHistory" on "video"."id" = "userVideoHistory"."videoId"')
250
251 and.push('"userVideoHistory"."userId" = :historyOfUser')
252 replacements.historyOfUser = options.historyOfUser
253 }
254
255 if (options.startDate) {
256 and.push('"video"."publishedAt" >= :startDate')
257 replacements.startDate = options.startDate
258 }
259
260 if (options.endDate) {
261 and.push('"video"."publishedAt" <= :endDate')
262 replacements.endDate = options.endDate
263 }
264
265 if (options.originallyPublishedStartDate) {
266 and.push('"video"."originallyPublishedAt" >= :originallyPublishedStartDate')
267 replacements.originallyPublishedStartDate = options.originallyPublishedStartDate
268 }
269
270 if (options.originallyPublishedEndDate) {
271 and.push('"video"."originallyPublishedAt" <= :originallyPublishedEndDate')
272 replacements.originallyPublishedEndDate = options.originallyPublishedEndDate
273 }
274
275 if (options.durationMin) {
276 and.push('"video"."duration" >= :durationMin')
277 replacements.durationMin = options.durationMin
278 }
279
280 if (options.durationMax) {
281 and.push('"video"."duration" <= :durationMax')
282 replacements.durationMax = options.durationMax
283 }
284
285 if (options.search) {
286 const escapedSearch = model.sequelize.escape(options.search)
287 const escapedLikeSearch = model.sequelize.escape('%' + options.search + '%')
288
289 let base = '(' +
290 ' lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
291 ' lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + ')) OR ' +
292 ' EXISTS (' +
293 ' SELECT 1 FROM "videoTag" ' +
294 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
295 ` WHERE lower("tag"."name") = ${escapedSearch} ` +
296 ' AND "video"."id" = "videoTag"."videoId"' +
297 ' )'
298
299 if (validator.isUUID(options.search)) {
300 base += ` OR "video"."uuid" = ${escapedSearch}`
301 }
302
303 base += ')'
304 and.push(base)
305
306 attributes.push(`similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity`)
307 } else {
308 attributes.push('0 as similarity')
309 }
310
311 if (options.isCount === true) attributes = [ 'COUNT(*) as "total"' ]
312
313 const cteString = cte.length !== 0
314 ? 'WITH ' + cte.join(', ') + ' '
315 : ''
316
317 let query = cteString +
318 'SELECT ' + attributes.join(', ') + ' ' +
319 'FROM "video" ' + joins.join(' ') + ' ' +
320 'WHERE ' + and.join(' AND ') + ' ' +
321 group + ' ' +
322 having + ' '
323
324 if (options.isCount !== true) {
325 const count = parseInt(options.count + '', 10)
326 const start = parseInt(options.start + '', 10)
327
328 query += buildOrder(model, options.sort) + ' ' +
329 'LIMIT ' + count + ' ' +
330 'OFFSET ' + start
331 }
332
333 return { query, replacements }
334}
335
336function buildOrder (model: typeof Model, value: string) {
337 const { direction, field } = buildDirectionAndField(value)
338 if (field.match(/^[a-zA-Z]+$/) === null) throw new Error('Invalid sort column ' + field)
339
340 if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()'
341
342 if (field.toLowerCase() === 'trending') { // Sort by aggregation
343 return `ORDER BY COALESCE(SUM("videoView"."views"), 0) ${direction}, "video"."views" ${direction}`
344 }
345
346 let firstSort: string
347
348 if (field.toLowerCase() === 'match') { // Search
349 firstSort = '"similarity"'
350 } else {
351 firstSort = `"video"."${field}"`
352 }
353
354 return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC`
355}
356
357export {
358 buildListQuery
359}