From 9a629c6efbe39dfac290347670ca41b0d7100f41 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 31 Aug 2018 17:18:13 +0200 Subject: Trending by interval --- server/models/utils.ts | 44 ++++++++++++++++++++++++------- server/models/video/video.ts | 63 +++++++++++++++++++++++++++++++++++--------- 2 files changed, 85 insertions(+), 22 deletions(-) (limited to 'server/models') 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) -- cgit v1.2.3