Split types and typings
[oweals/peertube.git] / server / models / video / video.ts
index 5964526a997061c2503d361043d77935e10dc40b..e2718300e4f1d86b1cd95e231ab0954a7aa6b6bf 100644 (file)
@@ -1,7 +1,7 @@
 import * as Bluebird from 'bluebird'
-import { maxBy, minBy } from 'lodash'
+import { maxBy, minBy, pick } from 'lodash'
 import { join } from 'path'
-import { CountOptions, FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
+import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
 import {
   AllowNull,
   BeforeDestroy,
@@ -23,7 +23,7 @@ import {
   Table,
   UpdatedAt
 } from 'sequelize-typescript'
-import { UserRight, VideoPrivacy, VideoState } from '../../../shared'
+import { UserRight, VideoPrivacy, VideoState, ResultList } from '../../../shared'
 import { VideoTorrentObject } from '../../../shared/models/activitypub/objects'
 import { Video, VideoDetails } from '../../../shared/models/videos'
 import { VideoFilter } from '../../../shared/models/videos/video-query.type'
@@ -43,7 +43,6 @@ import {
 } from '../../helpers/custom-validators/videos'
 import { getVideoFileResolution } from '../../helpers/ffmpeg-utils'
 import { logger } from '../../helpers/logger'
-import { getServerActor } from '../../helpers/utils'
 import {
   ACTIVITY_PUB,
   API_VERSION,
@@ -65,16 +64,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate'
 import { ActorModel } from '../activitypub/actor'
 import { AvatarModel } from '../avatar/avatar'
 import { ServerModel } from '../server/server'
-import {
-  buildBlockedAccountSQL,
-  buildTrigramSearchIndex,
-  buildWhereIdOrUUID,
-  createSafeIn,
-  createSimilarityAttribute,
-  getVideoSort,
-  isOutdated,
-  throwIfNotValid
-} from '../utils'
+import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils'
 import { TagModel } from './tag'
 import { VideoAbuseModel } from './video-abuse'
 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
@@ -86,7 +76,7 @@ import { ScheduleVideoUpdateModel } from './schedule-video-update'
 import { VideoCaptionModel } from './video-caption'
 import { VideoBlacklistModel } from './video-blacklist'
 import { remove } from 'fs-extra'
-import { VideoViewModel } from './video-views'
+import { VideoViewModel } from './video-view'
 import { VideoRedundancyModel } from '../redundancy/video-redundancy'
 import {
   videoFilesModelToFormattedJSON,
@@ -120,19 +110,23 @@ import {
   MVideoFormattableDetails,
   MVideoForUser,
   MVideoFullLight,
-  MVideoIdThumbnail, MVideoImmutable,
+  MVideoIdThumbnail,
+  MVideoImmutable,
   MVideoThumbnail,
   MVideoThumbnailBlacklist,
   MVideoWithAllFiles,
   MVideoWithFile,
   MVideoWithRights
-} from '../../typings/models'
-import { MVideoFile, MVideoFileStreamingPlaylistVideo } from '../../typings/models/video/video-file'
-import { MThumbnail } from '../../typings/models/video/thumbnail'
+} from '../../types/models'
+import { MVideoFile, MVideoFileStreamingPlaylistVideo } from '../../types/models/video/video-file'
+import { MThumbnail } from '../../types/models/video/thumbnail'
 import { VideoFile } from '@shared/models/videos/video-file.model'
 import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths'
-import validator from 'validator'
 import { ModelCache } from '@server/models/model-cache'
+import { buildListQuery, BuildVideosQueryOptions, wrapForAPIResults } from './video-query-builder'
+import { buildNSFWFilter } from '@server/helpers/express-utils'
+import { getServerActor } from '@server/models/application/application'
+import { getPrivaciesForFederation, isPrivacyForFederation } from "@server/helpers/video"
 
 export enum ScopeNames {
   AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS',
@@ -224,7 +218,7 @@ export type AvailableForListIDsOptions = {
 
     if (options.withFiles === true) {
       query.include.push({
-        model: VideoFileModel.unscoped(),
+        model: VideoFileModel,
         required: true
       })
     }
@@ -241,285 +235,6 @@ export type AvailableForListIDsOptions = {
 
     return query
   },
-  [ScopeNames.AVAILABLE_FOR_LIST_IDS]: (options: AvailableForListIDsOptions) => {
-    const whereAnd = options.baseWhere ? [].concat(options.baseWhere) : []
-
-    const query: FindOptions = {
-      raw: true,
-      include: []
-    }
-
-    const attributesType = options.attributesType || 'id'
-
-    if (attributesType === 'id') query.attributes = [ 'id' ]
-    else if (attributesType === 'none') query.attributes = []
-
-    whereAnd.push({
-      id: {
-        [Op.notIn]: Sequelize.literal(
-          '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
-        )
-      }
-    })
-
-    if (options.serverAccountId) {
-      whereAnd.push({
-        channelId: {
-          [Op.notIn]: Sequelize.literal(
-            '(' +
-              'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
-                buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
-              ')' +
-            ')'
-          )
-        }
-      })
-    }
-
-    // Only list public/published videos
-    if (!options.filter || options.filter !== 'all-local') {
-      const publishWhere = {
-        // Always list published videos, or videos that are being transcoded but on which we don't want to wait for transcoding
-        [Op.or]: [
-          {
-            state: VideoState.PUBLISHED
-          },
-          {
-            [Op.and]: {
-              state: VideoState.TO_TRANSCODE,
-              waitTranscoding: false
-            }
-          }
-        ]
-      }
-      whereAnd.push(publishWhere)
-
-      // List internal videos if the user is logged in
-      if (options.user) {
-        const privacyWhere = {
-          [Op.or]: [
-            {
-              privacy: VideoPrivacy.INTERNAL
-            },
-            {
-              privacy: VideoPrivacy.PUBLIC
-            }
-          ]
-        }
-
-        whereAnd.push(privacyWhere)
-      } else { // Or only public videos
-        const privacyWhere = { privacy: VideoPrivacy.PUBLIC }
-        whereAnd.push(privacyWhere)
-      }
-    }
-
-    if (options.videoPlaylistId) {
-      query.include.push({
-        attributes: [],
-        model: VideoPlaylistElementModel.unscoped(),
-        required: true,
-        where: {
-          videoPlaylistId: options.videoPlaylistId
-        }
-      })
-
-      query.subQuery = false
-    }
-
-    if (options.filter && (options.filter === 'local' || options.filter === 'all-local')) {
-      whereAnd.push({
-        remote: false
-      })
-    }
-
-    if (options.accountId || options.videoChannelId) {
-      const videoChannelInclude: IncludeOptions = {
-        attributes: [],
-        model: VideoChannelModel.unscoped(),
-        required: true
-      }
-
-      if (options.videoChannelId) {
-        videoChannelInclude.where = {
-          id: options.videoChannelId
-        }
-      }
-
-      if (options.accountId) {
-        const accountInclude: IncludeOptions = {
-          attributes: [],
-          model: AccountModel.unscoped(),
-          required: true
-        }
-
-        accountInclude.where = { id: options.accountId }
-        videoChannelInclude.include = [ accountInclude ]
-      }
-
-      query.include.push(videoChannelInclude)
-    }
-
-    if (options.followerActorId) {
-      let localVideosReq: WhereOptions = {}
-      if (options.includeLocalVideos === true) {
-        localVideosReq = { remote: false }
-      }
-
-      // Force actorId to be a number to avoid SQL injections
-      const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
-      whereAnd.push({
-        [Op.or]: [
-          {
-            id: {
-              [Op.in]: Sequelize.literal(
-                '(' +
-                'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
-                'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
-                'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
-                ')'
-              )
-            }
-          },
-          {
-            id: {
-              [Op.in]: Sequelize.literal(
-                '(' +
-                'SELECT "video"."id" AS "id" FROM "video" ' +
-                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-                'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
-                'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
-                'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
-                'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
-                ')'
-              )
-            }
-          },
-          localVideosReq
-        ]
-      })
-    }
-
-    if (options.withFiles === true) {
-      whereAnd.push({
-        id: {
-          [Op.in]: Sequelize.literal(
-            '(SELECT "videoId" FROM "videoFile")'
-          )
-        }
-      })
-    }
-
-    // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
-    if (options.tagsAllOf || options.tagsOneOf) {
-      if (options.tagsOneOf) {
-        const tagsOneOfLower = options.tagsOneOf.map(t => t.toLowerCase())
-
-        whereAnd.push({
-          id: {
-            [Op.in]: Sequelize.literal(
-              '(' +
-              'SELECT "videoId" FROM "videoTag" ' +
-              'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
-              'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsOneOfLower) + ')' +
-              ')'
-            )
-          }
-        })
-      }
-
-      if (options.tagsAllOf) {
-        const tagsAllOfLower = options.tagsAllOf.map(t => t.toLowerCase())
-
-        whereAnd.push({
-          id: {
-            [Op.in]: Sequelize.literal(
-              '(' +
-              'SELECT "videoId" FROM "videoTag" ' +
-              'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
-              'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsAllOfLower) + ')' +
-              'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
-              ')'
-            )
-          }
-        })
-      }
-    }
-
-    if (options.nsfw === true || options.nsfw === false) {
-      whereAnd.push({ nsfw: options.nsfw })
-    }
-
-    if (options.categoryOneOf) {
-      whereAnd.push({
-        category: {
-          [Op.or]: options.categoryOneOf
-        }
-      })
-    }
-
-    if (options.licenceOneOf) {
-      whereAnd.push({
-        licence: {
-          [Op.or]: options.licenceOneOf
-        }
-      })
-    }
-
-    if (options.languageOneOf) {
-      let videoLanguages = options.languageOneOf
-      if (options.languageOneOf.find(l => l === '_unknown')) {
-        videoLanguages = videoLanguages.concat([ null ])
-      }
-
-      whereAnd.push({
-        [Op.or]: [
-          {
-            language: {
-              [Op.or]: videoLanguages
-            }
-          },
-          {
-            id: {
-              [Op.in]: Sequelize.literal(
-                '(' +
-                'SELECT "videoId" FROM "videoCaption" ' +
-                'WHERE "language" IN (' + createSafeIn(VideoModel, options.languageOneOf) + ') ' +
-                ')'
-              )
-            }
-          }
-        ]
-      })
-    }
-
-    if (options.trendingDays) {
-      query.include.push(VideoModel.buildTrendingQuery(options.trendingDays))
-
-      query.subQuery = false
-    }
-
-    if (options.historyOfUser) {
-      query.include.push({
-        model: UserVideoHistoryModel,
-        required: true,
-        where: {
-          userId: options.historyOfUser.id
-        }
-      })
-
-      // Even if the relation is n:m, we know that a user only have 0..1 video history
-      // So we won't have multiple rows for the same video
-      // Without this, we would not be able to sort on "updatedAt" column of UserVideoHistoryModel
-      query.subQuery = false
-    }
-
-    query.where = {
-      [Op.and]: whereAnd
-    }
-
-    return query
-  },
   [ScopeNames.WITH_THUMBNAILS]: {
     include: [
       {
@@ -624,7 +339,7 @@ export type AvailableForListIDsOptions = {
     return {
       include: [
         {
-          model: VideoFileModel.unscoped(),
+          model: VideoFileModel,
           separate: true, // We may have multiple files, having multiple redundancies so let's separate this join
           required: false,
           include: subInclude
@@ -635,7 +350,7 @@ export type AvailableForListIDsOptions = {
   [ScopeNames.WITH_STREAMING_PLAYLISTS]: (withRedundancies = false) => {
     const subInclude: IncludeOptions[] = [
       {
-        model: VideoFileModel.unscoped(),
+        model: VideoFileModel,
         required: false
       }
     ]
@@ -785,7 +500,7 @@ export class VideoModel extends Model<VideoModel> {
   @AllowNull(false)
   @Is('VideoPrivacy', value => throwIfNotValid(value, isVideoPrivacyValid, 'privacy'))
   @Column
-  privacy: number
+  privacy: VideoPrivacy
 
   @AllowNull(false)
   @Is('VideoNSFW', value => throwIfNotValid(value, isBooleanValid, 'NSFW boolean'))
@@ -914,9 +629,9 @@ export class VideoModel extends Model<VideoModel> {
   @HasMany(() => VideoAbuseModel, {
     foreignKey: {
       name: 'videoId',
-      allowNull: false
+      allowNull: true
     },
-    onDelete: 'cascade'
+    onDelete: 'set null'
   })
   VideoAbuses: VideoAbuseModel[]
 
@@ -1084,6 +799,33 @@ export class VideoModel extends Model<VideoModel> {
     ModelCache.Instance.invalidateCache('video', instance.id)
   }
 
+  @BeforeDestroy
+  static async saveEssentialDataToAbuses (instance: VideoModel, options) {
+    const tasks: Promise<any>[] = []
+
+    logger.info('Saving video abuses details of video %s.', instance.url)
+
+    if (!Array.isArray(instance.VideoAbuses)) {
+      instance.VideoAbuses = await instance.$get('VideoAbuses')
+
+      if (instance.VideoAbuses.length === 0) return undefined
+    }
+
+    const details = instance.toFormattedDetailsJSON()
+
+    for (const abuse of instance.VideoAbuses) {
+      abuse.deletedVideo = details
+      tasks.push(abuse.save({ transaction: options.transaction }))
+    }
+
+    Promise.all(tasks)
+           .catch(err => {
+             logger.error('Some errors when saving details of video %s in its abuses before destroy hook.', instance.uuid, { err })
+           })
+
+    return undefined
+  }
+
   static listLocal (): Bluebird<MVideoWithAllFiles[]> {
     const query = {
       where: {
@@ -1118,15 +860,12 @@ export class VideoModel extends Model<VideoModel> {
       distinct: true,
       offset: start,
       limit: count,
-      order: getVideoSort('createdAt', [ 'Tags', 'name', 'ASC' ] as any), // FIXME: sequelize typings
+      order: getVideoSort('-createdAt', [ 'Tags', 'name', 'ASC' ] as any), // FIXME: sequelize typings
       where: {
         id: {
           [Op.in]: Sequelize.literal('(' + rawQuery + ')')
         },
-        [Op.or]: [
-          { privacy: VideoPrivacy.PUBLIC },
-          { privacy: VideoPrivacy.UNLISTED }
-        ]
+        [Op.or]: getPrivaciesForFederation()
       },
       include: [
         {
@@ -1292,25 +1031,21 @@ export class VideoModel extends Model<VideoModel> {
       throw new Error('Try to filter all-local but no user has not the see all videos right')
     }
 
-    const query: FindOptions & { where?: null } = {
-      offset: options.start,
-      limit: options.count,
-      order: getVideoSort(options.sort)
-    }
-
-    let trendingDays: number
-    if (options.sort.endsWith('trending')) {
-      trendingDays = CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
-
-      query.group = 'VideoModel.id'
-    }
+    const trendingDays = options.sort.endsWith('trending')
+      ? CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
+      : undefined
 
     const serverActor = await getServerActor()
 
     // followerActorId === null has a meaning, so just check undefined
-    const followerActorId = options.followerActorId !== undefined ? options.followerActorId : serverActor.id
+    const followerActorId = options.followerActorId !== undefined
+      ? options.followerActorId
+      : serverActor.id
 
     const queryOptions = {
+      start: options.start,
+      count: options.count,
+      sort: options.sort,
       followerActorId,
       serverAccountId: serverActor.Account.id,
       nsfw: options.nsfw,
@@ -1330,7 +1065,7 @@ export class VideoModel extends Model<VideoModel> {
       trendingDays
     }
 
-    return VideoModel.getAvailableForApi(query, queryOptions, options.countVideos)
+    return VideoModel.getAvailableForApi(queryOptions, options.countVideos)
   }
 
   static async searchAndPopulateAccountAndServer (options: {
@@ -1354,88 +1089,6 @@ export class VideoModel extends Model<VideoModel> {
     user?: MUserAccountId
     filter?: VideoFilter
   }) {
-    const whereAnd = []
-
-    if (options.startDate || options.endDate) {
-      const publishedAtRange = {}
-
-      if (options.startDate) publishedAtRange[Op.gte] = options.startDate
-      if (options.endDate) publishedAtRange[Op.lte] = options.endDate
-
-      whereAnd.push({ publishedAt: publishedAtRange })
-    }
-
-    if (options.originallyPublishedStartDate || options.originallyPublishedEndDate) {
-      const originallyPublishedAtRange = {}
-
-      if (options.originallyPublishedStartDate) originallyPublishedAtRange[Op.gte] = options.originallyPublishedStartDate
-      if (options.originallyPublishedEndDate) originallyPublishedAtRange[Op.lte] = options.originallyPublishedEndDate
-
-      whereAnd.push({ originallyPublishedAt: originallyPublishedAtRange })
-    }
-
-    if (options.durationMin || options.durationMax) {
-      const durationRange = {}
-
-      if (options.durationMin) durationRange[Op.gte] = options.durationMin
-      if (options.durationMax) durationRange[Op.lte] = options.durationMax
-
-      whereAnd.push({ duration: durationRange })
-    }
-
-    const attributesInclude = []
-    const escapedSearch = VideoModel.sequelize.escape(options.search)
-    const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
-    if (options.search) {
-      const trigramSearch = {
-        id: {
-          [Op.in]: Sequelize.literal(
-            '(' +
-            'SELECT "video"."id" FROM "video" ' +
-            'WHERE ' +
-            'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
-            'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
-            'UNION ALL ' +
-            'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
-            'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
-            'WHERE lower("tag"."name") = lower(' + escapedSearch + ')' +
-            ')'
-          )
-        }
-      }
-
-      if (validator.isUUID(options.search)) {
-        whereAnd.push({
-          [Op.or]: [
-            trigramSearch,
-            {
-              uuid: options.search
-            }
-          ]
-        })
-      } else {
-        whereAnd.push(trigramSearch)
-      }
-
-      attributesInclude.push(createSimilarityAttribute('VideoModel.name', options.search))
-    }
-
-    // Cannot search on similarity if we don't have a search
-    if (!options.search) {
-      attributesInclude.push(
-        Sequelize.literal('0 as similarity')
-      )
-    }
-
-    const query = {
-      attributes: {
-        include: attributesInclude
-      },
-      offset: options.start,
-      limit: options.count,
-      order: getVideoSort(options.sort)
-    }
-
     const serverActor = await getServerActor()
     const queryOptions = {
       followerActorId: serverActor.id,
@@ -1449,10 +1102,21 @@ export class VideoModel extends Model<VideoModel> {
       tagsAllOf: options.tagsAllOf,
       user: options.user,
       filter: options.filter,
-      baseWhere: whereAnd
+      start: options.start,
+      count: options.count,
+      sort: options.sort,
+      startDate: options.startDate,
+      endDate: options.endDate,
+      originallyPublishedStartDate: options.originallyPublishedStartDate,
+      originallyPublishedEndDate: options.originallyPublishedEndDate,
+
+      durationMin: options.durationMin,
+      durationMax: options.durationMax,
+
+      search: options.search
     }
 
-    return VideoModel.getAvailableForApi(query, queryOptions)
+    return VideoModel.getAvailableForApi(queryOptions)
   }
 
   static load (id: number | string, t?: Transaction): Bluebird<MVideoThumbnail> {
@@ -1663,16 +1327,25 @@ export class VideoModel extends Model<VideoModel> {
         remote: false
       }
     })
-    const totalVideos = await VideoModel.count()
 
     let totalLocalVideoViews = await VideoModel.sum('views', {
       where: {
         remote: false
       }
     })
+
     // Sequelize could return null...
     if (!totalLocalVideoViews) totalLocalVideoViews = 0
 
+    const { total: totalVideos } = await VideoModel.listForApi({
+      start: 0,
+      count: 0,
+      sort: '-publishedAt',
+      nsfw: buildNSFWFilter(),
+      includeLocalVideos: true,
+      withFiles: false
+    })
+
     return {
       totalLocalVideos,
       totalLocalVideoViews,
@@ -1693,7 +1366,7 @@ export class VideoModel extends Model<VideoModel> {
     // Instances only share videos
     const query = 'SELECT 1 FROM "videoShare" ' +
       'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
-      'WHERE "actorFollow"."actorId" = $followerActorId AND "videoShare"."videoId" = $videoId ' +
+      'WHERE "actorFollow"."actorId" = $followerActorId AND "actorFollow"."state" = \'accepted\' AND "videoShare"."videoId" = $videoId ' +
       'LIMIT 1'
 
     const options = {
@@ -1734,26 +1407,22 @@ export class VideoModel extends Model<VideoModel> {
     const serverActor = await getServerActor()
     const followerActorId = serverActor.id
 
-    const scopeOptions: AvailableForListIDsOptions = {
+    const queryOptions: BuildVideosQueryOptions = {
+      attributes: [ `"${field}"` ],
+      group: `GROUP BY "${field}"`,
+      having: `HAVING COUNT("${field}") >= ${threshold}`,
+      start: 0,
+      sort: 'random',
+      count,
       serverAccountId: serverActor.Account.id,
       followerActorId,
-      includeLocalVideos: true,
-      attributesType: 'none' // Don't break aggregation
+      includeLocalVideos: true
     }
 
-    const query: FindOptions = {
-      attributes: [ field ],
-      limit: count,
-      group: field,
-      having: Sequelize.where(
-        Sequelize.fn('COUNT', Sequelize.col(field)), { [Op.gte]: threshold }
-      ),
-      order: [ (this.sequelize as any).random() ]
-    }
+    const { query, replacements } = buildListQuery(VideoModel, queryOptions)
 
-    return VideoModel.scope({ method: [ ScopeNames.AVAILABLE_FOR_LIST_IDS, scopeOptions ] })
-                     .findAll(query)
-                     .then(rows => rows.map(r => r[field]))
+    return this.sequelize.query<any>(query, { replacements, type: QueryTypes.SELECT })
+        .then(rows => rows.map(r => r[field]))
   }
 
   static buildTrendingQuery (trendingDays: number) {
@@ -1771,35 +1440,30 @@ export class VideoModel extends Model<VideoModel> {
   }
 
   private static async getAvailableForApi (
-    query: FindOptions & { where?: null }, // Forbid where field in query
-    options: AvailableForListIDsOptions,
+    options: BuildVideosQueryOptions,
     countVideos = true
-  ) {
-    const idsScope: ScopeOptions = {
-      method: [
-        ScopeNames.AVAILABLE_FOR_LIST_IDS, options
-      ]
-    }
+  ): Promise<ResultList<VideoModel>> {
+    function getCount () {
+      if (countVideos !== true) return Promise.resolve(undefined)
 
-    // Remove trending sort on count, because it uses a group by
-    const countOptions = Object.assign({}, options, { trendingDays: undefined })
-    const countQuery: CountOptions = Object.assign({}, query, { attributes: undefined, group: undefined })
-    const countScope: ScopeOptions = {
-      method: [
-        ScopeNames.AVAILABLE_FOR_LIST_IDS, countOptions
-      ]
+      const countOptions = Object.assign({}, options, { isCount: true })
+      const { query: queryCount, replacements: replacementsCount } = buildListQuery(VideoModel, countOptions)
+
+      return VideoModel.sequelize.query<any>(queryCount, { replacements: replacementsCount, type: QueryTypes.SELECT })
+          .then(rows => rows.length !== 0 ? rows[0].total : 0)
     }
 
-    const [ count, rows ] = await Promise.all([
-      countVideos
-        ? VideoModel.scope(countScope).count(countQuery)
-        : Promise.resolve<number>(undefined),
+    function getModels () {
+      if (options.count === 0) return Promise.resolve([])
+
+      const { query, replacements, order } = buildListQuery(VideoModel, options)
+      const queryModels = wrapForAPIResults(query, replacements, options, order)
+
+      return VideoModel.sequelize.query<any>(queryModels, { replacements, type: QueryTypes.SELECT, nest: true })
+          .then(rows => VideoModel.buildAPIResult(rows))
+    }
 
-      VideoModel.scope(idsScope)
-                .findAll(Object.assign({}, query, { raw: true }))
-                .then(rows => rows.map(r => r.id))
-                .then(ids => VideoModel.loadCompleteVideosForApi(ids, query, options))
-    ])
+    const [ count, rows ] = await Promise.all([ getCount(), getModels() ])
 
     return {
       data: rows,
@@ -1807,43 +1471,113 @@ export class VideoModel extends Model<VideoModel> {
     }
   }
 
-  private static loadCompleteVideosForApi (ids: number[], query: FindOptions, options: AvailableForListIDsOptions) {
-    if (ids.length === 0) return []
+  private static buildAPIResult (rows: any[]) {
+    const memo: { [ id: number ]: VideoModel } = {}
+
+    const thumbnailsDone = new Set<number>()
+    const historyDone = new Set<number>()
+    const videoFilesDone = new Set<number>()
+
+    const videos: VideoModel[] = []
+
+    const avatarKeys = [ 'id', 'filename', 'fileUrl', 'onDisk', 'createdAt', 'updatedAt' ]
+    const actorKeys = [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ]
+    const serverKeys = [ 'id', 'host' ]
+    const videoFileKeys = [ 'id', 'createdAt', 'updatedAt', 'resolution', 'size', 'extname', 'infoHash', 'fps', 'videoId' ]
+    const videoKeys = [
+      'id',
+      'uuid',
+      'name',
+      'category',
+      'licence',
+      'language',
+      'privacy',
+      'nsfw',
+      'description',
+      'support',
+      'duration',
+      'views',
+      'likes',
+      'dislikes',
+      'remote',
+      'url',
+      'commentsEnabled',
+      'downloadEnabled',
+      'waitTranscoding',
+      'state',
+      'publishedAt',
+      'originallyPublishedAt',
+      'channelId',
+      'createdAt',
+      'updatedAt'
+    ]
 
-    const secondQuery: FindOptions = {
-      offset: 0,
-      limit: query.limit,
-      attributes: query.attributes,
-      order: [ // Keep original order
-        Sequelize.literal(
-          ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ')
-        )
-      ]
-    }
+    function buildActor (rowActor: any) {
+      const avatarModel = rowActor.Avatar.id !== null
+        ? new AvatarModel(pick(rowActor.Avatar, avatarKeys))
+        : null
+
+      const serverModel = rowActor.Server.id !== null
+        ? new ServerModel(pick(rowActor.Server, serverKeys))
+        : null
 
-    const apiScope: (string | ScopeOptions)[] = []
+      const actorModel = new ActorModel(pick(rowActor, actorKeys))
+      actorModel.Avatar = avatarModel
+      actorModel.Server = serverModel
 
-    if (options.user) {
-      apiScope.push({ method: [ ScopeNames.WITH_USER_HISTORY, options.user.id ] })
+      return actorModel
     }
 
-    apiScope.push({
-      method: [
-        ScopeNames.FOR_API, {
-          ids,
-          withFiles: options.withFiles,
-          videoPlaylistId: options.videoPlaylistId
-        } as ForAPIOptions
-      ]
-    })
+    for (const row of rows) {
+      if (!memo[row.id]) {
+        // Build Channel
+        const channel = row.VideoChannel
+        const channelModel = new VideoChannelModel(pick(channel, [ 'id', 'name', 'description', 'actorId' ]))
+        channelModel.Actor = buildActor(channel.Actor)
 
-    return VideoModel.scope(apiScope).findAll(secondQuery)
-  }
+        const account = row.VideoChannel.Account
+        const accountModel = new AccountModel(pick(account, [ 'id', 'name' ]))
+        accountModel.Actor = buildActor(account.Actor)
+
+        channelModel.Account = accountModel
+
+        const videoModel = new VideoModel(pick(row, videoKeys))
+        videoModel.VideoChannel = channelModel
+
+        videoModel.UserVideoHistories = []
+        videoModel.Thumbnails = []
+        videoModel.VideoFiles = []
+
+        memo[row.id] = videoModel
+        // Don't take object value to have a sorted array
+        videos.push(videoModel)
+      }
+
+      const videoModel = memo[row.id]
 
-  private static isPrivacyForFederation (privacy: VideoPrivacy) {
-    const castedPrivacy = parseInt(privacy + '', 10)
+      if (row.userVideoHistory?.id && !historyDone.has(row.userVideoHistory.id)) {
+        const historyModel = new UserVideoHistoryModel(pick(row.userVideoHistory, [ 'id', 'currentTime' ]))
+        videoModel.UserVideoHistories.push(historyModel)
 
-    return castedPrivacy === VideoPrivacy.PUBLIC || castedPrivacy === VideoPrivacy.UNLISTED
+        historyDone.add(row.userVideoHistory.id)
+      }
+
+      if (row.Thumbnails?.id && !thumbnailsDone.has(row.Thumbnails.id)) {
+        const thumbnailModel = new ThumbnailModel(pick(row.Thumbnails, [ 'id', 'type', 'filename' ]))
+        videoModel.Thumbnails.push(thumbnailModel)
+
+        thumbnailsDone.add(row.Thumbnails.id)
+      }
+
+      if (row.VideoFiles?.id && !videoFilesDone.has(row.VideoFiles.id)) {
+        const videoFileModel = new VideoFileModel(pick(row.VideoFiles, videoFileKeys))
+        videoModel.VideoFiles.push(videoFileModel)
+
+        videoFilesDone.add(row.VideoFiles.id)
+      }
+    }
+
+    return videos
   }
 
   static getCategoryLabel (id: number) {
@@ -1871,8 +1605,7 @@ export class VideoModel extends Model<VideoModel> {
   }
 
   isBlocked () {
-    return (this.VideoChannel.Account.Actor.Server && this.VideoChannel.Account.Actor.Server.isBlocked()) ||
-      this.VideoChannel.Account.isBlocked()
+    return this.VideoChannel.Account.Actor.Server?.isBlocked() || this.VideoChannel.Account.isBlocked()
   }
 
   getQualityFileBy<T extends MVideoWithFile> (this: T, fun: (files: MVideoFile[], it: (file: MVideoFile) => number) => MVideoFile) {
@@ -1937,6 +1670,10 @@ export class VideoModel extends Model<VideoModel> {
     return this.uuid + '.jpg'
   }
 
+  hasPreview () {
+    return !!this.getPreview()
+  }
+
   getPreview () {
     if (Array.isArray(this.Thumbnails) === false) return undefined
 
@@ -2067,11 +1804,11 @@ export class VideoModel extends Model<VideoModel> {
   }
 
   hasPrivacyForFederation () {
-    return VideoModel.isPrivacyForFederation(this.privacy)
+    return isPrivacyForFederation(this.privacy)
   }
 
   isNewVideo (newPrivacy: VideoPrivacy) {
-    return this.hasPrivacyForFederation() === false && VideoModel.isPrivacyForFederation(newPrivacy) === true
+    return this.hasPrivacyForFederation() === false && isPrivacyForFederation(newPrivacy) === true
   }
 
   setAsRefreshed () {
@@ -2140,6 +1877,14 @@ export class VideoModel extends Model<VideoModel> {
     return baseUrlHttp + STATIC_PATHS.WEBSEED + getVideoFilename(this, videoFile)
   }
 
+  getVideoFileMetadataUrl (videoFile: MVideoFile, baseUrlHttp: string) {
+    const path = '/api/v1/videos/'
+
+    return this.isOwned()
+      ? baseUrlHttp + path + this.uuid + '/metadata/' + videoFile.id
+      : videoFile.metadataUrl
+  }
+
   getVideoRedundancyUrl (videoFile: MVideoFile, baseUrlHttp: string) {
     return baseUrlHttp + STATIC_PATHS.REDUNDANCY + getVideoFilename(this, videoFile)
   }