diff options
author | Chocobozzz <me@florianbigard.com> | 2021-06-10 08:53:32 +0200 |
---|---|---|
committer | Chocobozzz <me@florianbigard.com> | 2021-06-10 09:22:58 +0200 |
commit | e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f (patch) | |
tree | e7ae22528a0cf5b181f7cefbf867e641e9cacef9 /server/models/video/sql/videos-model-list-query-builder.ts | |
parent | ff0ea0cd8e3b0ecad445672deb75b193babeddc2 (diff) | |
download | PeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.tar.gz PeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.tar.zst PeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.zip |
Refactor video query builder
Diffstat (limited to 'server/models/video/sql/videos-model-list-query-builder.ts')
-rw-r--r-- | server/models/video/sql/videos-model-list-query-builder.ts | 234 |
1 files changed, 234 insertions, 0 deletions
diff --git a/server/models/video/sql/videos-model-list-query-builder.ts b/server/models/video/sql/videos-model-list-query-builder.ts new file mode 100644 index 000000000..4ba9dd878 --- /dev/null +++ b/server/models/video/sql/videos-model-list-query-builder.ts | |||
@@ -0,0 +1,234 @@ | |||
1 | |||
2 | import { MUserId } from '@server/types/models' | ||
3 | import { Sequelize } from 'sequelize' | ||
4 | import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder' | ||
5 | import { buildVideosFromRows } from './video-model-builder' | ||
6 | import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder' | ||
7 | |||
8 | export class VideosModelListQueryBuilder extends AbstractVideosQueryBuilder { | ||
9 | private attributes: { [key: string]: string } | ||
10 | |||
11 | private joins: string[] = [] | ||
12 | |||
13 | private innerQuery: string | ||
14 | private innerSort: string | ||
15 | |||
16 | constructor (protected readonly sequelize: Sequelize) { | ||
17 | super() | ||
18 | } | ||
19 | |||
20 | queryVideos (options: BuildVideosListQueryOptions) { | ||
21 | this.buildInnerQuery(options) | ||
22 | this.buildListQueryFromIdsQuery(options) | ||
23 | |||
24 | return this.runQuery(true).then(rows => buildVideosFromRows(rows)) | ||
25 | } | ||
26 | |||
27 | private buildInnerQuery (options: BuildVideosListQueryOptions) { | ||
28 | const idsQueryBuilder = new VideosIdListQueryBuilder(this.sequelize) | ||
29 | const { query, sort, replacements } = idsQueryBuilder.getIdsListQueryAndSort(options) | ||
30 | |||
31 | this.replacements = replacements | ||
32 | this.innerQuery = query | ||
33 | this.innerSort = sort | ||
34 | } | ||
35 | |||
36 | private buildListQueryFromIdsQuery (options: BuildVideosListQueryOptions) { | ||
37 | this.attributes = { | ||
38 | '"video".*': '' | ||
39 | } | ||
40 | |||
41 | this.joins = [ 'INNER JOIN "video" ON "tmp"."id" = "video"."id"' ] | ||
42 | |||
43 | this.includeChannels() | ||
44 | this.includeAccounts() | ||
45 | this.includeThumbnails() | ||
46 | |||
47 | if (options.withFiles) { | ||
48 | this.includeFiles() | ||
49 | } | ||
50 | |||
51 | if (options.user) { | ||
52 | this.includeUserHistory(options.user) | ||
53 | } | ||
54 | |||
55 | if (options.videoPlaylistId) { | ||
56 | this.includePlaylist(options.videoPlaylistId) | ||
57 | } | ||
58 | |||
59 | const select = this.buildSelect() | ||
60 | |||
61 | this.query = `${select} FROM (${this.innerQuery}) AS "tmp" ${this.joins.join(' ')} ${this.innerSort}` | ||
62 | } | ||
63 | |||
64 | private includeChannels () { | ||
65 | this.attributes = { | ||
66 | ...this.attributes, | ||
67 | |||
68 | '"VideoChannel"."id"': '"VideoChannel.id"', | ||
69 | '"VideoChannel"."name"': '"VideoChannel.name"', | ||
70 | '"VideoChannel"."description"': '"VideoChannel.description"', | ||
71 | '"VideoChannel"."actorId"': '"VideoChannel.actorId"', | ||
72 | '"VideoChannel->Actor"."id"': '"VideoChannel.Actor.id"', | ||
73 | '"VideoChannel->Actor"."preferredUsername"': '"VideoChannel.Actor.preferredUsername"', | ||
74 | '"VideoChannel->Actor"."url"': '"VideoChannel.Actor.url"', | ||
75 | '"VideoChannel->Actor"."serverId"': '"VideoChannel.Actor.serverId"', | ||
76 | '"VideoChannel->Actor"."avatarId"': '"VideoChannel.Actor.avatarId"', | ||
77 | '"VideoChannel->Actor->Server"."id"': '"VideoChannel.Actor.Server.id"', | ||
78 | '"VideoChannel->Actor->Server"."host"': '"VideoChannel.Actor.Server.host"', | ||
79 | '"VideoChannel->Actor->Avatar"."id"': '"VideoChannel.Actor.Avatar.id"', | ||
80 | '"VideoChannel->Actor->Avatar"."filename"': '"VideoChannel.Actor.Avatar.filename"', | ||
81 | '"VideoChannel->Actor->Avatar"."fileUrl"': '"VideoChannel.Actor.Avatar.fileUrl"', | ||
82 | '"VideoChannel->Actor->Avatar"."onDisk"': '"VideoChannel.Actor.Avatar.onDisk"', | ||
83 | '"VideoChannel->Actor->Avatar"."createdAt"': '"VideoChannel.Actor.Avatar.createdAt"', | ||
84 | '"VideoChannel->Actor->Avatar"."updatedAt"': '"VideoChannel.Actor.Avatar.updatedAt"' | ||
85 | } | ||
86 | |||
87 | this.joins = this.joins.concat([ | ||
88 | 'INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"', | ||
89 | 'INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"', | ||
90 | |||
91 | 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"', | ||
92 | 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' + | ||
93 | 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"' | ||
94 | ]) | ||
95 | } | ||
96 | |||
97 | private includeAccounts () { | ||
98 | this.attributes = { | ||
99 | ...this.attributes, | ||
100 | |||
101 | '"VideoChannel->Account"."id"': '"VideoChannel.Account.id"', | ||
102 | '"VideoChannel->Account"."name"': '"VideoChannel.Account.name"', | ||
103 | '"VideoChannel->Account->Actor"."id"': '"VideoChannel.Account.Actor.id"', | ||
104 | '"VideoChannel->Account->Actor"."preferredUsername"': '"VideoChannel.Account.Actor.preferredUsername"', | ||
105 | '"VideoChannel->Account->Actor"."url"': '"VideoChannel.Account.Actor.url"', | ||
106 | '"VideoChannel->Account->Actor"."serverId"': '"VideoChannel.Account.Actor.serverId"', | ||
107 | '"VideoChannel->Account->Actor"."avatarId"': '"VideoChannel.Account.Actor.avatarId"', | ||
108 | '"VideoChannel->Account->Actor->Server"."id"': '"VideoChannel.Account.Actor.Server.id"', | ||
109 | '"VideoChannel->Account->Actor->Server"."host"': '"VideoChannel.Account.Actor.Server.host"', | ||
110 | '"VideoChannel->Account->Actor->Avatar"."id"': '"VideoChannel.Account.Actor.Avatar.id"', | ||
111 | '"VideoChannel->Account->Actor->Avatar"."filename"': '"VideoChannel.Account.Actor.Avatar.filename"', | ||
112 | '"VideoChannel->Account->Actor->Avatar"."fileUrl"': '"VideoChannel.Account.Actor.Avatar.fileUrl"', | ||
113 | '"VideoChannel->Account->Actor->Avatar"."onDisk"': '"VideoChannel.Account.Actor.Avatar.onDisk"', | ||
114 | '"VideoChannel->Account->Actor->Avatar"."createdAt"': '"VideoChannel.Account.Actor.Avatar.createdAt"', | ||
115 | '"VideoChannel->Account->Actor->Avatar"."updatedAt"': '"VideoChannel.Account.Actor.Avatar.updatedAt"' | ||
116 | } | ||
117 | |||
118 | this.joins = this.joins.concat([ | ||
119 | 'INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"', | ||
120 | 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"', | ||
121 | |||
122 | 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' + | ||
123 | 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"', | ||
124 | |||
125 | 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' + | ||
126 | 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"' | ||
127 | ]) | ||
128 | } | ||
129 | |||
130 | private includeThumbnails () { | ||
131 | this.attributes = { | ||
132 | ...this.attributes, | ||
133 | |||
134 | '"Thumbnails"."id"': '"Thumbnails.id"', | ||
135 | '"Thumbnails"."type"': '"Thumbnails.type"', | ||
136 | '"Thumbnails"."filename"': '"Thumbnails.filename"' | ||
137 | } | ||
138 | |||
139 | this.joins.push('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"') | ||
140 | } | ||
141 | |||
142 | private includeFiles () { | ||
143 | this.attributes = { | ||
144 | ...this.attributes, | ||
145 | |||
146 | '"VideoFiles"."id"': '"VideoFiles.id"', | ||
147 | '"VideoFiles"."createdAt"': '"VideoFiles.createdAt"', | ||
148 | '"VideoFiles"."updatedAt"': '"VideoFiles.updatedAt"', | ||
149 | '"VideoFiles"."resolution"': '"VideoFiles.resolution"', | ||
150 | '"VideoFiles"."size"': '"VideoFiles.size"', | ||
151 | '"VideoFiles"."extname"': '"VideoFiles.extname"', | ||
152 | '"VideoFiles"."filename"': '"VideoFiles.filename"', | ||
153 | '"VideoFiles"."fileUrl"': '"VideoFiles.fileUrl"', | ||
154 | '"VideoFiles"."torrentFilename"': '"VideoFiles.torrentFilename"', | ||
155 | '"VideoFiles"."torrentUrl"': '"VideoFiles.torrentUrl"', | ||
156 | '"VideoFiles"."infoHash"': '"VideoFiles.infoHash"', | ||
157 | '"VideoFiles"."fps"': '"VideoFiles.fps"', | ||
158 | '"VideoFiles"."videoId"': '"VideoFiles.videoId"', | ||
159 | |||
160 | '"VideoStreamingPlaylists"."id"': '"VideoStreamingPlaylists.id"', | ||
161 | '"VideoStreamingPlaylists"."playlistUrl"': '"VideoStreamingPlaylists.playlistUrl"', | ||
162 | '"VideoStreamingPlaylists"."type"': '"VideoStreamingPlaylists.type"', | ||
163 | '"VideoStreamingPlaylists->VideoFiles"."id"': '"VideoStreamingPlaylists.VideoFiles.id"', | ||
164 | '"VideoStreamingPlaylists->VideoFiles"."createdAt"': '"VideoStreamingPlaylists.VideoFiles.createdAt"', | ||
165 | '"VideoStreamingPlaylists->VideoFiles"."updatedAt"': '"VideoStreamingPlaylists.VideoFiles.updatedAt"', | ||
166 | '"VideoStreamingPlaylists->VideoFiles"."resolution"': '"VideoStreamingPlaylists.VideoFiles.resolution"', | ||
167 | '"VideoStreamingPlaylists->VideoFiles"."size"': '"VideoStreamingPlaylists.VideoFiles.size"', | ||
168 | '"VideoStreamingPlaylists->VideoFiles"."extname"': '"VideoStreamingPlaylists.VideoFiles.extname"', | ||
169 | '"VideoStreamingPlaylists->VideoFiles"."filename"': '"VideoStreamingPlaylists.VideoFiles.filename"', | ||
170 | '"VideoStreamingPlaylists->VideoFiles"."fileUrl"': '"VideoStreamingPlaylists.VideoFiles.fileUrl"', | ||
171 | '"VideoStreamingPlaylists->VideoFiles"."torrentFilename"': '"VideoStreamingPlaylists.VideoFiles.torrentFilename"', | ||
172 | '"VideoStreamingPlaylists->VideoFiles"."torrentUrl"': '"VideoStreamingPlaylists.VideoFiles.torrentUrl"', | ||
173 | '"VideoStreamingPlaylists->VideoFiles"."infoHash"': '"VideoStreamingPlaylists.VideoFiles.infoHash"', | ||
174 | '"VideoStreamingPlaylists->VideoFiles"."fps"': '"VideoStreamingPlaylists.VideoFiles.fps"', | ||
175 | '"VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId"': '"VideoStreamingPlaylists.VideoFiles.videoStreamingPlaylistId"', | ||
176 | '"VideoStreamingPlaylists->VideoFiles"."videoId"': '"VideoStreamingPlaylists.VideoFiles.videoId"' | ||
177 | } | ||
178 | |||
179 | this.joins = this.joins.concat([ | ||
180 | 'LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"', | ||
181 | |||
182 | 'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"', | ||
183 | |||
184 | 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' + | ||
185 | 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"' | ||
186 | ]) | ||
187 | } | ||
188 | |||
189 | private includeUserHistory (user: MUserId) { | ||
190 | this.attributes = { | ||
191 | ...this.attributes, | ||
192 | |||
193 | '"userVideoHistory"."id"': '"userVideoHistory.id"', | ||
194 | '"userVideoHistory"."currentTime"': '"userVideoHistory.currentTime"' | ||
195 | } | ||
196 | |||
197 | this.joins.push( | ||
198 | 'LEFT OUTER JOIN "userVideoHistory" ' + | ||
199 | 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId' | ||
200 | ) | ||
201 | |||
202 | this.replacements.userVideoHistoryId = user.id | ||
203 | } | ||
204 | |||
205 | private includePlaylist (playlistId: number) { | ||
206 | this.attributes = { | ||
207 | ...this.attributes, | ||
208 | |||
209 | '"VideoPlaylistElement"."createdAt"': '"VideoPlaylistElement.createdAt"', | ||
210 | '"VideoPlaylistElement"."updatedAt"': '"VideoPlaylistElement.updatedAt"', | ||
211 | '"VideoPlaylistElement"."url"': '"VideoPlaylistElement.url"', | ||
212 | '"VideoPlaylistElement"."position"': '"VideoPlaylistElement.position"', | ||
213 | '"VideoPlaylistElement"."startTimestamp"': '"VideoPlaylistElement.startTimestamp"', | ||
214 | '"VideoPlaylistElement"."stopTimestamp"': '"VideoPlaylistElement.stopTimestamp"', | ||
215 | '"VideoPlaylistElement"."videoPlaylistId"': '"VideoPlaylistElement.videoPlaylistId"' | ||
216 | } | ||
217 | |||
218 | this.joins.push( | ||
219 | 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' + | ||
220 | 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId' | ||
221 | ) | ||
222 | |||
223 | this.replacements.videoPlaylistId = playlistId | ||
224 | } | ||
225 | |||
226 | private buildSelect () { | ||
227 | return 'SELECT ' + Object.keys(this.attributes).map(key => { | ||
228 | const value = this.attributes[key] | ||
229 | if (value) return `${key} AS ${value}` | ||
230 | |||
231 | return key | ||
232 | }).join(', ') | ||
233 | } | ||
234 | } | ||