Optimize feeds query

4 files changed, 69 insertions, 24 deletions

diff --git a/server/models/video/sql/video/shared/abstract-video-query-builder.ts b/server/models/video/sql/video/shared/abstract-video-query-builder.ts
index b79d20ade..3c74b0ea6 100644
--- a/server/models/video/sql/video/shared/abstract-video-query-builder.ts
+++ b/server/models/video/sql/video/shared/abstract-video-query-builder.ts
@@ -313,7 +313,12 @@ export class AbstractVideoQueryBuilder extends AbstractRunQuery {
     return result
   }
 
-  protected whereId (options: { id?: string | number, url?: string }) {
+  protected whereId (options: { ids?: number[], id?: string | number, url?: string }) {
+    if (options.ids) {
+      this.where = `WHERE "video"."id" IN (${createSafeIn(this.sequelize, options.ids)})`
+      return
+    }
+
     if (options.url) {
       this.where = 'WHERE "video"."url" = :videoUrl'
       this.replacements.videoUrl = options.url
diff --git a/server/models/video/sql/video/shared/video-file-query-builder.ts b/server/models/video/sql/video/shared/video-file-query-builder.ts
index 50c12f627..cc53a4860 100644
--- a/server/models/video/sql/video/shared/video-file-query-builder.ts
+++ b/server/models/video/sql/video/shared/video-file-query-builder.ts
@@ -1,7 +1,17 @@
-import { Sequelize } from 'sequelize'
-import { BuildVideoGetQueryOptions } from '../video-model-get-query-builder'
+import { Sequelize, Transaction } from 'sequelize'
 import { AbstractVideoQueryBuilder } from './abstract-video-query-builder'
 
+export type FileQueryOptions = {
+  id?: string | number
+  url?: string
+
+  includeRedundancy: boolean
+
+  transaction?: Transaction
+
+  logging?: boolean
+}
+
 /**
  *
  * Fetch files (webtorrent and streaming playlist) according to a video
@@ -15,26 +25,26 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder {
     super(sequelize, 'get')
   }
 
-  queryWebTorrentVideos (options: BuildVideoGetQueryOptions) {
+  queryWebTorrentVideos (options: FileQueryOptions) {
     this.buildWebtorrentFilesQuery(options)
 
     return this.runQuery(options)
   }
 
-  queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) {
+  queryStreamingPlaylistVideos (options: FileQueryOptions) {
     this.buildVideoStreamingPlaylistFilesQuery(options)
 
     return this.runQuery(options)
   }
 
-  private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) {
+  private buildWebtorrentFilesQuery (options: FileQueryOptions) {
     this.attributes = {
       '"video"."id"': ''
     }
 
     this.includeWebtorrentFiles()
 
-    if (this.shouldIncludeRedundancies(options)) {
+    if (options.includeRedundancy) {
       this.includeWebTorrentRedundancies()
     }
 
@@ -43,14 +53,14 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder {
     this.query = this.buildQuery()
   }
 
-  private buildVideoStreamingPlaylistFilesQuery (options: BuildVideoGetQueryOptions) {
+  private buildVideoStreamingPlaylistFilesQuery (options: FileQueryOptions) {
     this.attributes = {
       '"video"."id"': ''
     }
 
     this.includeStreamingPlaylistFiles()
 
-    if (this.shouldIncludeRedundancies(options)) {
+    if (options.includeRedundancy) {
       this.includeStreamingPlaylistRedundancies()
     }
 
@@ -62,8 +72,4 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder {
   private buildQuery () {
     return `${this.buildSelect()} FROM "video" ${this.joins} ${this.where}`
   }
-
-  private shouldIncludeRedundancies (options: BuildVideoGetQueryOptions) {
-    return options.type === 'api'
-  }
 }
diff --git a/server/models/video/sql/video/video-model-get-query-builder.ts b/server/models/video/sql/video/video-model-get-query-builder.ts
index b0879c9ac..32e5c4ff7 100644
--- a/server/models/video/sql/video/video-model-get-query-builder.ts
+++ b/server/models/video/sql/video/video-model-get-query-builder.ts
@@ -1,3 +1,4 @@
+import { pick } from 'lodash'
 import { Sequelize, Transaction } from 'sequelize'
 import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder'
 import { VideoFileQueryBuilder } from './shared/video-file-query-builder'
@@ -50,15 +51,21 @@ export class VideoModelGetQueryBuilder {
   }
 
   async queryVideo (options: BuildVideoGetQueryOptions) {
+    const fileQueryOptions = {
+      ...pick(options, [ 'id', 'url', 'transaction', 'logging' ]),
+
+      includeRedundancy: this.shouldIncludeRedundancies(options)
+    }
+
     const [ videoRows, webtorrentFilesRows, streamingPlaylistFilesRows ] = await Promise.all([
       this.videoQueryBuilder.queryVideos(options),
 
       VideoModelGetQueryBuilder.videoFilesInclude.has(options.type)
-        ? this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(options)
+        ? this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(fileQueryOptions)
         : Promise.resolve(undefined),
 
       VideoModelGetQueryBuilder.videoFilesInclude.has(options.type)
-        ? this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(options)
+        ? this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(fileQueryOptions)
         : Promise.resolve(undefined)
     ])
 
@@ -76,6 +83,10 @@ export class VideoModelGetQueryBuilder {
 
     return videos[0]
   }
+
+  private shouldIncludeRedundancies (options: BuildVideoGetQueryOptions) {
+    return options.type === 'api'
+  }
 }
 
 export class VideosModelGetQuerySubBuilder extends AbstractVideoQueryBuilder {
diff --git a/server/models/video/sql/video/videos-model-list-query-builder.ts b/server/models/video/sql/video/videos-model-list-query-builder.ts
index 2a4afc389..4fe6bc321 100644
--- a/server/models/video/sql/video/videos-model-list-query-builder.ts
+++ b/server/models/video/sql/video/videos-model-list-query-builder.ts
@@ -1,6 +1,8 @@
-import { VideoInclude } from '@shared/models'
+import { pick } from 'lodash'
 import { Sequelize } from 'sequelize'
+import { VideoInclude } from '@shared/models'
 import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder'
+import { VideoFileQueryBuilder } from './shared/video-file-query-builder'
 import { VideoModelBuilder } from './shared/video-model-builder'
 import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder'
 
@@ -16,20 +18,46 @@ export class VideosModelListQueryBuilder extends AbstractVideoQueryBuilder {
   private innerQuery: string
   private innerSort: string
 
+  webtorrentFilesQueryBuilder: VideoFileQueryBuilder
+  streamingPlaylistFilesQueryBuilder: VideoFileQueryBuilder
+
   private readonly videoModelBuilder: VideoModelBuilder
 
   constructor (protected readonly sequelize: Sequelize) {
     super(sequelize, 'list')
 
     this.videoModelBuilder = new VideoModelBuilder(this.mode, this.tables)
+    this.webtorrentFilesQueryBuilder = new VideoFileQueryBuilder(sequelize)
+    this.streamingPlaylistFilesQueryBuilder = new VideoFileQueryBuilder(sequelize)
   }
 
-  queryVideos (options: BuildVideosListQueryOptions) {
+  async queryVideos (options: BuildVideosListQueryOptions) {
     this.buildInnerQuery(options)
     this.buildMainQuery(options)
 
-    return this.runQuery()
-      .then(rows => this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include }))
+    const rows = await this.runQuery()
+
+    if (options.include & VideoInclude.FILES) {
+      const videoIds = Array.from(new Set(rows.map(r => r.id)))
+
+      if (videoIds.length !== 0) {
+        const fileQueryOptions = {
+          ...pick(options, [ 'transaction', 'logging' ]),
+
+          ids: videoIds,
+          includeRedundancy: false
+        }
+
+        const [ rowsWebTorrentFiles, rowsStreamingPlaylist ] = await Promise.all([
+          this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(fileQueryOptions),
+          this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(fileQueryOptions)
+        ])
+
+        return this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include, rowsStreamingPlaylist, rowsWebTorrentFiles })
+      }
+    }
+
+    return this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include })
   }
 
   private buildInnerQuery (options: BuildVideosListQueryOptions) {
@@ -52,11 +80,6 @@ export class VideosModelListQueryBuilder extends AbstractVideoQueryBuilder {
     this.includeAccounts()
     this.includeThumbnails()
 
-    if (options.include & VideoInclude.FILES) {
-      this.includeWebtorrentFiles()
-      this.includeStreamingPlaylistFiles()
-    }
-
     if (options.user) {
       this.includeUserHistory(options.user.id)
     }