server/models/video/tag.ts

   1 import { col, fn, QueryTypes, Transaction } from 'sequelize'
   2 import { AllowNull, BelongsToMany, Column, CreatedAt, Is, Model, Table, UpdatedAt } from 'sequelize-typescript'
   3 import { MTag } from '@server/types/models'
   4 import { VideoPrivacy, VideoState } from '../../../shared/models/videos'
   5 import { isVideoTagValid } from '../../helpers/custom-validators/videos'
   6 import { throwIfNotValid } from '../utils'
   7 import { VideoModel } from './video'
   8 import { VideoTagModel } from './video-tag'
   9
  10 @Table({
  11   tableName: 'tag',
  12   timestamps: false,
  13   indexes: [
  14     {
  15       fields: [ 'name' ],
  16       unique: true
  17     },
  18     {
  19       name: 'tag_lower_name',
  20       fields: [ fn('lower', col('name')) ] as any // FIXME: typings
  21     }
  22   ]
  23 })
  24 export class TagModel extends Model {
  25
  26   @AllowNull(false)
  27   @Is('VideoTag', value => throwIfNotValid(value, isVideoTagValid, 'tag'))
  28   @Column
  29   name: string
  30
  31   @CreatedAt
  32   createdAt: Date
  33
  34   @UpdatedAt
  35   updatedAt: Date
  36
  37   @BelongsToMany(() => VideoModel, {
  38     foreignKey: 'tagId',
  39     through: () => VideoTagModel,
  40     onDelete: 'CASCADE'
  41   })
  42   Videos: VideoModel[]
  43
  44   static findOrCreateTags (tags: string[], transaction: Transaction): Promise<MTag[]> {
  45     if (tags === null) return Promise.resolve([])
  46
  47     const tasks: Promise<MTag>[] = []
  48     tags.forEach(tag => {
  49       const query = {
  50         where: {
  51           name: tag
  52         },
  53         defaults: {
  54           name: tag
  55         },
  56         transaction
  57       }
  58
  59       const promise = TagModel.findOrCreate<MTag>(query)
  60         .then(([ tagInstance ]) => tagInstance)
  61       tasks.push(promise)
  62     })
  63
  64     return Promise.all(tasks)
  65   }
  66
  67   // threshold corresponds to how many video the field should have to be returned
  68   static getRandomSamples (threshold: number, count: number): Promise<string[]> {
  69     const query = 'SELECT tag.name FROM tag ' +
  70       'INNER JOIN "videoTag" ON "videoTag"."tagId" = tag.id ' +
  71       'INNER JOIN video ON video.id = "videoTag"."videoId" ' +
  72       'WHERE video.privacy = $videoPrivacy AND video.state = $videoState ' +
  73       'GROUP BY tag.name HAVING COUNT(tag.name) >= $threshold ' +
  74       'ORDER BY random() ' +
  75       'LIMIT $count'
  76
  77     const options = {
  78       bind: { threshold, count, videoPrivacy: VideoPrivacy.PUBLIC, videoState: VideoState.PUBLISHED },
  79       type: QueryTypes.SELECT as QueryTypes.SELECT
  80     }
  81
  82     return TagModel.sequelize.query<{ name: string }>(query, options)
  83                     .then(data => data.map(d => d.name))
  84   }
  85 }