diff options
author | Chocobozzz <me@florianbigard.com> | 2020-03-05 15:04:57 +0100 |
---|---|---|
committer | Chocobozzz <me@florianbigard.com> | 2020-03-10 14:03:58 +0100 |
commit | 5f3e2425f1c64d93860a0c3341de9b361b3c1f1f (patch) | |
tree | a95a64e865ac047b403a6c40ff4161ac0c07ba7c /server | |
parent | f8cce49c3f36e03edd93ce141b93c49c7d6bfe58 (diff) | |
download | PeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.tar.gz PeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.tar.zst PeerTube-5f3e2425f1c64d93860a0c3341de9b361b3c1f1f.zip |
Rewrite video list in raw SQL
Diffstat (limited to 'server')
-rw-r--r-- | server/models/utils.ts | 38 | ||||
-rw-r--r-- | server/models/video/video-query-builder.ts | 359 | ||||
-rw-r--r-- | server/models/video/video.ts | 465 |
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 | ||
158 | const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => { | 158 | const 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 | ||
163 | function buildLocalAccountIdsIn () { | 166 | function buildLocalAccountIdsIn () { |
@@ -172,6 +175,21 @@ function buildLocalActorIdsIn () { | |||
172 | ) | 175 | ) |
173 | } | 176 | } |
174 | 177 | ||
178 | function 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 | ||
177 | export { | 195 | export { |
@@ -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) { | |||
203 | function searchTrigramNormalizeCol (col: string) { | 222 | function 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 | |||
207 | function 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 @@ | |||
1 | import { VideoFilter, VideoPrivacy, VideoState } from '@shared/models' | ||
2 | import { buildDirectionAndField, createSafeIn } from '@server/models/utils' | ||
3 | import { Model } from 'sequelize-typescript' | ||
4 | import { MUserAccountId, MUserId } from '@server/typings/models' | ||
5 | import validator from 'validator' | ||
6 | |||
7 | export 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 | |||
53 | function 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 | |||
336 | function 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 | |||
357 | export { | ||
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 @@ | |||
1 | import * as Bluebird from 'bluebird' | 1 | import * as Bluebird from 'bluebird' |
2 | import { maxBy, minBy } from 'lodash' | 2 | import { maxBy, minBy } from 'lodash' |
3 | import { join } from 'path' | 3 | import { join } from 'path' |
4 | import { CountOptions, FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' | 4 | import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' |
5 | import { | 5 | import { |
6 | AllowNull, | 6 | AllowNull, |
7 | BeforeDestroy, | 7 | BeforeDestroy, |
@@ -65,16 +65,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate' | |||
65 | import { ActorModel } from '../activitypub/actor' | 65 | import { ActorModel } from '../activitypub/actor' |
66 | import { AvatarModel } from '../avatar/avatar' | 66 | import { AvatarModel } from '../avatar/avatar' |
67 | import { ServerModel } from '../server/server' | 67 | import { ServerModel } from '../server/server' |
68 | import { | 68 | import { 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' | ||
78 | import { TagModel } from './tag' | 69 | import { TagModel } from './tag' |
79 | import { VideoAbuseModel } from './video-abuse' | 70 | import { VideoAbuseModel } from './video-abuse' |
80 | import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel' | 71 | import { 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 | |||
131 | import { MThumbnail } from '../../typings/models/video/thumbnail' | 123 | import { MThumbnail } from '../../typings/models/video/thumbnail' |
132 | import { VideoFile } from '@shared/models/videos/video-file.model' | 124 | import { VideoFile } from '@shared/models/videos/video-file.model' |
133 | import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths' | 125 | import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths' |
134 | import validator from 'validator' | ||
135 | import { ModelCache } from '@server/models/model-cache' | 126 | import { ModelCache } from '@server/models/model-cache' |
127 | import { buildListQuery, BuildVideosQueryOptions } from './video-query-builder' | ||
136 | 128 | ||
137 | export enum ScopeNames { | 129 | export 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(', ') |