- const query: FindOptions = {
- raw: true,
- attributes: [
- [ fn('COALESCE', fn('SUM', col('VideoFile.size')), '0'), 'totalUsed' ],
- [ fn('COUNT', fn('DISTINCT', col('videoId'))), 'totalVideos' ],
- [ fn('COUNT', col('videoFileId')), 'totalVideoFiles' ]
- ],
- where: {
- strategy,
- actorId: actor.id
- },
- include: [
- {
- attributes: [],
- model: VideoFileModel,
- required: true
- }
- ]
- }
-
- return VideoRedundancyModel.findOne(query)
- .then((r: any) => ({
- totalUsed: parseAggregateResult(r.totalUsed),
- totalVideos: r.totalVideos,
- totalVideoFiles: r.totalVideoFiles
- }))
+ const sql = `WITH "tmp" AS ` +
+ `(` +
+ `SELECT "videoFile"."size" AS "videoFileSize", "videoStreamingFile"."size" AS "videoStreamingFileSize", ` +
+ `"videoFile"."videoId" AS "videoFileVideoId", "videoStreamingPlaylist"."videoId" AS "videoStreamingVideoId"` +
+ `FROM "videoRedundancy" AS "videoRedundancy" ` +
+ `LEFT JOIN "videoFile" AS "videoFile" ON "videoRedundancy"."videoFileId" = "videoFile"."id" ` +
+ `LEFT JOIN "videoStreamingPlaylist" ON "videoRedundancy"."videoStreamingPlaylistId" = "videoStreamingPlaylist"."id" ` +
+ `LEFT JOIN "videoFile" AS "videoStreamingFile" ` +
+ `ON "videoStreamingPlaylist"."id" = "videoStreamingFile"."videoStreamingPlaylistId" ` +
+ `WHERE "videoRedundancy"."strategy" = :strategy AND "videoRedundancy"."actorId" = :actorId` +
+ `), ` +
+ `"videoIds" AS (` +
+ `SELECT "videoFileVideoId" AS "videoId" FROM "tmp" ` +
+ `UNION SELECT "videoStreamingVideoId" AS "videoId" FROM "tmp" ` +
+ `) ` +
+ `SELECT ` +
+ `COALESCE(SUM("videoFileSize"), '0') + COALESCE(SUM("videoStreamingFileSize"), '0') AS "totalUsed", ` +
+ `(SELECT COUNT("videoIds"."videoId") FROM "videoIds") AS "totalVideos", ` +
+ `COUNT(*) AS "totalVideoFiles" ` +
+ `FROM "tmp"`
+
+ return VideoRedundancyModel.sequelize.query<any>(sql, {
+ replacements: { strategy, actorId: actor.id },
+ type: QueryTypes.SELECT
+ }).then(([ row ]) => ({
+ totalUsed: parseAggregateResult(row.totalUsed),
+ totalVideos: row.totalVideos,
+ totalVideoFiles: row.totalVideoFiles
+ }))