aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2018-07-27 16:57:16 +0200
committerChocobozzz <me@florianbigard.com>2018-07-27 16:57:16 +0200
commit8d194d9a5c2b0acbf89f5832a8dd428bc722881b (patch)
treedcd64f7e5d8a34e06497daaba9f3ec36a92b3252 /server
parentdbfd3e9bfef86bf935a24c9a325d9f06e3978b8f (diff)
downloadPeerTube-8d194d9a5c2b0acbf89f5832a8dd428bc722881b.tar.gz
PeerTube-8d194d9a5c2b0acbf89f5832a8dd428bc722881b.tar.zst
PeerTube-8d194d9a5c2b0acbf89f5832a8dd428bc722881b.zip
Optimize videos list SQL query (another time)
Diffstat (limited to 'server')
-rw-r--r--server/models/video/video.ts12
1 files 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 = {
199 199
200 // Force actorId to be a number to avoid SQL injections 200 // Force actorId to be a number to avoid SQL injections
201 const actorIdNumber = parseInt(options.actorId.toString(), 10) 201 const actorIdNumber = parseInt(options.actorId.toString(), 10)
202
203 // FIXME: It would be more efficient to use a CTE so we join AFTER the filters, but sequelize does not support it...
202 const query: IFindOptions<VideoModel> = { 204 const query: IFindOptions<VideoModel> = {
203 where: { 205 where: {
204 id: { 206 id: {
@@ -215,8 +217,14 @@ type AvailableForListOptions = {
215 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + 217 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
216 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + 218 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
217 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + 219 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
218 'LEFT JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + 220 'WHERE "actor"."serverId" IS NULL ' +
219 'WHERE "actor"."serverId" IS NULL OR "actorFollow"."actorId" = ' + actorIdNumber + 221 ' UNION ALL ' +
222 'SELECT "video"."id" AS "id" FROM "video" ' +
223 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
224 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
225 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
226 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
227 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
220 ')' 228 ')'
221 ) 229 )
222 }, 230 },