aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
diff options
context:
space:
mode:
authorRigel Kent <sendmemail@rigelk.eu>2020-03-23 10:14:05 +0100
committerChocobozzz <chocobozzz@cpy.re>2020-03-31 10:29:24 +0200
commit8165d00ac6263cf3c0d61d450960ef36635084ff (patch)
treec0587121cd8dbdfc246a5bc74c08805830140a77 /server/models
parent628c155338cf106365a06ca021b9f244b784c003 (diff)
downloadPeerTube-8165d00ac6263cf3c0d61d450960ef36635084ff.tar.gz
PeerTube-8165d00ac6263cf3c0d61d450960ef36635084ff.tar.zst
PeerTube-8165d00ac6263cf3c0d61d450960ef36635084ff.zip
View stats for channels
Diffstat (limited to 'server/models')
-rw-r--r--server/models/video/video-channel.ts147
1 files changed, 105 insertions, 42 deletions
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 835216671..128915af3 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -30,7 +30,7 @@ import { buildServerIdsFollowedBy, buildTrigramSearchIndex, createSimilarityAttr
30import { VideoModel } from './video' 30import { VideoModel } from './video'
31import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants' 31import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
32import { ServerModel } from '../server/server' 32import { ServerModel } from '../server/server'
33import { FindOptions, Op } from 'sequelize' 33import { FindOptions, Op, literal, ScopeOptions } from 'sequelize'
34import { AvatarModel } from '../avatar/avatar' 34import { AvatarModel } from '../avatar/avatar'
35import { VideoPlaylistModel } from './video-playlist' 35import { VideoPlaylistModel } from './video-playlist'
36import * as Bluebird from 'bluebird' 36import * as Bluebird from 'bluebird'
@@ -45,16 +45,21 @@ import {
45 45
46export enum ScopeNames { 46export enum ScopeNames {
47 FOR_API = 'FOR_API', 47 FOR_API = 'FOR_API',
48 SUMMARY = 'SUMMARY',
48 WITH_ACCOUNT = 'WITH_ACCOUNT', 49 WITH_ACCOUNT = 'WITH_ACCOUNT',
49 WITH_ACTOR = 'WITH_ACTOR', 50 WITH_ACTOR = 'WITH_ACTOR',
50 WITH_VIDEOS = 'WITH_VIDEOS', 51 WITH_VIDEOS = 'WITH_VIDEOS',
51 SUMMARY = 'SUMMARY' 52 WITH_STATS = 'WITH_STATS'
52} 53}
53 54
54type AvailableForListOptions = { 55type AvailableForListOptions = {
55 actorId: number 56 actorId: number
56} 57}
57 58
59type AvailableWithStatsOptions = {
60 daysPrior: number
61}
62
58export type SummaryOptions = { 63export type SummaryOptions = {
59 withAccount?: boolean // Default: false 64 withAccount?: boolean // Default: false
60 withAccountBlockerIds?: number[] 65 withAccountBlockerIds?: number[]
@@ -69,40 +74,6 @@ export type SummaryOptions = {
69 ] 74 ]
70})) 75}))
71@Scopes(() => ({ 76@Scopes(() => ({
72 [ScopeNames.SUMMARY]: (options: SummaryOptions = {}) => {
73 const base: FindOptions = {
74 attributes: [ 'id', 'name', 'description', 'actorId' ],
75 include: [
76 {
77 attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
78 model: ActorModel.unscoped(),
79 required: true,
80 include: [
81 {
82 attributes: [ 'host' ],
83 model: ServerModel.unscoped(),
84 required: false
85 },
86 {
87 model: AvatarModel.unscoped(),
88 required: false
89 }
90 ]
91 }
92 ]
93 }
94
95 if (options.withAccount === true) {
96 base.include.push({
97 model: AccountModel.scope({
98 method: [ AccountModelScopeNames.SUMMARY, { withAccountBlockerIds: options.withAccountBlockerIds } as AccountSummaryOptions ]
99 }),
100 required: true
101 })
102 }
103
104 return base
105 },
106 [ScopeNames.FOR_API]: (options: AvailableForListOptions) => { 77 [ScopeNames.FOR_API]: (options: AvailableForListOptions) => {
107 // Only list local channels OR channels that are on an instance followed by actorId 78 // Only list local channels OR channels that are on an instance followed by actorId
108 const inQueryInstanceFollow = buildServerIdsFollowedBy(options.actorId) 79 const inQueryInstanceFollow = buildServerIdsFollowedBy(options.actorId)
@@ -143,6 +114,40 @@ export type SummaryOptions = {
143 ] 114 ]
144 } 115 }
145 }, 116 },
117 [ScopeNames.SUMMARY]: (options: SummaryOptions = {}) => {
118 const base: FindOptions = {
119 attributes: [ 'id', 'name', 'description', 'actorId' ],
120 include: [
121 {
122 attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
123 model: ActorModel.unscoped(),
124 required: true,
125 include: [
126 {
127 attributes: [ 'host' ],
128 model: ServerModel.unscoped(),
129 required: false
130 },
131 {
132 model: AvatarModel.unscoped(),
133 required: false
134 }
135 ]
136 }
137 ]
138 }
139
140 if (options.withAccount === true) {
141 base.include.push({
142 model: AccountModel.scope({
143 method: [ AccountModelScopeNames.SUMMARY, { withAccountBlockerIds: options.withAccountBlockerIds } as AccountSummaryOptions ]
144 }),
145 required: true
146 })
147 }
148
149 return base
150 },
146 [ScopeNames.WITH_ACCOUNT]: { 151 [ScopeNames.WITH_ACCOUNT]: {
147 include: [ 152 include: [
148 { 153 {
@@ -151,16 +156,52 @@ export type SummaryOptions = {
151 } 156 }
152 ] 157 ]
153 }, 158 },
154 [ScopeNames.WITH_VIDEOS]: { 159 [ScopeNames.WITH_ACTOR]: {
155 include: [ 160 include: [
156 VideoModel 161 ActorModel
157 ] 162 ]
158 }, 163 },
159 [ScopeNames.WITH_ACTOR]: { 164 [ScopeNames.WITH_VIDEOS]: {
160 include: [ 165 include: [
161 ActorModel 166 VideoModel
162 ] 167 ]
163 } 168 },
169 [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => ({
170 attributes: {
171 include: [
172 [
173 literal(
174 '(' +
175 `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` +
176 'FROM ( ' +
177 'WITH ' +
178 'days AS ( ' +
179 `SELECT generate_series(date_trunc('day', now()) - '${options.daysPrior} day'::interval, ` +
180 `date_trunc('day', now()), '1 day'::interval) AS day ` +
181 '), ' +
182 'views AS ( ' +
183 'SELECT * ' +
184 'FROM "videoView" ' +
185 'WHERE "videoView"."videoId" IN ( ' +
186 'SELECT "video"."id" ' +
187 'FROM "video" ' +
188 'WHERE "video"."channelId" = "VideoChannelModel"."id" ' +
189 ') ' +
190 ') ' +
191 'SELECT days.day AS day, ' +
192 'COALESCE(SUM(views.views), 0) AS views ' +
193 'FROM days ' +
194 `LEFT JOIN views ON date_trunc('day', "views"."createdAt") = days.day ` +
195 'GROUP BY 1 ' +
196 'ORDER BY day ' +
197 ') t' +
198 ')'
199 ),
200 'viewsPerDay'
201 ]
202 ]
203 }
204 })
164})) 205}))
165@Table({ 206@Table({
166 tableName: 'videoChannel', 207 tableName: 'videoChannel',
@@ -352,6 +393,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
352 start: number 393 start: number
353 count: number 394 count: number
354 sort: string 395 sort: string
396 withStats?: boolean
355 }) { 397 }) {
356 const query = { 398 const query = {
357 offset: options.start, 399 offset: options.start,
@@ -368,7 +410,17 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
368 ] 410 ]
369 } 411 }
370 412
413 const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ]
414
415 options.withStats = true // TODO: remove beyond after initial tests
416 if (options.withStats) {
417 scopes.push({
418 method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
419 })
420 }
421
371 return VideoChannelModel 422 return VideoChannelModel
423 .scope(scopes)
372 .findAndCountAll(query) 424 .findAndCountAll(query)
373 .then(({ rows, count }) => { 425 .then(({ rows, count }) => {
374 return { total: count, data: rows } 426 return { total: count, data: rows }
@@ -496,6 +548,8 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
496 } 548 }
497 549
498 toFormattedJSON (this: MChannelFormattable): VideoChannel { 550 toFormattedJSON (this: MChannelFormattable): VideoChannel {
551 const viewsPerDay = this.get('viewsPerDay') as string
552
499 const actor = this.Actor.toFormattedJSON() 553 const actor = this.Actor.toFormattedJSON()
500 const videoChannel = { 554 const videoChannel = {
501 id: this.id, 555 id: this.id,
@@ -505,7 +559,16 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
505 isLocal: this.Actor.isOwned(), 559 isLocal: this.Actor.isOwned(),
506 createdAt: this.createdAt, 560 createdAt: this.createdAt,
507 updatedAt: this.updatedAt, 561 updatedAt: this.updatedAt,
508 ownerAccount: undefined 562 ownerAccount: undefined,
563 viewsPerDay: viewsPerDay !== undefined
564 ? viewsPerDay.split(',').map(v => {
565 const o = v.split('|')
566 return {
567 date: new Date(o[0]),
568 views: +o[1]
569 }
570 })
571 : undefined
509 } 572 }
510 573
511 if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON() 574 if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON()