aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
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
parentf8cce49c3f36e03edd93ce141b93c49c7d6bfe58 (diff)
downloadPeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.tar.gz
PeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.tar.zst
PeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.zip
Rewrite video list in raw SQL
Diffstat (limited to 'server/models')
-rw-r--r--server/models/utils.ts38
-rw-r--r--server/models/video/video-query-builder.ts359
-rw-r--r--server/models/video/video.ts465
3 files changed, 428 insertions, 434 deletions
diff --git a/server/models/utils.ts b/server/models/utils.ts
index f7afb8d4b..674ddcbe4 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -156,8 +156,11 @@ function parseAggregateResult (result: any) {
156} 156}
157 157
158const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => { 158const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => {
159 return stringArr.map(t => model.sequelize.escape('' + t)) 159 return stringArr.map(t => {
160 .join(', ') 160 return t === null
161 ? null
162 : model.sequelize.escape('' + t)
163 }).join(', ')
161} 164}
162 165
163function buildLocalAccountIdsIn () { 166function buildLocalAccountIdsIn () {
@@ -172,6 +175,21 @@ function buildLocalActorIdsIn () {
172 ) 175 )
173} 176}
174 177
178function buildDirectionAndField (value: string) {
179 let field: string
180 let direction: 'ASC' | 'DESC'
181
182 if (value.substring(0, 1) === '-') {
183 direction = 'DESC'
184 field = value.substring(1)
185 } else {
186 direction = 'ASC'
187 field = value
188 }
189
190 return { direction, field }
191}
192
175// --------------------------------------------------------------------------- 193// ---------------------------------------------------------------------------
176 194
177export { 195export {
@@ -191,6 +209,7 @@ export {
191 isOutdated, 209 isOutdated,
192 parseAggregateResult, 210 parseAggregateResult,
193 getFollowsSort, 211 getFollowsSort,
212 buildDirectionAndField,
194 createSafeIn 213 createSafeIn
195} 214}
196 215
@@ -203,18 +222,3 @@ function searchTrigramNormalizeValue (value: string) {
203function searchTrigramNormalizeCol (col: string) { 222function searchTrigramNormalizeCol (col: string) {
204 return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col))) 223 return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
205} 224}
206
207function buildDirectionAndField (value: string) {
208 let field: string
209 let direction: 'ASC' | 'DESC'
210
211 if (value.substring(0, 1) === '-') {
212 direction = 'DESC'
213 field = value.substring(1)
214 } else {
215 direction = 'ASC'
216 field = value
217 }
218
219 return { direction, field }
220}
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}
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 2e518317d..217ca8e50 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -1,7 +1,7 @@
1import * as Bluebird from 'bluebird' 1import * as Bluebird from 'bluebird'
2import { maxBy, minBy } from 'lodash' 2import { maxBy, minBy } from 'lodash'
3import { join } from 'path' 3import { join } from 'path'
4import { CountOptions, FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' 4import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
5import { 5import {
6 AllowNull, 6 AllowNull,
7 BeforeDestroy, 7 BeforeDestroy,
@@ -65,16 +65,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate'
65import { ActorModel } from '../activitypub/actor' 65import { ActorModel } from '../activitypub/actor'
66import { AvatarModel } from '../avatar/avatar' 66import { AvatarModel } from '../avatar/avatar'
67import { ServerModel } from '../server/server' 67import { ServerModel } from '../server/server'
68import { 68import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils'
69 buildBlockedAccountSQL,
70 buildTrigramSearchIndex,
71 buildWhereIdOrUUID,
72 createSafeIn,
73 createSimilarityAttribute,
74 getVideoSort,
75 isOutdated,
76 throwIfNotValid
77} from '../utils'
78import { TagModel } from './tag' 69import { TagModel } from './tag'
79import { VideoAbuseModel } from './video-abuse' 70import { VideoAbuseModel } from './video-abuse'
80import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel' 71import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
@@ -120,7 +111,8 @@ import {
120 MVideoFormattableDetails, 111 MVideoFormattableDetails,
121 MVideoForUser, 112 MVideoForUser,
122 MVideoFullLight, 113 MVideoFullLight,
123 MVideoIdThumbnail, MVideoImmutable, 114 MVideoIdThumbnail,
115 MVideoImmutable,
124 MVideoThumbnail, 116 MVideoThumbnail,
125 MVideoThumbnailBlacklist, 117 MVideoThumbnailBlacklist,
126 MVideoWithAllFiles, 118 MVideoWithAllFiles,
@@ -131,8 +123,8 @@ import { MVideoFile, MVideoFileStreamingPlaylistVideo } from '../../typings/mode
131import { MThumbnail } from '../../typings/models/video/thumbnail' 123import { MThumbnail } from '../../typings/models/video/thumbnail'
132import { VideoFile } from '@shared/models/videos/video-file.model' 124import { VideoFile } from '@shared/models/videos/video-file.model'
133import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths' 125import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths'
134import validator from 'validator'
135import { ModelCache } from '@server/models/model-cache' 126import { ModelCache } from '@server/models/model-cache'
127import { buildListQuery, BuildVideosQueryOptions } from './video-query-builder'
136 128
137export enum ScopeNames { 129export enum ScopeNames {
138 AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS', 130 AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS',
@@ -241,274 +233,6 @@ export type AvailableForListIDsOptions = {
241 233
242 return query 234 return query
243 }, 235 },
244 [ScopeNames.AVAILABLE_FOR_LIST_IDS]: (options: AvailableForListIDsOptions) => {
245 const whereAnd = options.baseWhere ? [].concat(options.baseWhere) : []
246
247 const query: FindOptions = {
248 raw: true,
249 include: []
250 }
251
252 const attributesType = options.attributesType || 'id'
253
254 if (attributesType === 'id') query.attributes = [ 'id' ]
255 else if (attributesType === 'none') query.attributes = []
256
257 whereAnd.push({
258 id: {
259 [Op.notIn]: Sequelize.literal(
260 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
261 )
262 }
263 })
264
265 if (options.serverAccountId) {
266 whereAnd.push({
267 channelId: {
268 [Op.notIn]: Sequelize.literal(
269 '(' +
270 'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
271 buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
272 ')' +
273 ')'
274 )
275 }
276 })
277 }
278
279 // Only list public/published videos
280 if (!options.filter || options.filter !== 'all-local') {
281 const publishWhere = {
282 // Always list published videos, or videos that are being transcoded but on which we don't want to wait for transcoding
283 [Op.or]: [
284 {
285 state: VideoState.PUBLISHED
286 },
287 {
288 [Op.and]: {
289 state: VideoState.TO_TRANSCODE,
290 waitTranscoding: false
291 }
292 }
293 ]
294 }
295 whereAnd.push(publishWhere)
296
297 // List internal videos if the user is logged in
298 if (options.user) {
299 const privacyWhere = {
300 [Op.or]: [
301 {
302 privacy: VideoPrivacy.INTERNAL
303 },
304 {
305 privacy: VideoPrivacy.PUBLIC
306 }
307 ]
308 }
309
310 whereAnd.push(privacyWhere)
311 } else { // Or only public videos
312 const privacyWhere = { privacy: VideoPrivacy.PUBLIC }
313 whereAnd.push(privacyWhere)
314 }
315 }
316
317 if (options.videoPlaylistId) {
318 query.include.push({
319 attributes: [],
320 model: VideoPlaylistElementModel.unscoped(),
321 required: true,
322 where: {
323 videoPlaylistId: options.videoPlaylistId
324 }
325 })
326
327 query.subQuery = false
328 }
329
330 if (options.filter && (options.filter === 'local' || options.filter === 'all-local')) {
331 whereAnd.push({
332 remote: false
333 })
334 }
335
336 if (options.accountId || options.videoChannelId) {
337 const videoChannelInclude: IncludeOptions = {
338 attributes: [],
339 model: VideoChannelModel.unscoped(),
340 required: true
341 }
342
343 if (options.videoChannelId) {
344 videoChannelInclude.where = {
345 id: options.videoChannelId
346 }
347 }
348
349 if (options.accountId) {
350 const accountInclude: IncludeOptions = {
351 attributes: [],
352 model: AccountModel.unscoped(),
353 required: true
354 }
355
356 accountInclude.where = { id: options.accountId }
357 videoChannelInclude.include = [ accountInclude ]
358 }
359
360 query.include.push(videoChannelInclude)
361 }
362
363 if (options.followerActorId) {
364 let localVideosReq = ''
365 if (options.includeLocalVideos === true) {
366 localVideosReq = ' UNION ALL SELECT "video"."id" FROM "video" WHERE remote IS FALSE'
367 }
368
369 // Force actorId to be a number to avoid SQL injections
370 const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
371 whereAnd.push({
372 id: {
373 [Op.in]: Sequelize.literal(
374 '(' +
375 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
376 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
377 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
378 ' UNION ALL ' +
379 'SELECT "video"."id" AS "id" FROM "video" ' +
380 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
381 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
382 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
383 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
384 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
385 localVideosReq +
386 ')'
387 )
388 }
389 })
390 }
391
392 if (options.withFiles === true) {
393 whereAnd.push({
394 id: {
395 [Op.in]: Sequelize.literal(
396 '(SELECT "videoId" FROM "videoFile")'
397 )
398 }
399 })
400 }
401
402 // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
403 if (options.tagsAllOf || options.tagsOneOf) {
404 if (options.tagsOneOf) {
405 const tagsOneOfLower = options.tagsOneOf.map(t => t.toLowerCase())
406
407 whereAnd.push({
408 id: {
409 [Op.in]: Sequelize.literal(
410 '(' +
411 'SELECT "videoId" FROM "videoTag" ' +
412 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
413 'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsOneOfLower) + ')' +
414 ')'
415 )
416 }
417 })
418 }
419
420 if (options.tagsAllOf) {
421 const tagsAllOfLower = options.tagsAllOf.map(t => t.toLowerCase())
422
423 whereAnd.push({
424 id: {
425 [Op.in]: Sequelize.literal(
426 '(' +
427 'SELECT "videoId" FROM "videoTag" ' +
428 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
429 'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsAllOfLower) + ')' +
430 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
431 ')'
432 )
433 }
434 })
435 }
436 }
437
438 if (options.nsfw === true || options.nsfw === false) {
439 whereAnd.push({ nsfw: options.nsfw })
440 }
441
442 if (options.categoryOneOf) {
443 whereAnd.push({
444 category: {
445 [Op.or]: options.categoryOneOf
446 }
447 })
448 }
449
450 if (options.licenceOneOf) {
451 whereAnd.push({
452 licence: {
453 [Op.or]: options.licenceOneOf
454 }
455 })
456 }
457
458 if (options.languageOneOf) {
459 let videoLanguages = options.languageOneOf
460 if (options.languageOneOf.find(l => l === '_unknown')) {
461 videoLanguages = videoLanguages.concat([ null ])
462 }
463
464 whereAnd.push({
465 [Op.or]: [
466 {
467 language: {
468 [Op.or]: videoLanguages
469 }
470 },
471 {
472 id: {
473 [Op.in]: Sequelize.literal(
474 '(' +
475 'SELECT "videoId" FROM "videoCaption" ' +
476 'WHERE "language" IN (' + createSafeIn(VideoModel, options.languageOneOf) + ') ' +
477 ')'
478 )
479 }
480 }
481 ]
482 })
483 }
484
485 if (options.trendingDays) {
486 query.include.push(VideoModel.buildTrendingQuery(options.trendingDays))
487
488 query.subQuery = false
489 }
490
491 if (options.historyOfUser) {
492 query.include.push({
493 model: UserVideoHistoryModel,
494 required: true,
495 where: {
496 userId: options.historyOfUser.id
497 }
498 })
499
500 // Even if the relation is n:m, we know that a user only have 0..1 video history
501 // So we won't have multiple rows for the same video
502 // Without this, we would not be able to sort on "updatedAt" column of UserVideoHistoryModel
503 query.subQuery = false
504 }
505
506 query.where = {
507 [Op.and]: whereAnd
508 }
509
510 return query
511 },
512 [ScopeNames.WITH_THUMBNAILS]: { 236 [ScopeNames.WITH_THUMBNAILS]: {
513 include: [ 237 include: [
514 { 238 {
@@ -1281,25 +1005,21 @@ export class VideoModel extends Model<VideoModel> {
1281 throw new Error('Try to filter all-local but no user has not the see all videos right') 1005 throw new Error('Try to filter all-local but no user has not the see all videos right')
1282 } 1006 }
1283 1007
1284 const query: FindOptions & { where?: null } = { 1008 const trendingDays = options.sort.endsWith('trending')
1285 offset: options.start, 1009 ? CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
1286 limit: options.count, 1010 : undefined
1287 order: getVideoSort(options.sort)
1288 }
1289
1290 let trendingDays: number
1291 if (options.sort.endsWith('trending')) {
1292 trendingDays = CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
1293
1294 query.group = 'VideoModel.id'
1295 }
1296 1011
1297 const serverActor = await getServerActor() 1012 const serverActor = await getServerActor()
1298 1013
1299 // followerActorId === null has a meaning, so just check undefined 1014 // followerActorId === null has a meaning, so just check undefined
1300 const followerActorId = options.followerActorId !== undefined ? options.followerActorId : serverActor.id 1015 const followerActorId = options.followerActorId !== undefined
1016 ? options.followerActorId
1017 : serverActor.id
1301 1018
1302 const queryOptions = { 1019 const queryOptions = {
1020 start: options.start,
1021 count: options.count,
1022 sort: options.sort,
1303 followerActorId, 1023 followerActorId,
1304 serverAccountId: serverActor.Account.id, 1024 serverAccountId: serverActor.Account.id,
1305 nsfw: options.nsfw, 1025 nsfw: options.nsfw,
@@ -1319,7 +1039,7 @@ export class VideoModel extends Model<VideoModel> {
1319 trendingDays 1039 trendingDays
1320 } 1040 }
1321 1041
1322 return VideoModel.getAvailableForApi(query, queryOptions, options.countVideos) 1042 return VideoModel.getAvailableForApi(queryOptions, options.countVideos)
1323 } 1043 }
1324 1044
1325 static async searchAndPopulateAccountAndServer (options: { 1045 static async searchAndPopulateAccountAndServer (options: {
@@ -1343,88 +1063,6 @@ export class VideoModel extends Model<VideoModel> {
1343 user?: MUserAccountId 1063 user?: MUserAccountId
1344 filter?: VideoFilter 1064 filter?: VideoFilter
1345 }) { 1065 }) {
1346 const whereAnd = []
1347
1348 if (options.startDate || options.endDate) {
1349 const publishedAtRange = {}
1350
1351 if (options.startDate) publishedAtRange[Op.gte] = options.startDate
1352 if (options.endDate) publishedAtRange[Op.lte] = options.endDate
1353
1354 whereAnd.push({ publishedAt: publishedAtRange })
1355 }
1356
1357 if (options.originallyPublishedStartDate || options.originallyPublishedEndDate) {
1358 const originallyPublishedAtRange = {}
1359
1360 if (options.originallyPublishedStartDate) originallyPublishedAtRange[Op.gte] = options.originallyPublishedStartDate
1361 if (options.originallyPublishedEndDate) originallyPublishedAtRange[Op.lte] = options.originallyPublishedEndDate
1362
1363 whereAnd.push({ originallyPublishedAt: originallyPublishedAtRange })
1364 }
1365
1366 if (options.durationMin || options.durationMax) {
1367 const durationRange = {}
1368
1369 if (options.durationMin) durationRange[Op.gte] = options.durationMin
1370 if (options.durationMax) durationRange[Op.lte] = options.durationMax
1371
1372 whereAnd.push({ duration: durationRange })
1373 }
1374
1375 const attributesInclude = []
1376 const escapedSearch = VideoModel.sequelize.escape(options.search)
1377 const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
1378 if (options.search) {
1379 const trigramSearch = {
1380 id: {
1381 [Op.in]: Sequelize.literal(
1382 '(' +
1383 'SELECT "video"."id" FROM "video" ' +
1384 'WHERE ' +
1385 'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
1386 'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
1387 'UNION ALL ' +
1388 'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
1389 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
1390 'WHERE lower("tag"."name") = lower(' + escapedSearch + ')' +
1391 ')'
1392 )
1393 }
1394 }
1395
1396 if (validator.isUUID(options.search)) {
1397 whereAnd.push({
1398 [Op.or]: [
1399 trigramSearch,
1400 {
1401 uuid: options.search
1402 }
1403 ]
1404 })
1405 } else {
1406 whereAnd.push(trigramSearch)
1407 }
1408
1409 attributesInclude.push(createSimilarityAttribute('VideoModel.name', options.search))
1410 }
1411
1412 // Cannot search on similarity if we don't have a search
1413 if (!options.search) {
1414 attributesInclude.push(
1415 Sequelize.literal('0 as similarity')
1416 )
1417 }
1418
1419 const query = {
1420 attributes: {
1421 include: attributesInclude
1422 },
1423 offset: options.start,
1424 limit: options.count,
1425 order: getVideoSort(options.sort)
1426 }
1427
1428 const serverActor = await getServerActor() 1066 const serverActor = await getServerActor()
1429 const queryOptions = { 1067 const queryOptions = {
1430 followerActorId: serverActor.id, 1068 followerActorId: serverActor.id,
@@ -1438,10 +1076,21 @@ export class VideoModel extends Model<VideoModel> {
1438 tagsAllOf: options.tagsAllOf, 1076 tagsAllOf: options.tagsAllOf,
1439 user: options.user, 1077 user: options.user,
1440 filter: options.filter, 1078 filter: options.filter,
1441 baseWhere: whereAnd 1079 start: options.start,
1080 count: options.count,
1081 sort: options.sort,
1082 startDate: options.startDate,
1083 endDate: options.endDate,
1084 originallyPublishedStartDate: options.originallyPublishedStartDate,
1085 originallyPublishedEndDate: options.originallyPublishedEndDate,
1086
1087 durationMin: options.durationMin,
1088 durationMax: options.durationMax,
1089
1090 search: options.search
1442 } 1091 }
1443 1092
1444 return VideoModel.getAvailableForApi(query, queryOptions) 1093 return VideoModel.getAvailableForApi(queryOptions)
1445 } 1094 }
1446 1095
1447 static load (id: number | string, t?: Transaction): Bluebird<MVideoThumbnail> { 1096 static load (id: number | string, t?: Transaction): Bluebird<MVideoThumbnail> {
@@ -1723,26 +1372,22 @@ export class VideoModel extends Model<VideoModel> {
1723 const serverActor = await getServerActor() 1372 const serverActor = await getServerActor()
1724 const followerActorId = serverActor.id 1373 const followerActorId = serverActor.id
1725 1374
1726 const scopeOptions: AvailableForListIDsOptions = { 1375 const queryOptions: BuildVideosQueryOptions = {
1376 attributes: [ `"${field}"` ],
1377 group: `GROUP BY "${field}"`,
1378 having: `HAVING COUNT("${field}") >= ${threshold}`,
1379 start: 0,
1380 sort: 'random',
1381 count,
1727 serverAccountId: serverActor.Account.id, 1382 serverAccountId: serverActor.Account.id,
1728 followerActorId, 1383 followerActorId,
1729 includeLocalVideos: true, 1384 includeLocalVideos: true
1730 attributesType: 'none' // Don't break aggregation
1731 } 1385 }
1732 1386
1733 const query: FindOptions = { 1387 const { query, replacements } = buildListQuery(VideoModel, queryOptions)
1734 attributes: [ field ],
1735 limit: count,
1736 group: field,
1737 having: Sequelize.where(
1738 Sequelize.fn('COUNT', Sequelize.col(field)), { [Op.gte]: threshold }
1739 ),
1740 order: [ (this.sequelize as any).random() ]
1741 }
1742 1388
1743 return VideoModel.scope({ method: [ ScopeNames.AVAILABLE_FOR_LIST_IDS, scopeOptions ] }) 1389 return this.sequelize.query<any>(query, { replacements, type: QueryTypes.SELECT })
1744 .findAll(query) 1390 .then(rows => rows.map(r => r[field]))
1745 .then(rows => rows.map(r => r[field]))
1746 } 1391 }
1747 1392
1748 static buildTrendingQuery (trendingDays: number) { 1393 static buildTrendingQuery (trendingDays: number) {
@@ -1760,34 +1405,21 @@ export class VideoModel extends Model<VideoModel> {
1760 } 1405 }
1761 1406
1762 private static async getAvailableForApi ( 1407 private static async getAvailableForApi (
1763 query: FindOptions & { where?: null }, // Forbid where field in query 1408 options: BuildVideosQueryOptions,
1764 options: AvailableForListIDsOptions,
1765 countVideos = true 1409 countVideos = true
1766 ) { 1410 ) {
1767 const idsScope: ScopeOptions = { 1411 const { query, replacements } = buildListQuery(VideoModel, options)
1768 method: [ 1412 const { query: queryCount, replacements: replacementsCount } = buildListQuery(VideoModel, Object.assign({}, options, { isCount: true }))
1769 ScopeNames.AVAILABLE_FOR_LIST_IDS, options
1770 ]
1771 }
1772
1773 // Remove trending sort on count, because it uses a group by
1774 const countOptions = Object.assign({}, options, { trendingDays: undefined })
1775 const countQuery: CountOptions = Object.assign({}, query, { attributes: undefined, group: undefined })
1776 const countScope: ScopeOptions = {
1777 method: [
1778 ScopeNames.AVAILABLE_FOR_LIST_IDS, countOptions
1779 ]
1780 }
1781 1413
1782 const [ count, rows ] = await Promise.all([ 1414 const [ count, rows ] = await Promise.all([
1783 countVideos 1415 countVideos
1784 ? VideoModel.scope(countScope).count(countQuery) 1416 ? this.sequelize.query<any>(queryCount, { replacements: replacementsCount, type: QueryTypes.SELECT })
1417 .then(rows => rows.length !== 0 ? rows[0].total : 0)
1785 : Promise.resolve<number>(undefined), 1418 : Promise.resolve<number>(undefined),
1786 1419
1787 VideoModel.scope(idsScope) 1420 this.sequelize.query<any>(query, { replacements, type: QueryTypes.SELECT })
1788 .findAll(Object.assign({}, query, { raw: true }))
1789 .then(rows => rows.map(r => r.id)) 1421 .then(rows => rows.map(r => r.id))
1790 .then(ids => VideoModel.loadCompleteVideosForApi(ids, query, options)) 1422 .then(ids => VideoModel.loadCompleteVideosForApi(ids, options))
1791 ]) 1423 ])
1792 1424
1793 return { 1425 return {
@@ -1796,13 +1428,12 @@ export class VideoModel extends Model<VideoModel> {
1796 } 1428 }
1797 } 1429 }
1798 1430
1799 private static loadCompleteVideosForApi (ids: number[], query: FindOptions, options: AvailableForListIDsOptions) { 1431 private static loadCompleteVideosForApi (ids: number[], options: BuildVideosQueryOptions) {
1800 if (ids.length === 0) return [] 1432 if (ids.length === 0) return []
1801 1433
1802 const secondQuery: FindOptions = { 1434 const secondQuery: FindOptions = {
1803 offset: 0, 1435 offset: 0,
1804 limit: query.limit, 1436 limit: options.count,
1805 attributes: query.attributes,
1806 order: [ // Keep original order 1437 order: [ // Keep original order
1807 Sequelize.literal( 1438 Sequelize.literal(
1808 ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ') 1439 ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ')