]>
Commit | Line | Data |
---|---|---|
2ec349aa | 1 | import { Sequelize, Transaction } from 'sequelize' |
e5dbd508 C |
2 | import validator from 'validator' |
3 | import { exists } from '@server/helpers/custom-validators/misc' | |
29837f88 | 4 | import { WEBSERVER } from '@server/initializers/constants' |
8c4bbd94 | 5 | import { buildSortDirectionAndField } from '@server/models/shared' |
e5dbd508 | 6 | import { MUserAccountId, MUserId } from '@server/types/models' |
8c4bbd94 | 7 | import { forceNumber } from '@shared/core-utils' |
2760b454 | 8 | import { VideoInclude, VideoPrivacy, VideoState } from '@shared/models' |
8c4bbd94 | 9 | import { createSafeIn, parseRowCountResult } from '../../../shared' |
156c44c8 | 10 | import { AbstractRunQuery } from '../../../shared/abstract-run-query' |
e5dbd508 | 11 | |
1d43c3a6 C |
12 | /** |
13 | * | |
14 | * Build videos list SQL query to fetch rows | |
15 | * | |
16 | */ | |
17 | ||
2760b454 C |
18 | export type DisplayOnlyForFollowerOptions = { |
19 | actorId: number | |
20 | orLocalVideos: boolean | |
21 | } | |
22 | ||
e5dbd508 C |
23 | export type BuildVideosListQueryOptions = { |
24 | attributes?: string[] | |
25 | ||
2760b454 C |
26 | serverAccountIdForBlock: number |
27 | ||
28 | displayOnlyForFollower: DisplayOnlyForFollowerOptions | |
e5dbd508 C |
29 | |
30 | count: number | |
31 | start: number | |
32 | sort: string | |
33 | ||
34 | nsfw?: boolean | |
29837f88 | 35 | host?: string |
e5dbd508 | 36 | isLive?: boolean |
2760b454 C |
37 | isLocal?: boolean |
38 | include?: VideoInclude | |
e5dbd508 C |
39 | |
40 | categoryOneOf?: number[] | |
41 | licenceOneOf?: number[] | |
42 | languageOneOf?: string[] | |
43 | tagsOneOf?: string[] | |
44 | tagsAllOf?: string[] | |
527a52ac | 45 | privacyOneOf?: VideoPrivacy[] |
e5dbd508 | 46 | |
fbd67e7f C |
47 | uuids?: string[] |
48 | ||
3c10840f | 49 | hasFiles?: boolean |
d324756e C |
50 | hasHLSFiles?: boolean |
51 | hasWebtorrentFiles?: boolean | |
e5dbd508 C |
52 | |
53 | accountId?: number | |
54 | videoChannelId?: number | |
55 | ||
56 | videoPlaylistId?: number | |
57 | ||
58 | trendingAlgorithm?: string // best, hot, or any other algorithm implemented | |
59 | trendingDays?: number | |
60 | ||
61 | user?: MUserAccountId | |
62 | historyOfUser?: MUserId | |
63 | ||
64 | startDate?: string // ISO 8601 | |
65 | endDate?: string // ISO 8601 | |
66 | originallyPublishedStartDate?: string | |
67 | originallyPublishedEndDate?: string | |
68 | ||
69 | durationMin?: number // seconds | |
70 | durationMax?: number // seconds | |
71 | ||
72 | search?: string | |
73 | ||
74 | isCount?: boolean | |
75 | ||
76 | group?: string | |
77 | having?: string | |
2ec349aa C |
78 | |
79 | transaction?: Transaction | |
80 | logging?: boolean | |
2a4c0d8b W |
81 | |
82 | excludeAlreadyWatched?: boolean | |
e5dbd508 C |
83 | } |
84 | ||
7e7d8e48 | 85 | export class VideosIdListQueryBuilder extends AbstractRunQuery { |
d9bf974f C |
86 | protected replacements: any = {} |
87 | ||
e5dbd508 | 88 | private attributes: string[] |
d9bf974f | 89 | private joins: string[] = [] |
e5dbd508 | 90 | |
e5dbd508 | 91 | private readonly and: string[] = [] |
e5dbd508 C |
92 | |
93 | private readonly cte: string[] = [] | |
94 | ||
95 | private group = '' | |
96 | private having = '' | |
97 | ||
98 | private sort = '' | |
99 | private limit = '' | |
100 | private offset = '' | |
101 | ||
102 | constructor (protected readonly sequelize: Sequelize) { | |
156c44c8 | 103 | super(sequelize) |
e5dbd508 C |
104 | } |
105 | ||
106 | queryVideoIds (options: BuildVideosListQueryOptions) { | |
107 | this.buildIdsListQuery(options) | |
108 | ||
109 | return this.runQuery() | |
110 | } | |
111 | ||
112 | countVideoIds (countOptions: BuildVideosListQueryOptions): Promise<number> { | |
113 | this.buildIdsListQuery(countOptions) | |
114 | ||
bae61627 | 115 | return this.runQuery().then(rows => parseRowCountResult(rows)) |
e5dbd508 C |
116 | } |
117 | ||
7e7d8e48 | 118 | getQuery (options: BuildVideosListQueryOptions) { |
e5dbd508 | 119 | this.buildIdsListQuery(options) |
2760b454 | 120 | |
e5dbd508 C |
121 | return { query: this.query, sort: this.sort, replacements: this.replacements } |
122 | } | |
123 | ||
124 | private buildIdsListQuery (options: BuildVideosListQueryOptions) { | |
125 | this.attributes = options.attributes || [ '"video"."id"' ] | |
126 | ||
127 | if (options.group) this.group = options.group | |
128 | if (options.having) this.having = options.having | |
129 | ||
130 | this.joins = this.joins.concat([ | |
131 | 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"', | |
132 | 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"', | |
133 | 'INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id"' | |
134 | ]) | |
135 | ||
2760b454 C |
136 | if (!(options.include & VideoInclude.BLACKLISTED)) { |
137 | this.whereNotBlacklisted() | |
138 | } | |
e5dbd508 | 139 | |
2760b454 C |
140 | if (options.serverAccountIdForBlock && !(options.include & VideoInclude.BLOCKED_OWNER)) { |
141 | this.whereNotBlocked(options.serverAccountIdForBlock, options.user) | |
e5dbd508 C |
142 | } |
143 | ||
2760b454 C |
144 | // Only list published videos |
145 | if (!(options.include & VideoInclude.NOT_PUBLISHED_STATE)) { | |
146 | this.whereStateAvailable() | |
147 | } | |
148 | ||
e5dbd508 C |
149 | if (options.videoPlaylistId) { |
150 | this.joinPlaylist(options.videoPlaylistId) | |
151 | } | |
152 | ||
2760b454 C |
153 | if (exists(options.isLocal)) { |
154 | this.whereLocal(options.isLocal) | |
e5dbd508 C |
155 | } |
156 | ||
29837f88 C |
157 | if (options.host) { |
158 | this.whereHost(options.host) | |
159 | } | |
160 | ||
e5dbd508 C |
161 | if (options.accountId) { |
162 | this.whereAccountId(options.accountId) | |
163 | } | |
164 | ||
165 | if (options.videoChannelId) { | |
166 | this.whereChannelId(options.videoChannelId) | |
167 | } | |
168 | ||
2760b454 C |
169 | if (options.displayOnlyForFollower) { |
170 | this.whereFollowerActorId(options.displayOnlyForFollower) | |
e5dbd508 C |
171 | } |
172 | ||
3c10840f | 173 | if (options.hasFiles === true) { |
e5dbd508 C |
174 | this.whereFileExists() |
175 | } | |
176 | ||
d324756e C |
177 | if (exists(options.hasWebtorrentFiles)) { |
178 | this.whereWebTorrentFileExists(options.hasWebtorrentFiles) | |
179 | } | |
180 | ||
181 | if (exists(options.hasHLSFiles)) { | |
182 | this.whereHLSFileExists(options.hasHLSFiles) | |
183 | } | |
184 | ||
e5dbd508 C |
185 | if (options.tagsOneOf) { |
186 | this.whereTagsOneOf(options.tagsOneOf) | |
187 | } | |
188 | ||
189 | if (options.tagsAllOf) { | |
190 | this.whereTagsAllOf(options.tagsAllOf) | |
191 | } | |
192 | ||
527a52ac C |
193 | if (options.privacyOneOf) { |
194 | this.wherePrivacyOneOf(options.privacyOneOf) | |
195 | } else { | |
4b3145a7 | 196 | // Only list videos with the appropriate privacy |
527a52ac C |
197 | this.wherePrivacyAvailable(options.user) |
198 | } | |
199 | ||
fbd67e7f C |
200 | if (options.uuids) { |
201 | this.whereUUIDs(options.uuids) | |
202 | } | |
203 | ||
e5dbd508 C |
204 | if (options.nsfw === true) { |
205 | this.whereNSFW() | |
206 | } else if (options.nsfw === false) { | |
207 | this.whereSFW() | |
208 | } | |
209 | ||
210 | if (options.isLive === true) { | |
211 | this.whereLive() | |
212 | } else if (options.isLive === false) { | |
213 | this.whereVOD() | |
214 | } | |
215 | ||
216 | if (options.categoryOneOf) { | |
217 | this.whereCategoryOneOf(options.categoryOneOf) | |
218 | } | |
219 | ||
220 | if (options.licenceOneOf) { | |
221 | this.whereLicenceOneOf(options.licenceOneOf) | |
222 | } | |
223 | ||
224 | if (options.languageOneOf) { | |
225 | this.whereLanguageOneOf(options.languageOneOf) | |
226 | } | |
227 | ||
228 | // We don't exclude results in this so if we do a count we don't need to add this complex clause | |
229 | if (options.isCount !== true) { | |
230 | if (options.trendingDays) { | |
231 | this.groupForTrending(options.trendingDays) | |
232 | } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) { | |
233 | this.groupForHotOrBest(options.trendingAlgorithm, options.user) | |
234 | } | |
235 | } | |
236 | ||
237 | if (options.historyOfUser) { | |
238 | this.joinHistory(options.historyOfUser.id) | |
239 | } | |
240 | ||
241 | if (options.startDate) { | |
242 | this.whereStartDate(options.startDate) | |
243 | } | |
244 | ||
245 | if (options.endDate) { | |
246 | this.whereEndDate(options.endDate) | |
247 | } | |
248 | ||
249 | if (options.originallyPublishedStartDate) { | |
250 | this.whereOriginallyPublishedStartDate(options.originallyPublishedStartDate) | |
251 | } | |
252 | ||
253 | if (options.originallyPublishedEndDate) { | |
254 | this.whereOriginallyPublishedEndDate(options.originallyPublishedEndDate) | |
255 | } | |
256 | ||
257 | if (options.durationMin) { | |
258 | this.whereDurationMin(options.durationMin) | |
259 | } | |
260 | ||
261 | if (options.durationMax) { | |
262 | this.whereDurationMax(options.durationMax) | |
263 | } | |
264 | ||
2a4c0d8b W |
265 | if (options.excludeAlreadyWatched) { |
266 | if (exists(options.user.id)) { | |
267 | this.whereExcludeAlreadyWatched(options.user.id) | |
268 | } else { | |
269 | throw new Error('Cannot use excludeAlreadyWatched parameter when auth token is not provided') | |
270 | } | |
271 | } | |
272 | ||
e5dbd508 C |
273 | this.whereSearch(options.search) |
274 | ||
275 | if (options.isCount === true) { | |
276 | this.setCountAttribute() | |
277 | } else { | |
278 | if (exists(options.sort)) { | |
279 | this.setSort(options.sort) | |
280 | } | |
281 | ||
282 | if (exists(options.count)) { | |
283 | this.setLimit(options.count) | |
284 | } | |
285 | ||
286 | if (exists(options.start)) { | |
287 | this.setOffset(options.start) | |
288 | } | |
289 | } | |
290 | ||
291 | const cteString = this.cte.length !== 0 | |
292 | ? `WITH ${this.cte.join(', ')} ` | |
293 | : '' | |
294 | ||
295 | this.query = cteString + | |
296 | 'SELECT ' + this.attributes.join(', ') + ' ' + | |
297 | 'FROM "video" ' + this.joins.join(' ') + ' ' + | |
298 | 'WHERE ' + this.and.join(' AND ') + ' ' + | |
299 | this.group + ' ' + | |
300 | this.having + ' ' + | |
301 | this.sort + ' ' + | |
302 | this.limit + ' ' + | |
303 | this.offset | |
304 | } | |
305 | ||
306 | private setCountAttribute () { | |
307 | this.attributes = [ 'COUNT(*) as "total"' ] | |
308 | } | |
309 | ||
310 | private joinHistory (userId: number) { | |
311 | this.joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"') | |
312 | ||
313 | this.and.push('"userVideoHistory"."userId" = :historyOfUser') | |
314 | ||
315 | this.replacements.historyOfUser = userId | |
316 | } | |
317 | ||
318 | private joinPlaylist (playlistId: number) { | |
319 | this.joins.push( | |
320 | 'INNER JOIN "videoPlaylistElement" "video"."id" = "videoPlaylistElement"."videoId" ' + | |
321 | 'AND "videoPlaylistElement"."videoPlaylistId" = :videoPlaylistId' | |
322 | ) | |
323 | ||
324 | this.replacements.videoPlaylistId = playlistId | |
325 | } | |
326 | ||
2760b454 | 327 | private whereStateAvailable () { |
e5dbd508 C |
328 | this.and.push( |
329 | `("video"."state" = ${VideoState.PUBLISHED} OR ` + | |
330 | `("video"."state" = ${VideoState.TO_TRANSCODE} AND "video"."waitTranscoding" IS false))` | |
331 | ) | |
2760b454 | 332 | } |
e5dbd508 | 333 | |
2760b454 | 334 | private wherePrivacyAvailable (user?: MUserAccountId) { |
e5dbd508 C |
335 | if (user) { |
336 | this.and.push( | |
337 | `("video"."privacy" = ${VideoPrivacy.PUBLIC} OR "video"."privacy" = ${VideoPrivacy.INTERNAL})` | |
338 | ) | |
339 | } else { // Or only public videos | |
340 | this.and.push( | |
341 | `"video"."privacy" = ${VideoPrivacy.PUBLIC}` | |
342 | ) | |
343 | } | |
344 | } | |
345 | ||
2760b454 C |
346 | private whereLocal (isLocal: boolean) { |
347 | const isRemote = isLocal ? 'FALSE' : 'TRUE' | |
348 | ||
349 | this.and.push('"video"."remote" IS ' + isRemote) | |
e5dbd508 C |
350 | } |
351 | ||
29837f88 C |
352 | private whereHost (host: string) { |
353 | // Local instance | |
354 | if (host === WEBSERVER.HOST) { | |
355 | this.and.push('"accountActor"."serverId" IS NULL') | |
356 | return | |
357 | } | |
358 | ||
359 | this.joins.push('INNER JOIN "server" ON "server"."id" = "accountActor"."serverId"') | |
360 | ||
361 | this.and.push('"server"."host" = :host') | |
362 | this.replacements.host = host | |
363 | } | |
364 | ||
e5dbd508 C |
365 | private whereAccountId (accountId: number) { |
366 | this.and.push('"account"."id" = :accountId') | |
367 | this.replacements.accountId = accountId | |
368 | } | |
369 | ||
370 | private whereChannelId (channelId: number) { | |
371 | this.and.push('"videoChannel"."id" = :videoChannelId') | |
372 | this.replacements.videoChannelId = channelId | |
373 | } | |
374 | ||
2760b454 | 375 | private whereFollowerActorId (options: { actorId: number, orLocalVideos: boolean }) { |
e5dbd508 C |
376 | let query = |
377 | '(' + | |
4d029ef8 | 378 | ' EXISTS (' + // Videos shared by actors we follow |
e5dbd508 C |
379 | ' SELECT 1 FROM "videoShare" ' + |
380 | ' INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId" ' + | |
381 | ' AND "actorFollowShare"."actorId" = :followerActorId AND "actorFollowShare"."state" = \'accepted\' ' + | |
382 | ' WHERE "videoShare"."videoId" = "video"."id"' + | |
383 | ' )' + | |
384 | ' OR' + | |
84845992 | 385 | ' EXISTS (' + // Videos published by channels or accounts we follow |
e5dbd508 | 386 | ' SELECT 1 from "actorFollow" ' + |
84845992 C |
387 | ' WHERE ("actorFollow"."targetActorId" = "account"."actorId" OR "actorFollow"."targetActorId" = "videoChannel"."actorId") ' + |
388 | ' AND "actorFollow"."actorId" = :followerActorId ' + | |
e5dbd508 C |
389 | ' AND "actorFollow"."state" = \'accepted\'' + |
390 | ' )' | |
391 | ||
2760b454 | 392 | if (options.orLocalVideos) { |
e5dbd508 C |
393 | query += ' OR "video"."remote" IS FALSE' |
394 | } | |
395 | ||
396 | query += ')' | |
397 | ||
398 | this.and.push(query) | |
2760b454 | 399 | this.replacements.followerActorId = options.actorId |
e5dbd508 C |
400 | } |
401 | ||
402 | private whereFileExists () { | |
d324756e C |
403 | this.and.push(`(${this.buildWebTorrentFileExistsQuery(true)} OR ${this.buildHLSFileExistsQuery(true)})`) |
404 | } | |
405 | ||
406 | private whereWebTorrentFileExists (exists: boolean) { | |
407 | this.and.push(this.buildWebTorrentFileExistsQuery(exists)) | |
408 | } | |
409 | ||
410 | private whereHLSFileExists (exists: boolean) { | |
411 | this.and.push(this.buildHLSFileExistsQuery(exists)) | |
412 | } | |
413 | ||
414 | private buildWebTorrentFileExistsQuery (exists: boolean) { | |
415 | const prefix = exists ? '' : 'NOT ' | |
416 | ||
417 | return prefix + 'EXISTS (SELECT 1 FROM "videoFile" WHERE "videoFile"."videoId" = "video"."id")' | |
418 | } | |
419 | ||
420 | private buildHLSFileExistsQuery (exists: boolean) { | |
421 | const prefix = exists ? '' : 'NOT ' | |
422 | ||
423 | return prefix + 'EXISTS (' + | |
424 | ' SELECT 1 FROM "videoStreamingPlaylist" ' + | |
425 | ' INNER JOIN "videoFile" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist"."id" ' + | |
426 | ' WHERE "videoStreamingPlaylist"."videoId" = "video"."id"' + | |
427 | ')' | |
e5dbd508 C |
428 | } |
429 | ||
430 | private whereTagsOneOf (tagsOneOf: string[]) { | |
431 | const tagsOneOfLower = tagsOneOf.map(t => t.toLowerCase()) | |
432 | ||
433 | this.and.push( | |
434 | 'EXISTS (' + | |
435 | ' SELECT 1 FROM "videoTag" ' + | |
436 | ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + | |
437 | ' WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsOneOfLower) + ') ' + | |
438 | ' AND "video"."id" = "videoTag"."videoId"' + | |
439 | ')' | |
440 | ) | |
441 | } | |
442 | ||
443 | private whereTagsAllOf (tagsAllOf: string[]) { | |
444 | const tagsAllOfLower = tagsAllOf.map(t => t.toLowerCase()) | |
445 | ||
446 | this.and.push( | |
447 | 'EXISTS (' + | |
448 | ' SELECT 1 FROM "videoTag" ' + | |
449 | ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + | |
450 | ' WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsAllOfLower) + ') ' + | |
451 | ' AND "video"."id" = "videoTag"."videoId" ' + | |
452 | ' GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length + | |
453 | ')' | |
454 | ) | |
455 | } | |
456 | ||
527a52ac C |
457 | private wherePrivacyOneOf (privacyOneOf: VideoPrivacy[]) { |
458 | this.and.push('"video"."privacy" IN (:privacyOneOf)') | |
459 | this.replacements.privacyOneOf = privacyOneOf | |
460 | } | |
461 | ||
fbd67e7f C |
462 | private whereUUIDs (uuids: string[]) { |
463 | this.and.push('"video"."uuid" IN (' + createSafeIn(this.sequelize, uuids) + ')') | |
464 | } | |
465 | ||
e5dbd508 C |
466 | private whereCategoryOneOf (categoryOneOf: number[]) { |
467 | this.and.push('"video"."category" IN (:categoryOneOf)') | |
468 | this.replacements.categoryOneOf = categoryOneOf | |
469 | } | |
470 | ||
471 | private whereLicenceOneOf (licenceOneOf: number[]) { | |
472 | this.and.push('"video"."licence" IN (:licenceOneOf)') | |
473 | this.replacements.licenceOneOf = licenceOneOf | |
474 | } | |
475 | ||
476 | private whereLanguageOneOf (languageOneOf: string[]) { | |
477 | const languages = languageOneOf.filter(l => l && l !== '_unknown') | |
478 | const languagesQueryParts: string[] = [] | |
479 | ||
480 | if (languages.length !== 0) { | |
481 | languagesQueryParts.push('"video"."language" IN (:languageOneOf)') | |
482 | this.replacements.languageOneOf = languages | |
483 | ||
484 | languagesQueryParts.push( | |
485 | 'EXISTS (' + | |
486 | ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' + | |
487 | ' IN (' + createSafeIn(this.sequelize, languages) + ') AND ' + | |
488 | ' "videoCaption"."videoId" = "video"."id"' + | |
489 | ')' | |
490 | ) | |
491 | } | |
492 | ||
493 | if (languageOneOf.includes('_unknown')) { | |
494 | languagesQueryParts.push('"video"."language" IS NULL') | |
495 | } | |
496 | ||
497 | if (languagesQueryParts.length !== 0) { | |
498 | this.and.push('(' + languagesQueryParts.join(' OR ') + ')') | |
499 | } | |
500 | } | |
501 | ||
502 | private whereNSFW () { | |
503 | this.and.push('"video"."nsfw" IS TRUE') | |
504 | } | |
505 | ||
506 | private whereSFW () { | |
507 | this.and.push('"video"."nsfw" IS FALSE') | |
508 | } | |
509 | ||
510 | private whereLive () { | |
511 | this.and.push('"video"."isLive" IS TRUE') | |
512 | } | |
513 | ||
514 | private whereVOD () { | |
515 | this.and.push('"video"."isLive" IS FALSE') | |
516 | } | |
517 | ||
518 | private whereNotBlocked (serverAccountId: number, user?: MUserAccountId) { | |
519 | const blockerIds = [ serverAccountId ] | |
520 | if (user) blockerIds.push(user.Account.id) | |
521 | ||
522 | const inClause = createSafeIn(this.sequelize, blockerIds) | |
523 | ||
524 | this.and.push( | |
525 | 'NOT EXISTS (' + | |
526 | ' SELECT 1 FROM "accountBlocklist" ' + | |
527 | ' WHERE "accountBlocklist"."accountId" IN (' + inClause + ') ' + | |
528 | ' AND "accountBlocklist"."targetAccountId" = "account"."id" ' + | |
529 | ')' + | |
530 | 'AND NOT EXISTS (' + | |
531 | ' SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN (' + inClause + ') ' + | |
532 | ' AND "serverBlocklist"."targetServerId" = "accountActor"."serverId"' + | |
533 | ')' | |
534 | ) | |
535 | } | |
536 | ||
537 | private whereSearch (search?: string) { | |
538 | if (!search) { | |
539 | this.attributes.push('0 as similarity') | |
540 | return | |
541 | } | |
542 | ||
543 | const escapedSearch = this.sequelize.escape(search) | |
544 | const escapedLikeSearch = this.sequelize.escape('%' + search + '%') | |
545 | ||
546 | this.cte.push( | |
547 | '"trigramSearch" AS (' + | |
548 | ' SELECT "video"."id", ' + | |
549 | ` similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity ` + | |
550 | ' FROM "video" ' + | |
551 | ' WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' + | |
552 | ' lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' + | |
553 | ')' | |
554 | ) | |
555 | ||
556 | this.joins.push('LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id"') | |
557 | ||
558 | let base = '(' + | |
559 | ' "trigramSearch"."id" IS NOT NULL OR ' + | |
560 | ' EXISTS (' + | |
561 | ' SELECT 1 FROM "videoTag" ' + | |
562 | ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + | |
cd8f207a | 563 | ` WHERE lower("tag"."name") = lower(${escapedSearch}) ` + |
e5dbd508 C |
564 | ' AND "video"."id" = "videoTag"."videoId"' + |
565 | ' )' | |
566 | ||
567 | if (validator.isUUID(search)) { | |
568 | base += ` OR "video"."uuid" = ${escapedSearch}` | |
569 | } | |
570 | ||
571 | base += ')' | |
572 | ||
573 | this.and.push(base) | |
574 | this.attributes.push(`COALESCE("trigramSearch"."similarity", 0) as similarity`) | |
575 | } | |
576 | ||
577 | private whereNotBlacklisted () { | |
578 | this.and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")') | |
579 | } | |
580 | ||
581 | private whereStartDate (startDate: string) { | |
582 | this.and.push('"video"."publishedAt" >= :startDate') | |
583 | this.replacements.startDate = startDate | |
584 | } | |
585 | ||
586 | private whereEndDate (endDate: string) { | |
587 | this.and.push('"video"."publishedAt" <= :endDate') | |
588 | this.replacements.endDate = endDate | |
589 | } | |
590 | ||
591 | private whereOriginallyPublishedStartDate (startDate: string) { | |
592 | this.and.push('"video"."originallyPublishedAt" >= :originallyPublishedStartDate') | |
593 | this.replacements.originallyPublishedStartDate = startDate | |
594 | } | |
595 | ||
596 | private whereOriginallyPublishedEndDate (endDate: string) { | |
597 | this.and.push('"video"."originallyPublishedAt" <= :originallyPublishedEndDate') | |
598 | this.replacements.originallyPublishedEndDate = endDate | |
599 | } | |
600 | ||
601 | private whereDurationMin (durationMin: number) { | |
602 | this.and.push('"video"."duration" >= :durationMin') | |
603 | this.replacements.durationMin = durationMin | |
604 | } | |
605 | ||
606 | private whereDurationMax (durationMax: number) { | |
607 | this.and.push('"video"."duration" <= :durationMax') | |
608 | this.replacements.durationMax = durationMax | |
609 | } | |
610 | ||
2a4c0d8b W |
611 | private whereExcludeAlreadyWatched (userId: number) { |
612 | this.and.push( | |
613 | 'NOT EXISTS (' + | |
614 | ' SELECT 1' + | |
615 | ' FROM "userVideoHistory"' + | |
616 | ' WHERE "video"."id" = "userVideoHistory"."videoId"' + | |
617 | ' AND "userVideoHistory"."userId" = :excludeAlreadyWatchedUserId' + | |
618 | ')' | |
619 | ) | |
620 | this.replacements.excludeAlreadyWatchedUserId = userId | |
621 | } | |
622 | ||
e5dbd508 C |
623 | private groupForTrending (trendingDays: number) { |
624 | const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * trendingDays) | |
625 | ||
626 | this.joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') | |
627 | this.replacements.viewsGteDate = viewsGteDate | |
628 | ||
629 | this.attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"') | |
630 | ||
631 | this.group = 'GROUP BY "video"."id"' | |
632 | } | |
633 | ||
634 | private groupForHotOrBest (trendingAlgorithm: string, user?: MUserAccountId) { | |
635 | /** | |
636 | * "Hotness" is a measure based on absolute view/comment/like/dislike numbers, | |
637 | * with fixed weights only applied to their log values. | |
638 | * | |
639 | * This algorithm gives little chance for an old video to have a good score, | |
640 | * for which recent spikes in interactions could be a sign of "hotness" and | |
641 | * justify a better score. However there are multiple ways to achieve that | |
642 | * goal, which is left for later. Yes, this is a TODO :) | |
643 | * | |
644 | * notes: | |
645 | * - weights and base score are in number of half-days. | |
646 | * - all comments are counted, regardless of being written by the video author or not | |
647 | * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58 | |
648 | * - we have less interactions than on reddit, so multiply weights by an arbitrary factor | |
649 | */ | |
650 | const weights = { | |
651 | like: 3 * 50, | |
652 | dislike: -3 * 50, | |
653 | view: Math.floor((1 / 3) * 50), | |
654 | comment: 2 * 50, // a comment takes more time than a like to do, but can be done multiple times | |
655 | history: -2 * 50 | |
656 | } | |
657 | ||
658 | this.joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') | |
659 | ||
660 | let attribute = | |
661 | `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) | |
662 | `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) | |
663 | `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) | |
664 | `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+) | |
665 | '+ (SELECT (EXTRACT(epoch FROM "video"."publishedAt") - 1446156582) / 47000) ' // base score (in number of half-days) | |
666 | ||
667 | if (trendingAlgorithm === 'best' && user) { | |
668 | this.joins.push( | |
669 | 'LEFT JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :bestUser' | |
670 | ) | |
671 | this.replacements.bestUser = user.id | |
672 | ||
673 | attribute += `+ POWER(COUNT(DISTINCT "userVideoHistory"."id"), 2.0) * ${weights.history} ` | |
674 | } | |
675 | ||
676 | attribute += 'AS "score"' | |
677 | this.attributes.push(attribute) | |
678 | ||
679 | this.group = 'GROUP BY "video"."id"' | |
680 | } | |
681 | ||
682 | private setSort (sort: string) { | |
683 | if (sort === '-originallyPublishedAt' || sort === 'originallyPublishedAt') { | |
684 | this.attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"') | |
685 | } | |
686 | ||
687 | this.sort = this.buildOrder(sort) | |
688 | } | |
689 | ||
690 | private buildOrder (value: string) { | |
8c4bbd94 | 691 | const { direction, field } = buildSortDirectionAndField(value) |
e5dbd508 C |
692 | if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field) |
693 | ||
694 | if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' | |
695 | ||
696 | if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation | |
697 | return `ORDER BY "score" ${direction}, "video"."views" ${direction}` | |
698 | } | |
699 | ||
700 | let firstSort: string | |
701 | ||
702 | if (field.toLowerCase() === 'match') { // Search | |
703 | firstSort = '"similarity"' | |
704 | } else if (field === 'originallyPublishedAt') { | |
705 | firstSort = '"publishedAtForOrder"' | |
706 | } else if (field.includes('.')) { | |
707 | firstSort = field | |
708 | } else { | |
709 | firstSort = `"video"."${field}"` | |
710 | } | |
711 | ||
712 | return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC` | |
713 | } | |
714 | ||
715 | private setLimit (countArg: number) { | |
4638cd71 | 716 | const count = forceNumber(countArg) |
e5dbd508 C |
717 | this.limit = `LIMIT ${count}` |
718 | } | |
719 | ||
720 | private setOffset (startArg: number) { | |
4638cd71 | 721 | const start = forceNumber(startArg) |
e5dbd508 C |
722 | this.offset = `OFFSET ${start}` |
723 | } | |
724 | } |