Add language filters in user preferences

3 files changed, 140 insertions, 89 deletions

diff --git a/server/models/account/user.ts b/server/models/account/user.ts
index e75039521..aac691d66 100644
--- a/server/models/account/user.ts
+++ b/server/models/account/user.ts
@@ -31,6 +31,7 @@ import {
   isUserPasswordValid,
   isUserRoleValid,
   isUserUsernameValid,
+  isUserVideoLanguages,
   isUserVideoQuotaDailyValid,
   isUserVideoQuotaValid,
   isUserVideosHistoryEnabledValid,
@@ -147,6 +148,12 @@ export class UserModel extends Model<UserModel> {
   @Column
   autoPlayVideo: boolean
 
+  @AllowNull(true)
+  @Default(null)
+  @Is('UserVideoLanguages', value => throwIfNotValid(value, isUserVideoLanguages, 'video languages'))
+  @Column(DataType.ARRAY(DataType.STRING))
+  videoLanguages: string[]
+
   @AllowNull(false)
   @Default(UserAdminFlag.NONE)
   @Is('UserAdminFlags', value => throwIfNotValid(value, isUserAdminFlagsValid, 'user admin flags'))
@@ -551,6 +558,7 @@ export class UserModel extends Model<UserModel> {
       webTorrentEnabled: this.webTorrentEnabled,
       videosHistoryEnabled: this.videosHistoryEnabled,
       autoPlayVideo: this.autoPlayVideo,
+      videoLanguages: this.videoLanguages,
       role: this.role,
       roleLabel: USER_ROLE_LABELS[ this.role ],
       videoQuota: this.videoQuota,
diff --git a/server/models/utils.ts b/server/models/utils.ts
index 2b172f608..206e108c3 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -1,7 +1,7 @@
-import { Sequelize } from 'sequelize-typescript'
+import { Model, Sequelize } from 'sequelize-typescript'
 import * as validator from 'validator'
-import { OrderItem } from 'sequelize'
 import { Col } from 'sequelize/types/lib/utils'
+import { OrderItem } from 'sequelize/types'
 
 type SortType = { sortModel: any, sortValue: string }
 
@@ -127,6 +127,11 @@ function parseAggregateResult (result: any) {
   return total
 }
 
+const createSafeIn = (model: typeof Model, stringArr: string[]) => {
+  return stringArr.map(t => model.sequelize.escape(t))
+                  .join(', ')
+}
+
 // ---------------------------------------------------------------------------
 
 export {
@@ -141,7 +146,8 @@ export {
   buildTrigramSearchIndex,
   buildWhereIdOrUUID,
   isOutdated,
-  parseAggregateResult
+  parseAggregateResult,
+  createSafeIn
 }
 
 // ---------------------------------------------------------------------------
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index eccf0a4fa..92d07b5bc 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -83,6 +83,7 @@ import {
   buildBlockedAccountSQL,
   buildTrigramSearchIndex,
   buildWhereIdOrUUID,
+  createSafeIn,
   createSimilarityAttribute,
   getVideoSort,
   isOutdated,
@@ -227,6 +228,8 @@ type AvailableForListIDsOptions = {
   trendingDays?: number
   user?: UserModel,
   historyOfUser?: UserModel
+
+  baseWhere?: WhereOptions[]
 }
 
 @Scopes(() => ({
@@ -270,34 +273,34 @@ type AvailableForListIDsOptions = {
     return query
   },
   [ ScopeNames.AVAILABLE_FOR_LIST_IDS ]: (options: AvailableForListIDsOptions) => {
-    const attributes = options.withoutId === true ? [] : [ 'id' ]
+    const whereAnd = options.baseWhere ? options.baseWhere : []
 
     const query: FindOptions = {
       raw: true,
-      attributes,
-      where: {
-        id: {
-          [ Op.and ]: [
-            {
-              [ Op.notIn ]: Sequelize.literal(
-                '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
-              )
-            }
-          ]
-        },
-        channelId: {
-          [ Op.notIn ]: Sequelize.literal(
-            '(' +
-              'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
-                buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
-              ')' +
-            ')'
-          )
-        }
-      },
+      attributes: options.withoutId === true ? [] : [ 'id' ],
       include: []
     }
 
+    whereAnd.push({
+      id: {
+        [ Op.notIn ]: Sequelize.literal(
+          '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
+        )
+      }
+    })
+
+    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 privacyWhere = {
@@ -317,7 +320,7 @@ type AvailableForListIDsOptions = {
         ]
       }
 
-      Object.assign(query.where, privacyWhere)
+      whereAnd.push(privacyWhere)
     }
 
     if (options.videoPlaylistId) {
@@ -387,86 +390,114 @@ type AvailableForListIDsOptions = {
 
       // Force actorId to be a number to avoid SQL injections
       const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
-      query.where[ 'id' ][ Op.and ].push({
-        [ Op.in ]: Sequelize.literal(
-          '(' +
-          'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
-          'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
-          'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
-          ' UNION ALL ' +
-          '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 +
-          ')'
-        )
+      whereAnd.push({
+        id: {
+          [ Op.in ]: Sequelize.literal(
+            '(' +
+            'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
+            'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
+            'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
+            ' UNION ALL ' +
+            '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) {
-      query.where[ 'id' ][ Op.and ].push({
-        [ Op.in ]: Sequelize.literal(
-          '(SELECT "videoId" FROM "videoFile")'
-        )
+      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) {
-      const createTagsIn = (tags: string[]) => {
-        return tags.map(t => VideoModel.sequelize.escape(t))
-                   .join(', ')
-      }
-
       if (options.tagsOneOf) {
-        query.where[ 'id' ][ Op.and ].push({
-          [ Op.in ]: Sequelize.literal(
-            '(' +
-            'SELECT "videoId" FROM "videoTag" ' +
-            'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
-            'WHERE "tag"."name" IN (' + createTagsIn(options.tagsOneOf) + ')' +
-            ')'
-          )
+        whereAnd.push({
+          id: {
+            [ Op.in ]: Sequelize.literal(
+              '(' +
+              'SELECT "videoId" FROM "videoTag" ' +
+              'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
+              'WHERE "tag"."name" IN (' + createSafeIn(VideoModel, options.tagsOneOf) + ')' +
+              ')'
+            )
+          }
         })
       }
 
       if (options.tagsAllOf) {
-        query.where[ 'id' ][ Op.and ].push({
-          [ Op.in ]: Sequelize.literal(
-            '(' +
-            'SELECT "videoId" FROM "videoTag" ' +
-            'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
-            'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' +
-            'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length +
-            ')'
-          )
+        whereAnd.push({
+          id: {
+            [ Op.in ]: Sequelize.literal(
+              '(' +
+              'SELECT "videoId" FROM "videoTag" ' +
+              'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
+              'WHERE "tag"."name" IN (' + createSafeIn(VideoModel, options.tagsAllOf) + ')' +
+              'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length +
+              ')'
+            )
+          }
         })
       }
     }
 
     if (options.nsfw === true || options.nsfw === false) {
-      query.where[ 'nsfw' ] = options.nsfw
+      whereAnd.push({ nsfw: options.nsfw })
     }
 
     if (options.categoryOneOf) {
-      query.where[ 'category' ] = {
-        [ Op.or ]: options.categoryOneOf
-      }
+      whereAnd.push({
+        category: {
+          [ Op.or ]: options.categoryOneOf
+        }
+      })
     }
 
     if (options.licenceOneOf) {
-      query.where[ 'licence' ] = {
-        [ Op.or ]: options.licenceOneOf
-      }
+      whereAnd.push({
+        licence: {
+          [ Op.or ]: options.licenceOneOf
+        }
+      })
     }
 
     if (options.languageOneOf) {
-      query.where[ 'language' ] = {
-        [ Op.or ]: 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) {
@@ -490,6 +521,10 @@ type AvailableForListIDsOptions = {
       query.subQuery = false
     }
 
+    query.where = {
+      [ Op.and ]: whereAnd
+    }
+
     return query
   },
   [ ScopeNames.WITH_THUMBNAILS ]: {
@@ -1175,7 +1210,7 @@ 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 = {
+    const query: FindOptions & { where?: null } = {
       offset: options.start,
       limit: options.count,
       order: getVideoSort(options.sort)
@@ -1299,16 +1334,13 @@ export class VideoModel extends Model<VideoModel> {
       )
     }
 
-    const query: FindOptions = {
+    const query = {
       attributes: {
         include: attributesInclude
       },
       offset: options.start,
       limit: options.count,
-      order: getVideoSort(options.sort),
-      where: {
-        [ Op.and ]: whereAnd
-      }
+      order: getVideoSort(options.sort)
     }
 
     const serverActor = await getServerActor()
@@ -1323,7 +1355,8 @@ export class VideoModel extends Model<VideoModel> {
       tagsOneOf: options.tagsOneOf,
       tagsAllOf: options.tagsAllOf,
       user: options.user,
-      filter: options.filter
+      filter: options.filter,
+      baseWhere: whereAnd
     }
 
     return VideoModel.getAvailableForApi(query, queryOptions)
@@ -1590,7 +1623,7 @@ export class VideoModel extends Model<VideoModel> {
   }
 
   private static async getAvailableForApi (
-    query: FindOptions,
+    query: FindOptions & { where?: null }, // Forbid where field in query
     options: AvailableForListIDsOptions,
     countVideos = true
   ) {
@@ -1609,11 +1642,15 @@ export class VideoModel extends Model<VideoModel> {
       ]
     }
 
-    const [ count, rowsId ] = await Promise.all([
-      countVideos ? VideoModel.scope(countScope).count(countQuery) : Promise.resolve<number>(undefined),
-      VideoModel.scope(idsScope).findAll(query)
+    const [ count, ids ] = await Promise.all([
+      countVideos
+        ? VideoModel.scope(countScope).count(countQuery)
+        : Promise.resolve<number>(undefined),
+
+      VideoModel.scope(idsScope)
+                .findAll(query)
+                .then(rows => rows.map(r => r.id))
     ])
-    const ids = rowsId.map(r => r.id)
 
     if (ids.length === 0) return { data: [], total: count }