aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2021-06-11 11:27:45 +0200
committerChocobozzz <me@florianbigard.com>2021-06-11 11:27:45 +0200
commit3c79c2ce86eaf9e151ab6c2c9d1f646968a16744 (patch)
treefa7e993359e070395a45ecb7a19e9c580d88ff61
parent17bb45388ec319d288a1b8387c6c199fe2f6b64f (diff)
downloadPeerTube-3c79c2ce86eaf9e151ab6c2c9d1f646968a16744.tar.gz
PeerTube-3c79c2ce86eaf9e151ab6c2c9d1f646968a16744.tar.zst
PeerTube-3c79c2ce86eaf9e151ab6c2c9d1f646968a16744.zip
Optimize join build
-rw-r--r--server/models/video/sql/shared/abstract-videos-model-query-builder.ts57
-rw-r--r--server/models/video/sql/shared/video-file-query-builder.ts3
-rw-r--r--server/models/video/sql/video-model-get-query-builder.ts3
-rw-r--r--server/models/video/sql/videos-model-list-query-builder.ts5
4 files changed, 39 insertions, 29 deletions
diff --git a/server/models/video/sql/shared/abstract-videos-model-query-builder.ts b/server/models/video/sql/shared/abstract-videos-model-query-builder.ts
index 8eff59db0..65df8d914 100644
--- a/server/models/video/sql/shared/abstract-videos-model-query-builder.ts
+++ b/server/models/video/sql/shared/abstract-videos-model-query-builder.ts
@@ -10,7 +10,8 @@ import { VideoTables } from './video-tables'
10 10
11export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder { 11export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder {
12 protected attributes: { [key: string]: string } = {} 12 protected attributes: { [key: string]: string } = {}
13 protected joins: string[] = [] 13
14 protected joins = ''
14 protected where: string 15 protected where: string
15 16
16 protected tables: VideoTables 17 protected tables: VideoTables
@@ -31,12 +32,14 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
31 } 32 }
32 33
33 protected includeChannels () { 34 protected includeChannels () {
34 this.joins.push( 35 this.addJoin('INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"')
35 'INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"', 36 this.addJoin('INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"')
36 'INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"',
37 37
38 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"', 38 this.addJoin(
39 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"'
40 )
39 41
42 this.addJoin(
40 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' + 43 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' +
41 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"' 44 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"'
42 ) 45 )
@@ -52,13 +55,17 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
52 } 55 }
53 56
54 protected includeAccounts () { 57 protected includeAccounts () {
55 this.joins.push( 58 this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"')
56 'INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"', 59 this.addJoin(
57 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"', 60 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"'
61 )
58 62
63 this.addJoin(
59 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' + 64 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' +
60 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"', 65 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"'
66 )
61 67
68 this.addJoin(
62 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' + 69 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' +
63 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"' 70 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"'
64 ) 71 )
@@ -74,7 +81,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
74 } 81 }
75 82
76 protected includeThumbnails () { 83 protected includeThumbnails () {
77 this.joins.push('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"') 84 this.addJoin('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"')
78 85
79 this.attributes = { 86 this.attributes = {
80 ...this.attributes, 87 ...this.attributes,
@@ -85,7 +92,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
85 92
86 protected includeWebtorrentFiles (required: boolean) { 93 protected includeWebtorrentFiles (required: boolean) {
87 const joinType = required ? 'INNER' : 'LEFT' 94 const joinType = required ? 'INNER' : 'LEFT'
88 this.joins.push(joinType + ' JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"') 95 this.addJoin(joinType + ' JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"')
89 96
90 this.attributes = { 97 this.attributes = {
91 ...this.attributes, 98 ...this.attributes,
@@ -97,9 +104,11 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
97 protected includeStreamingPlaylistFiles (required: boolean) { 104 protected includeStreamingPlaylistFiles (required: boolean) {
98 const joinType = required ? 'INNER' : 'LEFT' 105 const joinType = required ? 'INNER' : 'LEFT'
99 106
100 this.joins.push( 107 this.addJoin(
101 joinType + ' JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"', 108 joinType + ' JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"'
109 )
102 110
111 this.addJoin(
103 joinType + ' JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' + 112 joinType + ' JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
104 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"' 113 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
105 ) 114 )
@@ -113,7 +122,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
113 } 122 }
114 123
115 protected includeUserHistory (userId: number) { 124 protected includeUserHistory (userId: number) {
116 this.joins.push( 125 this.addJoin(
117 'LEFT OUTER JOIN "userVideoHistory" ' + 126 'LEFT OUTER JOIN "userVideoHistory" ' +
118 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId' 127 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
119 ) 128 )
@@ -128,7 +137,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
128 } 137 }
129 138
130 protected includePlaylist (playlistId: number) { 139 protected includePlaylist (playlistId: number) {
131 this.joins.push( 140 this.addJoin(
132 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' + 141 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
133 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId' 142 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
134 ) 143 )
@@ -143,7 +152,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
143 } 152 }
144 153
145 protected includeTags () { 154 protected includeTags () {
146 this.joins.push( 155 this.addJoin(
147 'LEFT OUTER JOIN (' + 156 'LEFT OUTER JOIN (' +
148 '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' + 157 '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' +
149 ') ' + 158 ') ' +
@@ -159,7 +168,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
159 } 168 }
160 169
161 protected includeBlacklisted () { 170 protected includeBlacklisted () {
162 this.joins.push( 171 this.addJoin(
163 'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"' 172 'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"'
164 ) 173 )
165 174
@@ -171,7 +180,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
171 } 180 }
172 181
173 protected includeScheduleUpdate () { 182 protected includeScheduleUpdate () {
174 this.joins.push( 183 this.addJoin(
175 'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"' 184 'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"'
176 ) 185 )
177 186
@@ -183,7 +192,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
183 } 192 }
184 193
185 protected includeLive () { 194 protected includeLive () {
186 this.joins.push( 195 this.addJoin(
187 'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"' 196 'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"'
188 ) 197 )
189 198
@@ -195,7 +204,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
195 } 204 }
196 205
197 protected includeTrackers () { 206 protected includeTrackers () {
198 this.joins.push( 207 this.addJoin(
199 'LEFT OUTER JOIN (' + 208 'LEFT OUTER JOIN (' +
200 '"videoTracker" AS "Trackers->VideoTrackerModel" ' + 209 '"videoTracker" AS "Trackers->VideoTrackerModel" ' +
201 'INNER JOIN "tracker" AS "Trackers" ON "Trackers"."id" = "Trackers->VideoTrackerModel"."trackerId"' + 210 'INNER JOIN "tracker" AS "Trackers" ON "Trackers"."id" = "Trackers->VideoTrackerModel"."trackerId"' +
@@ -211,7 +220,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
211 } 220 }
212 221
213 protected includeWebTorrentRedundancies () { 222 protected includeWebTorrentRedundancies () {
214 this.joins.push( 223 this.addJoin(
215 'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' + 224 'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' +
216 '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"' 225 '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"'
217 ) 226 )
@@ -224,7 +233,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
224 } 233 }
225 234
226 protected includeStreamingPlaylistRedundancies () { 235 protected includeStreamingPlaylistRedundancies () {
227 this.joins.push( 236 this.addJoin(
228 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' + 237 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' +
229 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"' 238 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"'
230 ) 239 )
@@ -269,4 +278,8 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
269 278
270 this.replacements.videoId = id 279 this.replacements.videoId = id
271 } 280 }
281
282 protected addJoin (join: string) {
283 this.joins += join + ' '
284 }
272} 285}
diff --git a/server/models/video/sql/shared/video-file-query-builder.ts b/server/models/video/sql/shared/video-file-query-builder.ts
index ad47905c6..7d822f8fa 100644
--- a/server/models/video/sql/shared/video-file-query-builder.ts
+++ b/server/models/video/sql/shared/video-file-query-builder.ts
@@ -10,7 +10,6 @@ import { AbstractVideosModelQueryBuilder } from './abstract-videos-model-query-b
10 10
11export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder { 11export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder {
12 protected attributes: { [key: string]: string } 12 protected attributes: { [key: string]: string }
13 protected joins: string[] = []
14 13
15 constructor (protected readonly sequelize: Sequelize) { 14 constructor (protected readonly sequelize: Sequelize) {
16 super('get') 15 super('get')
@@ -61,6 +60,6 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder {
61 } 60 }
62 61
63 private buildQuery () { 62 private buildQuery () {
64 return `${this.buildSelect()} FROM "video" ${this.joins.join(' ')} ${this.where}` 63 return `${this.buildSelect()} FROM "video" ${this.joins} ${this.where}`
65 } 64 }
66} 65}
diff --git a/server/models/video/sql/video-model-get-query-builder.ts b/server/models/video/sql/video-model-get-query-builder.ts
index 892639076..4aab9ff1d 100644
--- a/server/models/video/sql/video-model-get-query-builder.ts
+++ b/server/models/video/sql/video-model-get-query-builder.ts
@@ -52,7 +52,6 @@ export class VideosModelGetQueryBuilder {
52 52
53export class VideosModelGetQuerySubBuilder extends AbstractVideosModelQueryBuilder { 53export class VideosModelGetQuerySubBuilder extends AbstractVideosModelQueryBuilder {
54 protected attributes: { [key: string]: string } 54 protected attributes: { [key: string]: string }
55 protected joins: string[] = []
56 55
57 protected webtorrentFilesQuery: string 56 protected webtorrentFilesQuery: string
58 protected streamingPlaylistFilesQuery: string 57 protected streamingPlaylistFilesQuery: string
@@ -102,6 +101,6 @@ export class VideosModelGetQuerySubBuilder extends AbstractVideosModelQueryBuild
102 const order = 'ORDER BY "Tags"."name" ASC' 101 const order = 'ORDER BY "Tags"."name" ASC'
103 const from = `SELECT * FROM "video" ${this.where} LIMIT 1` 102 const from = `SELECT * FROM "video" ${this.where} LIMIT 1`
104 103
105 return `${this.buildSelect()} FROM (${from}) AS "video" ${this.joins.join(' ')} ${order}` 104 return `${this.buildSelect()} FROM (${from}) AS "video" ${this.joins} ${order}`
106 } 105 }
107} 106}
diff --git a/server/models/video/sql/videos-model-list-query-builder.ts b/server/models/video/sql/videos-model-list-query-builder.ts
index 459f542a4..d3a9a9466 100644
--- a/server/models/video/sql/videos-model-list-query-builder.ts
+++ b/server/models/video/sql/videos-model-list-query-builder.ts
@@ -11,7 +11,6 @@ import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-
11 11
12export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder { 12export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder {
13 protected attributes: { [key: string]: string } 13 protected attributes: { [key: string]: string }
14 protected joins: string[] = []
15 14
16 private innerQuery: string 15 private innerQuery: string
17 private innerSort: string 16 private innerSort: string
@@ -45,7 +44,7 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder
45 '"video".*': '' 44 '"video".*': ''
46 } 45 }
47 46
48 this.joins = [ 'INNER JOIN "video" ON "tmp"."id" = "video"."id"' ] 47 this.addJoin('INNER JOIN "video" ON "tmp"."id" = "video"."id"')
49 48
50 this.includeChannels() 49 this.includeChannels()
51 this.includeAccounts() 50 this.includeAccounts()
@@ -66,6 +65,6 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder
66 65
67 const select = this.buildSelect() 66 const select = this.buildSelect()
68 67
69 this.query = `${select} FROM (${this.innerQuery}) AS "tmp" ${this.joins.join(' ')} ${this.innerSort}` 68 this.query = `${select} FROM (${this.innerQuery}) AS "tmp" ${this.joins} ${this.innerSort}`
70 } 69 }
71} 70}