diff options
author | Rigel Kent <sendmemail@rigelk.eu> | 2021-01-25 04:32:39 +0100 |
---|---|---|
committer | Chocobozzz <chocobozzz@cpy.re> | 2021-01-28 15:55:34 +0100 |
commit | 28eeb811c40325a28208231324f66f4032e5cf67 (patch) | |
tree | 38b3819b511b311db70c70298cb4da63c207edf3 /server/models | |
parent | 5bcbcbe338ef5a1ed14f084311d013fbb25dabcf (diff) | |
download | PeerTube-28eeb811c40325a28208231324f66f4032e5cf67.tar.gz PeerTube-28eeb811c40325a28208231324f66f4032e5cf67.tar.zst PeerTube-28eeb811c40325a28208231324f66f4032e5cf67.zip |
only count comments from people other than the video author
Diffstat (limited to 'server/models')
-rw-r--r-- | server/models/video/video-query-builder.ts | 25 |
1 files changed, 21 insertions, 4 deletions
diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts index 65b72fe1c..8e0965244 100644 --- a/server/models/video/video-query-builder.ts +++ b/server/models/video/video-query-builder.ts | |||
@@ -268,21 +268,38 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) | |||
268 | like: 3, | 268 | like: 3, |
269 | dislike: 3, | 269 | dislike: 3, |
270 | view: 1 / 12, | 270 | view: 1 / 12, |
271 | comment: 6 | 271 | comment: 2 // a comment takes more time than a like to do, but can be done multiple times |
272 | } | 272 | } |
273 | 273 | ||
274 | joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') | 274 | cte.push( // TODO: exclude blocklisted comments |
275 | '"totalCommentsWithoutVideoAuthor" AS (' + | ||
276 | 'SELECT "video"."id", ' + | ||
277 | 'COUNT("replies"."id") - (' + | ||
278 | 'SELECT COUNT("authorReplies"."id") ' + | ||
279 | 'FROM "videoComment" AS "authorReplies" ' + | ||
280 | 'LEFT JOIN "account" ON "account"."id" = "authorReplies"."accountId" ' + | ||
281 | 'LEFT JOIN "videoChannel" ON "videoChannel"."accountId" = "account"."id" ' + | ||
282 | 'WHERE "video"."channelId" = "videoChannel"."id" ' + | ||
283 | ') as "value" ' + | ||
284 | 'FROM "videoComment" AS "replies" ' + | ||
285 | 'LEFT JOIN "video" ON "video"."id" = "replies"."videoId" ' + | ||
286 | 'WHERE "replies"."videoId" = "video"."id" ' + | ||
287 | 'GROUP BY "video"."id"' + | ||
288 | ')' | ||
289 | ) | ||
290 | |||
291 | joins.push('LEFT JOIN "totalCommentsWithoutVideoAuthor" ON "video"."id" = "totalCommentsWithoutVideoAuthor"."id"') | ||
275 | 292 | ||
276 | attributes.push( | 293 | attributes.push( |
277 | `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) | 294 | `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) |
278 | `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) | 295 | `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) |
279 | `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) | 296 | `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) |
280 | `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id") - 1)) * ${weights.comment} ` + // comments (+) | 297 | `+ LOG(GREATEST(1, "totalCommentsWithoutVideoAuthor"."value")) * ${weights.comment} ` + // comments (+) |
281 | '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days) | 298 | '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days) |
282 | 'AS "score"' | 299 | 'AS "score"' |
283 | ) | 300 | ) |
284 | 301 | ||
285 | group = 'GROUP BY "video"."id"' | 302 | group = 'GROUP BY "video"."id", "totalCommentsWithoutVideoAuthor"."value"' |
286 | } | 303 | } |
287 | 304 | ||
288 | if (options.historyOfUser) { | 305 | if (options.historyOfUser) { |