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