From 9a629c6efbe39dfac290347670ca41b0d7100f41 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 31 Aug 2018 17:18:13 +0200 Subject: [PATCH] Trending by interval --- .../src/app/shared/video/sort-field.type.ts | 1 + .../video-list/video-trending.component.ts | 2 +- config/default.yaml | 4 ++ config/production.yaml.example | 4 ++ server/initializers/checker.ts | 2 +- server/initializers/constants.ts | 10 ++- server/models/utils.ts | 44 ++++++++++--- server/models/video/video.ts | 63 +++++++++++++++---- server/tests/api/videos/videos-overview.ts | 20 +++--- 9 files changed, 116 insertions(+), 34 deletions(-) diff --git a/client/src/app/shared/video/sort-field.type.ts b/client/src/app/shared/video/sort-field.type.ts index 2192745b9..d1088d244 100644 --- a/client/src/app/shared/video/sort-field.type.ts +++ b/client/src/app/shared/video/sort-field.type.ts @@ -4,3 +4,4 @@ export type VideoSortField = 'name' | '-name' | 'createdAt' | '-createdAt' | 'views' | '-views' | 'likes' | '-likes' + | 'trending' | '-trending' diff --git a/client/src/app/videos/video-list/video-trending.component.ts b/client/src/app/videos/video-list/video-trending.component.ts index 68bb70265..8f3d3842b 100644 --- a/client/src/app/videos/video-list/video-trending.component.ts +++ b/client/src/app/videos/video-list/video-trending.component.ts @@ -18,7 +18,7 @@ import { ScreenService } from '@app/shared/misc/screen.service' export class VideoTrendingComponent extends AbstractVideoList implements OnInit, OnDestroy { titlePage: string currentRoute = '/videos/trending' - defaultSort: VideoSortField = '-views' + defaultSort: VideoSortField = '-trending' constructor ( protected router: Router, diff --git a/config/default.yaml b/config/default.yaml index ef63fbd28..254fa0c99 100644 --- a/config/default.yaml +++ b/config/default.yaml @@ -62,6 +62,10 @@ search: users: true anonymous: false +trending: + videos: + interval_days: 7 # Compute trending videos for the last x days + cache: previews: size: 500 # Max number of previews you want to cache diff --git a/config/production.yaml.example b/config/production.yaml.example index f7b153698..e33427fae 100644 --- a/config/production.yaml.example +++ b/config/production.yaml.example @@ -63,6 +63,10 @@ search: users: true anonymous: false +trending: + videos: + interval_days: 7 # Compute trending videos for the last x days + ############################################################################### # # From this point, all the following keys can be overridden by the web interface diff --git a/server/initializers/checker.ts b/server/initializers/checker.ts index ee02ecf48..b126bf67e 100644 --- a/server/initializers/checker.ts +++ b/server/initializers/checker.ts @@ -52,7 +52,7 @@ function checkMissedConfig () { 'signup.enabled', 'signup.limit', 'signup.requires_email_verification', 'signup.filters.cidr.whitelist', 'signup.filters.cidr.blacklist', 'transcoding.enabled', 'transcoding.threads', - 'import.videos.http.enabled', + 'import.videos.http.enabled', 'import.videos.torrent.enabled', 'instance.name', 'instance.short_description', 'instance.description', 'instance.terms', 'instance.default_client_route', 'instance.default_nsfw_policy', 'instance.robots', 'services.twitter.username', 'services.twitter.whitelisted' diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts index 6d0503f48..efe27a241 100644 --- a/server/initializers/constants.ts +++ b/server/initializers/constants.ts @@ -37,14 +37,15 @@ const SORTABLE_COLUMNS = { JOBS: [ 'createdAt' ], VIDEO_ABUSES: [ 'id', 'createdAt', 'state' ], VIDEO_CHANNELS: [ 'id', 'name', 'updatedAt', 'createdAt' ], - VIDEOS: [ 'name', 'duration', 'createdAt', 'publishedAt', 'views', 'likes' ], VIDEO_IMPORTS: [ 'createdAt' ], VIDEO_COMMENT_THREADS: [ 'createdAt' ], BLACKLISTS: [ 'id', 'name', 'duration', 'views', 'likes', 'dislikes', 'uuid', 'createdAt' ], FOLLOWERS: [ 'createdAt' ], FOLLOWING: [ 'createdAt' ], - VIDEOS_SEARCH: [ 'match', 'name', 'duration', 'createdAt', 'publishedAt', 'views', 'likes' ], + VIDEOS: [ 'name', 'duration', 'createdAt', 'publishedAt', 'views', 'likes', 'trending' ], + + VIDEOS_SEARCH: [ 'name', 'duration', 'createdAt', 'publishedAt', 'views', 'likes', 'match' ], VIDEO_CHANNELS_SEARCH: [ 'match', 'displayName', 'createdAt' ] } @@ -201,6 +202,11 @@ const CONFIG = { ANONYMOUS: config.get('search.remote_uri.anonymous') } }, + TRENDING: { + VIDEOS: { + INTERVAL_DAYS: config.get('trending.videos.interval_days') + } + }, ADMIN: { get EMAIL () { return config.get('admin.email') } }, diff --git a/server/models/utils.ts b/server/models/utils.ts index eb6653f3d..edb8e1161 100644 --- a/server/models/utils.ts +++ b/server/models/utils.ts @@ -1,23 +1,31 @@ -// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ] import { Sequelize } from 'sequelize-typescript' type SortType = { sortModel: any, sortValue: string } +// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ] function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) { - let field: any - let direction: 'ASC' | 'DESC' + const { direction, field } = buildDirectionAndField(value) - if (value.substring(0, 1) === '-') { - direction = 'DESC' - field = value.substring(1) - } else { - direction = 'ASC' - field = value - } + return [ [ field, direction ], lastSort ] +} + +function getVideoSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) { + let { direction, field } = buildDirectionAndField(value) // Alias if (field.toLowerCase() === 'match') field = Sequelize.col('similarity') + // Sort by aggregation + if (field.toLowerCase() === 'trending') { + return [ + [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ], + + [ Sequelize.col('VideoModel.views'), direction ], + + lastSort + ] + } + return [ [ field, direction ], lastSort ] } @@ -58,6 +66,7 @@ function createSimilarityAttribute (col: string, value: string) { export { SortType, getSort, + getVideoSort, getSortOnModel, createSimilarityAttribute, throwIfNotValid, @@ -73,3 +82,18 @@ function searchTrigramNormalizeValue (value: string) { function searchTrigramNormalizeCol (col: string) { return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col))) } + +function buildDirectionAndField (value: string) { + let field: any + let direction: 'ASC' | 'DESC' + + if (value.substring(0, 1) === '-') { + direction = 'DESC' + field = value.substring(1) + } else { + direction = 'ASC' + field = value + } + + return { direction, field } +} diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 67b123d77..6fb5ececa 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -17,6 +17,7 @@ import { HasMany, HasOne, IFindOptions, + IIncludeOptions, Is, IsInt, IsUUID, @@ -24,8 +25,7 @@ import { Model, Scopes, Table, - UpdatedAt, - IIncludeOptions + UpdatedAt } from 'sequelize-typescript' import { VideoPrivacy, VideoResolution, VideoState } from '../../../shared' import { VideoTorrentObject } from '../../../shared/models/activitypub/objects' @@ -77,7 +77,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate' import { ActorModel } from '../activitypub/actor' import { AvatarModel } from '../avatar/avatar' import { ServerModel } from '../server/server' -import { buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils' +import { buildTrigramSearchIndex, createSimilarityAttribute, getVideoSort, throwIfNotValid } from '../utils' import { TagModel } from './tag' import { VideoAbuseModel } from './video-abuse' import { VideoChannelModel } from './video-channel' @@ -89,7 +89,7 @@ import { ScheduleVideoUpdateModel } from './schedule-video-update' import { VideoCaptionModel } from './video-caption' import { VideoBlacklistModel } from './video-blacklist' import { copy, remove, rename, stat, writeFile } from 'fs-extra' -import { immutableAssign } from '../../tests/utils' +import { VideoViewModel } from './video-views' // FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation const indexes: Sequelize.DefineIndexesOptions[] = [ @@ -146,6 +146,7 @@ type AvailableForListIDsOptions = { withFiles?: boolean accountId?: number videoChannelId?: number + trendingDays?: number } @Scopes({ @@ -384,6 +385,21 @@ type AvailableForListIDsOptions = { } } + if (options.trendingDays) { + query.include.push({ + attributes: [], + model: VideoViewModel, + required: false, + where: { + startDate: { + [ Sequelize.Op.gte ]: new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays) + } + } + }) + + query.subQuery = false + } + return query }, [ScopeNames.WITH_ACCOUNT_DETAILS]: { @@ -649,6 +665,16 @@ export class VideoModel extends Model { }) VideoComments: VideoCommentModel[] + @HasMany(() => VideoViewModel, { + foreignKey: { + name: 'videoId', + allowNull: false + }, + onDelete: 'cascade', + hooks: true + }) + VideoViews: VideoViewModel[] + @HasOne(() => ScheduleVideoUpdateModel, { foreignKey: { name: 'videoId', @@ -754,7 +780,7 @@ export class VideoModel extends Model { distinct: true, offset: start, limit: count, - order: getSort('createdAt', [ 'Tags', 'name', 'ASC' ]), + order: getVideoSort('createdAt', [ 'Tags', 'name', 'ASC' ]), where: { id: { [Sequelize.Op.in]: Sequelize.literal('(' + rawQuery + ')') @@ -845,7 +871,7 @@ export class VideoModel extends Model { const query: IFindOptions = { offset: start, limit: count, - order: getSort(sort), + order: getVideoSort(sort), include: [ { model: VideoChannelModel, @@ -902,11 +928,19 @@ export class VideoModel extends Model { accountId?: number, videoChannelId?: number, actorId?: number + trendingDays?: number }) { - const query = { + const query: IFindOptions = { offset: options.start, limit: options.count, - order: getSort(options.sort) + order: getVideoSort(options.sort) + } + + let trendingDays: number + if (options.sort.endsWith('trending')) { + trendingDays = CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS + + query.group = 'VideoModel.id' } // actorId === null has a meaning, so just check undefined @@ -924,7 +958,8 @@ export class VideoModel extends Model { withFiles: options.withFiles, accountId: options.accountId, videoChannelId: options.videoChannelId, - includeLocalVideos: options.includeLocalVideos + includeLocalVideos: options.includeLocalVideos, + trendingDays } return VideoModel.getAvailableForApi(query, queryOptions) @@ -1006,7 +1041,7 @@ export class VideoModel extends Model { }, offset: options.start, limit: options.count, - order: getSort(options.sort), + order: getVideoSort(options.sort), where: { [ Sequelize.Op.and ]: whereAnd } @@ -1177,8 +1212,12 @@ export class VideoModel extends Model { const secondQuery = { offset: 0, limit: query.limit, - order: query.order, - attributes: query.attributes + attributes: query.attributes, + order: [ // Keep original order + Sequelize.literal( + ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ') + ) + ] } const rows = await VideoModel.scope(apiScope).findAll(secondQuery) diff --git a/server/tests/api/videos/videos-overview.ts b/server/tests/api/videos/videos-overview.ts index 1514d1bda..7d1f29c92 100644 --- a/server/tests/api/videos/videos-overview.ts +++ b/server/tests/api/videos/videos-overview.ts @@ -30,8 +30,10 @@ describe('Test a videos overview', function () { expect(overview.channels).to.have.lengthOf(0) }) - it('Should upload 3 videos in a specific category, tag and channel but not include them in overview', async function () { - for (let i = 0; i < 3; i++) { + it('Should upload 5 videos in a specific category, tag and channel but not include them in overview', async function () { + this.timeout(15000) + + for (let i = 0; i < 5; i++) { await uploadVideo(server.url, server.accessToken, { name: 'video ' + i, category: 3, @@ -49,7 +51,7 @@ describe('Test a videos overview', function () { it('Should upload another video and include all videos in the overview', async function () { await uploadVideo(server.url, server.accessToken, { - name: 'video 3', + name: 'video 5', category: 3, tags: [ 'coucou1', 'coucou2' ] }) @@ -70,11 +72,13 @@ describe('Test a videos overview', function () { for (const attr of [ 'tags', 'categories', 'channels' ]) { const obj = overview[attr][0] - expect(obj.videos).to.have.lengthOf(4) - expect(obj.videos[0].name).to.equal('video 3') - expect(obj.videos[1].name).to.equal('video 2') - expect(obj.videos[2].name).to.equal('video 1') - expect(obj.videos[3].name).to.equal('video 0') + expect(obj.videos).to.have.lengthOf(6) + expect(obj.videos[0].name).to.equal('video 5') + expect(obj.videos[1].name).to.equal('video 4') + expect(obj.videos[2].name).to.equal('video 3') + expect(obj.videos[3].name).to.equal('video 2') + expect(obj.videos[4].name).to.equal('video 1') + expect(obj.videos[5].name).to.equal('video 0') } expect(overview.tags.find(t => t.tag === 'coucou1')).to.not.be.undefined -- 2.25.1