aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorRigel Kent <sendmemail@rigelk.eu>2020-03-30 12:06:46 +0200
committerChocobozzz <chocobozzz@cpy.re>2020-03-31 10:29:24 +0200
commit3d527ba173a37bd61ec8ad742642bb320d12995c (patch)
tree4b2890df00b64ff6cdcf96afb652af8abcac3ff5 /server
parent714bfcc556177dce2b65a1e58babdf2488e9de13 (diff)
downloadPeerTube-3d527ba173a37bd61ec8ad742642bb320d12995c.tar.gz
PeerTube-3d527ba173a37bd61ec8ad742642bb320d12995c.tar.zst
PeerTube-3d527ba173a37bd61ec8ad742642bb320d12995c.zip
Use inner join and document code for viewr stats for channels
Diffstat (limited to 'server')
-rw-r--r--server/models/video/video-channel.ts65
-rw-r--r--server/tests/api/videos/video-channels.ts4
2 files changed, 35 insertions, 34 deletions
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 78fc3d7e4..642e129ff 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -166,42 +166,43 @@ export type SummaryOptions = {
166 VideoModel 166 VideoModel
167 ] 167 ]
168 }, 168 },
169 [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => ({ 169 [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => {
170 attributes: { 170 const daysPrior = parseInt(options.daysPrior + '', 10)
171 include: [ 171
172 [ 172 return {
173 literal( 173 attributes: {
174 '(' + 174 include: [
175 `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` + 175 [
176 'FROM ( ' + 176 literal(
177 'WITH ' + 177 '(' +
178 'days AS ( ' + 178 `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` +
179 `SELECT generate_series(date_trunc('day', now()) - '${options.daysPrior} day'::interval, ` + 179 'FROM ( ' +
180 `date_trunc('day', now()), '1 day'::interval) AS day ` + 180 'WITH ' +
181 '), ' + 181 'days AS ( ' +
182 'views AS ( ' + 182 `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` +
183 'SELECT * ' + 183 `date_trunc('day', now()), '1 day'::interval) AS day ` +
184 'FROM "videoView" ' + 184 '), ' +
185 'WHERE "videoView"."videoId" IN ( ' + 185 'views AS ( ' +
186 'SELECT "video"."id" ' + 186 'SELECT v.* ' +
187 'FROM "video" ' + 187 'FROM "videoView" AS v ' +
188 'INNER JOIN "video" ON "video"."id" = v."videoId" ' +
188 'WHERE "video"."channelId" = "VideoChannelModel"."id" ' + 189 'WHERE "video"."channelId" = "VideoChannelModel"."id" ' +
189 ') ' + 190 ') ' +
190 ') ' + 191 'SELECT days.day AS day, ' +
191 'SELECT days.day AS day, ' + 192 'COALESCE(SUM(views.views), 0) AS views ' +
192 'COALESCE(SUM(views.views), 0) AS views ' + 193 'FROM days ' +
193 'FROM days ' + 194 `LEFT JOIN views ON date_trunc('day', "views"."startDate") = date_trunc('day', days.day) ` +
194 `LEFT JOIN views ON date_trunc('day', "views"."startDate") = date_trunc('day', days.day) ` + 195 'GROUP BY day ' +
195 'GROUP BY 1 ' + 196 'ORDER BY day ' +
196 'ORDER BY day ' + 197 ') t' +
197 ') t' + 198 ')'
198 ')' 199 ),
199 ), 200 'viewsPerDay'
200 'viewsPerDay' 201 ]
201 ] 202 ]
202 ] 203 }
203 } 204 }
204 }) 205 }
205})) 206}))
206@Table({ 207@Table({
207 tableName: 'videoChannel', 208 tableName: 'videoChannel',
diff --git a/server/tests/api/videos/video-channels.ts b/server/tests/api/videos/video-channels.ts
index bde45584d..876a6ab66 100644
--- a/server/tests/api/videos/video-channels.ts
+++ b/server/tests/api/videos/video-channels.ts
@@ -2,7 +2,7 @@
2 2
3import * as chai from 'chai' 3import * as chai from 'chai'
4import 'mocha' 4import 'mocha'
5import { User, Video, VideoChannel, viewsPerTime, VideoDetails } from '../../../../shared/index' 5import { User, Video, VideoChannel, ViewsPerDate, VideoDetails } from '../../../../shared/index'
6import { 6import {
7 cleanupTests, 7 cleanupTests,
8 createUser, 8 createUser,
@@ -376,7 +376,7 @@ describe('Test video channels', function () {
376 res.body.data.forEach((channel: VideoChannel) => { 376 res.body.data.forEach((channel: VideoChannel) => {
377 expect(channel).to.haveOwnProperty('viewsPerDay') 377 expect(channel).to.haveOwnProperty('viewsPerDay')
378 expect(channel.viewsPerDay).to.have.length(30 + 1) // daysPrior + today 378 expect(channel.viewsPerDay).to.have.length(30 + 1) // daysPrior + today
379 channel.viewsPerDay.forEach((v: viewsPerTime) => { 379 channel.viewsPerDay.forEach((v: ViewsPerDate) => {
380 expect(v.date).to.be.an('string') 380 expect(v.date).to.be.an('string')
381 expect(v.views).to.equal(0) 381 expect(v.views).to.equal(0)
382 }) 382 })