aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/view
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2022-05-05 14:12:57 +0200
committerChocobozzz <me@florianbigard.com>2022-05-05 14:13:14 +0200
commit49f0468d44468528c2fb2c8b0efd19cdaeeec43d (patch)
tree4fd07d5da74506729a1cbbe67548bfd9c6c76849 /server/models/view
parentf18a060a83d7053897173b2a24fb7984893131c7 (diff)
downloadPeerTube-49f0468d44468528c2fb2c8b0efd19cdaeeec43d.tar.gz
PeerTube-49f0468d44468528c2fb2c8b0efd19cdaeeec43d.tar.zst
PeerTube-49f0468d44468528c2fb2c8b0efd19cdaeeec43d.zip
Add filter by start/end date overall stats in api
Diffstat (limited to 'server/models/view')
-rw-r--r--server/models/view/local-video-viewer.ts36
1 files changed, 27 insertions, 9 deletions
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 5928ba5f6..2862f8b96 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -100,10 +100,28 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
100 }) 100 })
101 } 101 }
102 102
103 static async getOverallStats (video: MVideo): Promise<VideoStatsOverall> { 103 static async getOverallStats (options: {
104 const options = { 104 video: MVideo
105 startDate?: string
106 endDate?: string
107 }): Promise<VideoStatsOverall> {
108 const { video, startDate, endDate } = options
109
110 const queryOptions = {
105 type: QueryTypes.SELECT as QueryTypes.SELECT, 111 type: QueryTypes.SELECT as QueryTypes.SELECT,
106 replacements: { videoId: video.id } 112 replacements: { videoId: video.id } as any
113 }
114
115 let dateWhere = ''
116
117 if (startDate) {
118 dateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
119 queryOptions.replacements.startDate = startDate
120 }
121
122 if (endDate) {
123 dateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
124 queryOptions.replacements.endDate = endDate
107 } 125 }
108 126
109 const watchTimeQuery = `SELECT ` + 127 const watchTimeQuery = `SELECT ` +
@@ -111,9 +129,9 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
111 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` + 129 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
112 `FROM "localVideoViewer" ` + 130 `FROM "localVideoViewer" ` +
113 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` + 131 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
114 `WHERE "videoId" = :videoId` 132 `WHERE "videoId" = :videoId ${dateWhere}`
115 133
116 const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, options) 134 const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
117 135
118 const watchPeakQuery = `WITH "watchPeakValues" AS ( 136 const watchPeakQuery = `WITH "watchPeakValues" AS (
119 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc" 137 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
@@ -122,7 +140,7 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
122 UNION ALL 140 UNION ALL
123 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc" 141 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
124 FROM "localVideoViewer" 142 FROM "localVideoViewer"
125 WHERE "videoId" = :videoId 143 WHERE "videoId" = :videoId ${dateWhere}
126 ) 144 )
127 SELECT "dateBreakpoint", "concurrent" 145 SELECT "dateBreakpoint", "concurrent"
128 FROM ( 146 FROM (
@@ -132,14 +150,14 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
132 ) tmp 150 ) tmp
133 ORDER BY "concurrent" DESC 151 ORDER BY "concurrent" DESC
134 FETCH FIRST 1 ROW ONLY` 152 FETCH FIRST 1 ROW ONLY`
135 const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, options) 153 const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
136 154
137 const countriesQuery = `SELECT country, COUNT(country) as viewers ` + 155 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
138 `FROM "localVideoViewer" ` + 156 `FROM "localVideoViewer" ` +
139 `WHERE "videoId" = :videoId AND country IS NOT NULL ` + 157 `WHERE "videoId" = :videoId AND country IS NOT NULL ${dateWhere} ` +
140 `GROUP BY country ` + 158 `GROUP BY country ` +
141 `ORDER BY viewers DESC` 159 `ORDER BY viewers DESC`
142 const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, options) 160 const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
143 161
144 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([ 162 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
145 watchTimePromise, 163 watchTimePromise,