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