2 files changed, 45 insertions, 2 deletions

diff --git a/server/models/video/tag.ts b/server/models/video/tag.ts
index 6d79a5575..e39a418cd 100644
--- a/server/models/video/tag.ts
+++ b/server/models/video/tag.ts
@@ -1,10 +1,11 @@
 import * as Bluebird from 'bluebird'
-import { Transaction } from 'sequelize'
+import * as Sequelize from 'sequelize'
 import { AllowNull, BelongsToMany, Column, CreatedAt, Is, Model, Table, UpdatedAt } from 'sequelize-typescript'
 import { isVideoTagValid } from '../../helpers/custom-validators/videos'
 import { throwIfNotValid } from '../utils'
 import { VideoModel } from './video'
 import { VideoTagModel } from './video-tag'
+import { VideoPrivacy, VideoState } from '../../../shared/models/videos'
 
 @Table({
   tableName: 'tag',
@@ -36,7 +37,7 @@ export class TagModel extends Model<TagModel> {
   })
   Videos: VideoModel[]
 
-  static findOrCreateTags (tags: string[], transaction: Transaction) {
+  static findOrCreateTags (tags: string[], transaction: Sequelize.Transaction) {
     if (tags === null) return []
 
     const tasks: Bluebird<TagModel>[] = []
@@ -59,4 +60,23 @@ export class TagModel extends Model<TagModel> {
 
     return Promise.all(tasks)
   }
+
+  // threshold corresponds to how many video the field should have to be returned
+  static getRandomSamples (threshold: number, count: number): Bluebird<string[]> {
+    const query = 'SELECT tag.name FROM tag ' +
+      'INNER JOIN "videoTag" ON "videoTag"."tagId" = tag.id ' +
+      'INNER JOIN video ON video.id = "videoTag"."videoId" ' +
+      'WHERE video.privacy = $videoPrivacy AND video.state = $videoState ' +
+      'GROUP BY tag.name HAVING COUNT(tag.name) >= $threshold ' +
+      'ORDER BY random() ' +
+      'LIMIT $count'
+
+    const options = {
+      bind: { threshold, count, videoPrivacy: VideoPrivacy.PUBLIC, videoState: VideoState.PUBLISHED },
+      type: Sequelize.QueryTypes.SELECT
+    }
+
+    return TagModel.sequelize.query(query, options)
+                    .then(data => data.map(d => d.name))
+  }
 }
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 3410833c8..695990b17 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -1083,6 +1083,29 @@ export class VideoModel extends Model<VideoModel> {
     })
   }
 
+  // threshold corresponds to how many video the field should have to be returned
+  static getRandomFieldSamples (field: 'category' | 'channelId', threshold: number, count: number) {
+    const query: IFindOptions<VideoModel> = {
+      attributes: [ field ],
+      limit: count,
+      group: field,
+      having: Sequelize.where(Sequelize.fn('COUNT', Sequelize.col(field)), {
+        [Sequelize.Op.gte]: threshold
+      }) as any, // FIXME: typings
+      where: {
+        [field]: {
+          [Sequelize.Op.not]: null,
+        },
+        privacy: VideoPrivacy.PUBLIC,
+        state: VideoState.PUBLISHED
+      },
+      order: [ this.sequelize.random() ]
+    }
+
+    return VideoModel.findAll(query)
+      .then(rows => rows.map(r => r[field]))
+  }
+
   private static buildActorWhereWithFilter (filter?: VideoFilter) {
     if (filter && filter === 'local') {
       return {