X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;ds=sidebyside;f=server%2Fmodels%2Fvideo%2Fvideo-query-builder.ts;h=155afe64be00ce66bbc7db1f4a5c51ba0c3d6224;hb=be89e66895948bb52b3f10186bcea1a8b92ef912;hp=65b72fe1c8fd732b5399c82d1a37e38cf1bc66d0;hpb=5bcbcbe338ef5a1ed14f084311d013fbb25dabcf;p=github%2FChocobozzz%2FPeerTube.git diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts index 65b72fe1c..155afe64b 100644 --- a/server/models/video/video-query-builder.ts +++ b/server/models/video/video-query-builder.ts @@ -16,9 +16,11 @@ export type BuildVideosQueryOptions = { start: number sort: string + nsfw?: boolean filter?: VideoFilter + isLive?: boolean + categoryOneOf?: number[] - nsfw?: boolean licenceOneOf?: number[] languageOneOf?: string[] tagsOneOf?: string[] @@ -31,8 +33,8 @@ export type BuildVideosQueryOptions = { videoPlaylistId?: number + trendingAlgorithm?: string // best, hot, or any other algorithm implemented trendingDays?: number - hot?: boolean user?: MUserAccountId historyOfUser?: MUserId @@ -199,10 +201,14 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) if (options.nsfw === true) { and.push('"video"."nsfw" IS TRUE') + } else if (options.nsfw === false) { + and.push('"video"."nsfw" IS FALSE') } - if (options.nsfw === false) { - and.push('"video"."nsfw" IS FALSE') + if (options.isLive === true) { + and.push('"video"."isLive" IS TRUE') + } else if (options.isLive === false) { + and.push('"video"."isLive" IS FALSE') } if (options.categoryOneOf) { @@ -242,51 +248,67 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) } // We don't exclude results in this so if we do a count we don't need to add this complex clause - if (options.trendingDays && options.isCount !== true) { - const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays) - - joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') - replacements.viewsGteDate = viewsGteDate - - attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"') - - group = 'GROUP BY "video"."id"' - } else if (options.hot && options.isCount !== true) { - /** - * "Hotness" is a measure based on absolute view/comment/like/dislike numbers, - * with fixed weights only applied to their log values. - * - * This algorithm gives little chance for an old video to have a good score, - * for which recent spikes in interactions could be a sign of "hotness" and - * justify a better score. However there are multiple ways to achieve that - * goal, which is left for later. Yes, this is a TODO :) - * - * note: weights and base score are in number of half-days. - * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58 - */ - const weights = { - like: 3, - dislike: 3, - view: 1 / 12, - comment: 6 - } + if (options.isCount !== true) { + if (options.trendingDays) { + const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays) + + joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') + replacements.viewsGteDate = viewsGteDate + + attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"') + + group = 'GROUP BY "video"."id"' + } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) { + /** + * "Hotness" is a measure based on absolute view/comment/like/dislike numbers, + * with fixed weights only applied to their log values. + * + * This algorithm gives little chance for an old video to have a good score, + * for which recent spikes in interactions could be a sign of "hotness" and + * justify a better score. However there are multiple ways to achieve that + * goal, which is left for later. Yes, this is a TODO :) + * + * notes: + * - weights and base score are in number of half-days. + * - all comments are counted, regardless of being written by the video author or not + * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58 + * - we have less interactions than on reddit, so multiply weights by an arbitrary factor + */ + const weights = { + like: 3 * 50, + dislike: -3 * 50, + view: Math.floor((1 / 3) * 50), + comment: 2 * 50, // a comment takes more time than a like to do, but can be done multiple times + history: -2 * 50 + } - joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') + joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') - attributes.push( - `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) - `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) - `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) - `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id") - 1)) * ${weights.comment} ` + // comments (+) - '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days) - 'AS "score"' - ) + let attribute = + `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) + `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) + `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) + `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+) + '+ (SELECT (EXTRACT(epoch FROM "video"."publishedAt") - 1446156582) / 47000) ' // base score (in number of half-days) - group = 'GROUP BY "video"."id"' + if (options.trendingAlgorithm === 'best' && options.user) { + joins.push( + 'LEFT JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :bestUser' + ) + replacements.bestUser = options.user.id + + attribute += `+ POWER(COUNT(DISTINCT "userVideoHistory"."id"), 2.0) * ${weights.history} ` + } + + attribute += 'AS "score"' + attributes.push(attribute) + + group = 'GROUP BY "video"."id"' + } } if (options.historyOfUser) { - joins.push('INNER JOIN "userVideoHistory" on "video"."id" = "userVideoHistory"."videoId"') + joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"') and.push('"userVideoHistory"."userId" = :historyOfUser') replacements.historyOfUser = options.historyOfUser.id @@ -406,7 +428,7 @@ function buildOrder (value: string) { if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' - if ([ 'trending', 'hot' ].includes(field.toLowerCase())) { // Sort by aggregation + if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation return `ORDER BY "score" ${direction}, "video"."views" ${direction}` } @@ -474,12 +496,13 @@ function wrapForAPIResults (baseQuery: string, replacements: any, options: Build 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"', 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"', - 'LEFT OUTER JOIN "avatar" AS "VideoChannel->Actor->Avatar" ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"', + 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' + + 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"', 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' + 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"', - 'LEFT OUTER JOIN "avatar" AS "VideoChannel->Account->Actor->Avatar" ' + + 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' + 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"', 'LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"' @@ -501,6 +524,10 @@ function wrapForAPIResults (baseQuery: string, replacements: any, options: Build '"VideoFiles"."resolution"': '"VideoFiles.resolution"', '"VideoFiles"."size"': '"VideoFiles.size"', '"VideoFiles"."extname"': '"VideoFiles.extname"', + '"VideoFiles"."filename"': '"VideoFiles.filename"', + '"VideoFiles"."fileUrl"': '"VideoFiles.fileUrl"', + '"VideoFiles"."torrentFilename"': '"VideoFiles.torrentFilename"', + '"VideoFiles"."torrentUrl"': '"VideoFiles.torrentUrl"', '"VideoFiles"."infoHash"': '"VideoFiles.infoHash"', '"VideoFiles"."fps"': '"VideoFiles.fps"', '"VideoFiles"."videoId"': '"VideoFiles.videoId"', @@ -514,6 +541,10 @@ function wrapForAPIResults (baseQuery: string, replacements: any, options: Build '"VideoStreamingPlaylists->VideoFiles"."resolution"': '"VideoStreamingPlaylists.VideoFiles.resolution"', '"VideoStreamingPlaylists->VideoFiles"."size"': '"VideoStreamingPlaylists.VideoFiles.size"', '"VideoStreamingPlaylists->VideoFiles"."extname"': '"VideoStreamingPlaylists.VideoFiles.extname"', + '"VideoStreamingPlaylists->VideoFiles"."filename"': '"VideoStreamingPlaylists.VideoFiles.filename"', + '"VideoStreamingPlaylists->VideoFiles"."fileUrl"': '"VideoStreamingPlaylists.VideoFiles.fileUrl"', + '"VideoStreamingPlaylists->VideoFiles"."torrentFilename"': '"VideoStreamingPlaylists.VideoFiles.torrentFilename"', + '"VideoStreamingPlaylists->VideoFiles"."torrentUrl"': '"VideoStreamingPlaylists.VideoFiles.torrentUrl"', '"VideoStreamingPlaylists->VideoFiles"."infoHash"': '"VideoStreamingPlaylists.VideoFiles.infoHash"', '"VideoStreamingPlaylists->VideoFiles"."fps"': '"VideoStreamingPlaylists.VideoFiles.fps"', '"VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId"': '"VideoStreamingPlaylists.VideoFiles.videoStreamingPlaylistId"',