From 8d194d9a5c2b0acbf89f5832a8dd428bc722881b Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 27 Jul 2018 16:57:16 +0200 Subject: [PATCH] Optimize videos list SQL query (another time) --- server/models/video/video.ts | 12 ++++++++++-- 1 file changed, 10 insertions(+), 2 deletions(-) diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 464dbf597..5808f3936 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -199,6 +199,8 @@ type AvailableForListOptions = { // Force actorId to be a number to avoid SQL injections const actorIdNumber = parseInt(options.actorId.toString(), 10) + + // FIXME: It would be more efficient to use a CTE so we join AFTER the filters, but sequelize does not support it... const query: IFindOptions = { where: { id: { @@ -215,8 +217,14 @@ type AvailableForListOptions = { 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + - 'LEFT JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + - 'WHERE "actor"."serverId" IS NULL OR "actorFollow"."actorId" = ' + actorIdNumber + + 'WHERE "actor"."serverId" IS NULL ' + + ' UNION ALL ' + + 'SELECT "video"."id" AS "id" FROM "video" ' + + 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + + 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + + 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + + 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + + 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + ')' ) }, -- 2.25.1