aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/view
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2023-02-15 09:02:34 +0100
committerChocobozzz <me@florianbigard.com>2023-02-15 09:02:34 +0100
commitfd7581b036d9e3580e2e0b1e20d7e4c771d8ff83 (patch)
tree0af4d61a1434515ca3da1065e5ae90f216030d36 /server/models/view
parent20321f2049f46a7bfaadcc5da2f1eb53b9bb4d76 (diff)
downloadPeerTube-fd7581b036d9e3580e2e0b1e20d7e4c771d8ff83.tar.gz
PeerTube-fd7581b036d9e3580e2e0b1e20d7e4c771d8ff83.tar.zst
PeerTube-fd7581b036d9e3580e2e0b1e20d7e4c771d8ff83.zip
Fix "unique viewers" inconsistency with countries
Diffstat (limited to 'server/models/view')
-rw-r--r--server/models/view/local-video-viewer.ts25
1 files changed, 20 insertions, 5 deletions
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 274117e86..c7ac51a03 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -119,18 +119,32 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
119 if (startDate) queryOptions.replacements.startDate = startDate 119 if (startDate) queryOptions.replacements.startDate = startDate
120 if (endDate) queryOptions.replacements.endDate = endDate 120 if (endDate) queryOptions.replacements.endDate = endDate
121 121
122 const buildTotalViewersPromise = () => {
123 let totalViewersDateWhere = ''
124
125 if (startDate) totalViewersDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
126 if (endDate) totalViewersDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
127
128 const totalViewersQuery = `SELECT ` +
129 `COUNT("localVideoViewer"."id") AS "totalViewers" ` +
130 `FROM "localVideoViewer" ` +
131 `WHERE "videoId" = :videoId ${totalViewersDateWhere}`
132
133 return LocalVideoViewerModel.sequelize.query<any>(totalViewersQuery, queryOptions)
134 }
135
122 const buildWatchTimePromise = () => { 136 const buildWatchTimePromise = () => {
123 let watchTimeDateWhere = '' 137 let watchTimeDateWhere = ''
124 138
139 // We know this where is not exact
140 // But we prefer to take into account only watch section that started and ended **in** the interval
125 if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate' 141 if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
126 if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate' 142 if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
127 143
128 const watchTimeQuery = `SELECT ` + 144 const watchTimeQuery = `SELECT ` +
129 `COUNT("localVideoViewer"."id") AS "totalViewers", ` +
130 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` + 145 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
131 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` + 146 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
132 `FROM "localVideoViewer" ` + 147 `FROM "localVideoViewer" ` +
133 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
134 `WHERE "videoId" = :videoId ${watchTimeDateWhere}` 148 `WHERE "videoId" = :videoId ${watchTimeDateWhere}`
135 149
136 return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions) 150 return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
@@ -194,7 +208,8 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
194 return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions) 208 return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
195 } 209 }
196 210
197 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([ 211 const [ rowsTotalViewers, rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
212 buildTotalViewersPromise(),
198 buildWatchTimePromise(), 213 buildWatchTimePromise(),
199 buildWatchPeakPromise(), 214 buildWatchPeakPromise(),
200 buildCountriesPromise() 215 buildCountriesPromise()
@@ -212,8 +227,8 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
212 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0 227 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
213 : 0, 228 : 0,
214 229
215 totalViewers: rowsWatchTime.length !== 0 230 totalViewers: rowsTotalViewers.length !== 0
216 ? Math.round(rowsWatchTime[0].totalViewers) || 0 231 ? Math.round(rowsTotalViewers[0].totalViewers) || 0
217 : 0, 232 : 0,
218 233
219 viewersPeak, 234 viewersPeak,