aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2020-06-12 16:01:42 +0200
committerChocobozzz <me@florianbigard.com>2020-06-12 16:27:41 +0200
commit5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9 (patch)
tree9bc4788c0b54d7386d5533589c6cf52c347c0a48 /server/models
parente74bda21d15300b4653392957e730c5e4eff2af3 (diff)
downloadPeerTube-5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9.tar.gz
PeerTube-5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9.tar.zst
PeerTube-5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9.zip
Optimize views per day in video channels
Diffstat (limited to 'server/models')
-rw-r--r--server/models/video/video-channel.ts26
1 files changed, 11 insertions, 15 deletions
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 642e129ff..b5bcbdc65 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -181,20 +181,16 @@ export type SummaryOptions = {
181 'days AS ( ' + 181 'days AS ( ' +
182 `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` + 182 `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` +
183 `date_trunc('day', now()), '1 day'::interval) AS day ` + 183 `date_trunc('day', now()), '1 day'::interval) AS day ` +
184 '), ' +
185 'views AS ( ' +
186 'SELECT v.* ' +
187 'FROM "videoView" AS v ' +
188 'INNER JOIN "video" ON "video"."id" = v."videoId" ' +
189 'WHERE "video"."channelId" = "VideoChannelModel"."id" ' +
190 ') ' + 184 ') ' +
191 'SELECT days.day AS day, ' + 185 'SELECT days.day AS day, COALESCE(SUM("videoView".views), 0) AS views ' +
192 'COALESCE(SUM(views.views), 0) AS views ' + 186 'FROM days ' +
193 'FROM days ' + 187 'LEFT JOIN (' +
194 `LEFT JOIN views ON date_trunc('day', "views"."startDate") = date_trunc('day', days.day) ` + 188 '"videoView" INNER JOIN "video" ON "videoView"."videoId" = "video"."id" ' +
195 'GROUP BY day ' + 189 'AND "video"."channelId" = "VideoChannelModel"."id"' +
196 'ORDER BY day ' + 190 `) ON date_trunc('day', "videoView"."startDate") = date_trunc('day', days.day) ` +
197 ') t' + 191 'GROUP BY day ' +
192 'ORDER BY day ' +
193 ') t' +
198 ')' 194 ')'
199 ), 195 ),
200 'viewsPerDay' 196 'viewsPerDay'
@@ -413,7 +409,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
413 409
414 const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ] 410 const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ]
415 411
416 if (options.withStats) { 412 if (options.withStats === true) {
417 scopes.push({ 413 scopes.push({
418 method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ] 414 method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
419 }) 415 })
@@ -560,7 +556,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
560 createdAt: this.createdAt, 556 createdAt: this.createdAt,
561 updatedAt: this.updatedAt, 557 updatedAt: this.updatedAt,
562 ownerAccount: undefined, 558 ownerAccount: undefined,
563 viewsPerDay: viewsPerDay !== undefined 559 viewsPerDay: viewsPerDay
564 ? viewsPerDay.split(',').map(v => { 560 ? viewsPerDay.split(',').map(v => {
565 const o = v.split('|') 561 const o = v.split('|')
566 return { 562 return {