aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2018-08-31 11:44:48 +0200
committerChocobozzz <me@florianbigard.com>2018-08-31 11:44:48 +0200
commitb6314e3cee2ec9d9f7894fe057cfa7e9d0c50fbc (patch)
treefed501b05bcaad04888eb38623eeddbdd79c2298 /server
parent288fe38590788fb737eb4280309846c76c51e7c3 (diff)
downloadPeerTube-b6314e3cee2ec9d9f7894fe057cfa7e9d0c50fbc.tar.gz
PeerTube-b6314e3cee2ec9d9f7894fe057cfa7e9d0c50fbc.tar.zst
PeerTube-b6314e3cee2ec9d9f7894fe057cfa7e9d0c50fbc.zip
Cleanup video API SQL requests
Diffstat (limited to 'server')
-rw-r--r--server/models/video/video.ts82
1 files changed, 50 insertions, 32 deletions
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 48232fb7d..67b123d77 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -222,9 +222,13 @@ type AvailableForListIDsOptions = {
222 attributes: [ 'id' ], 222 attributes: [ 'id' ],
223 where: { 223 where: {
224 id: { 224 id: {
225 [Sequelize.Op.notIn]: Sequelize.literal( 225 [Sequelize.Op.and]: [
226 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")' 226 {
227 ) 227 [ Sequelize.Op.notIn ]: Sequelize.literal(
228 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
229 )
230 }
231 ]
228 }, 232 },
229 // Always list public videos 233 // Always list public videos
230 privacy: VideoPrivacy.PUBLIC, 234 privacy: VideoPrivacy.PUBLIC,
@@ -298,27 +302,30 @@ type AvailableForListIDsOptions = {
298 302
299 // Force actorId to be a number to avoid SQL injections 303 // Force actorId to be a number to avoid SQL injections
300 const actorIdNumber = parseInt(options.actorId.toString(), 10) 304 const actorIdNumber = parseInt(options.actorId.toString(), 10)
301 query.where['id'][ Sequelize.Op.in ] = Sequelize.literal( 305 query.where['id'][Sequelize.Op.and].push({
302 '(' + 306 [ Sequelize.Op.in ]: Sequelize.literal(
303 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' + 307 '(' +
304 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' + 308 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
305 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 309 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
306 ' UNION ALL ' + 310 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
307 'SELECT "video"."id" AS "id" FROM "video" ' + 311 ' UNION ALL ' +
308 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + 312 'SELECT "video"."id" AS "id" FROM "video" ' +
309 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + 313 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
310 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + 314 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
311 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + 315 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
312 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 316 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
313 localVideosReq + 317 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
314 ')' 318 localVideosReq +
315 ) 319 ')'
320 )
321 })
316 } 322 }
317 323
318 if (options.withFiles === true) { 324 if (options.withFiles === true) {
319 query.include.push({ 325 query.where['id'][Sequelize.Op.and].push({
320 model: VideoFileModel.unscoped(), 326 [ Sequelize.Op.in ]: Sequelize.literal(
321 required: true 327 '(SELECT "videoId" FROM "videoFile")'
328 )
322 }) 329 })
323 } 330 }
324 331
@@ -330,24 +337,28 @@ type AvailableForListIDsOptions = {
330 } 337 }
331 338
332 if (options.tagsOneOf) { 339 if (options.tagsOneOf) {
333 query.where['id'][Sequelize.Op.in] = Sequelize.literal( 340 query.where['id'][Sequelize.Op.and].push({
334 '(' + 341 [Sequelize.Op.in]: Sequelize.literal(
342 '(' +
335 'SELECT "videoId" FROM "videoTag" ' + 343 'SELECT "videoId" FROM "videoTag" ' +
336 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + 344 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
337 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsOneOf) + ')' + 345 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsOneOf) + ')' +
338 ')' 346 ')'
339 ) 347 )
348 })
340 } 349 }
341 350
342 if (options.tagsAllOf) { 351 if (options.tagsAllOf) {
343 query.where['id'][Sequelize.Op.in] = Sequelize.literal( 352 query.where['id'][Sequelize.Op.and].push({
353 [Sequelize.Op.in]: Sequelize.literal(
344 '(' + 354 '(' +
345 'SELECT "videoId" FROM "videoTag" ' + 355 'SELECT "videoId" FROM "videoTag" ' +
346 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + 356 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
347 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' + 357 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' +
348 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length + 358 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length +
349 ')' 359 ')'
350 ) 360 )
361 })
351 } 362 }
352 } 363 }
353 364
@@ -1162,7 +1173,14 @@ export class VideoModel extends Model<VideoModel> {
1162 const apiScope = { 1173 const apiScope = {
1163 method: [ ScopeNames.FOR_API, { ids, withFiles: options.withFiles } as ForAPIOptions ] 1174 method: [ ScopeNames.FOR_API, { ids, withFiles: options.withFiles } as ForAPIOptions ]
1164 } 1175 }
1165 const rows = await VideoModel.scope(apiScope).findAll(immutableAssign(query, { offset: 0 })) 1176
1177 const secondQuery = {
1178 offset: 0,
1179 limit: query.limit,
1180 order: query.order,
1181 attributes: query.attributes
1182 }
1183 const rows = await VideoModel.scope(apiScope).findAll(secondQuery)
1166 1184
1167 return { 1185 return {
1168 data: rows, 1186 data: rows,