From ac0868bcc0259d4ff14265d9ae403e10869a13aa Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 3 Jan 2020 14:17:57 +0100 Subject: [PATCH] Improve SQL query for my special playlists --- server/controllers/api/users/me.ts | 7 ++- server/models/account/user.ts | 72 ++++++++++++++++-------------- server/tests/api/users/users.ts | 5 ++- server/typings/models/user/user.ts | 10 ++++- shared/models/users/user.model.ts | 11 ++++- 5 files changed, 63 insertions(+), 42 deletions(-) diff --git a/server/controllers/api/users/me.ts b/server/controllers/api/users/me.ts index 2f3efe6aa..ac7c62aab 100644 --- a/server/controllers/api/users/me.ts +++ b/server/controllers/api/users/me.ts @@ -126,14 +126,13 @@ async function getUserVideoImports (req: express.Request, res: express.Response) async function getUserInformation (req: express.Request, res: express.Response) { // We did not load channels in res.locals.user - const user = await UserModel.loadByUsernameAndPopulateChannels(res.locals.oauth.token.user.username) + const user = await UserModel.loadForMeAPI(res.locals.oauth.token.user.username) - return res.json(user.toFormattedJSON({ me: true })) + return res.json(user.toMeFormattedJSON()) } async function getUserVideoQuotaUsed (req: express.Request, res: express.Response) { - // We did not load channels in res.locals.user - const user = await UserModel.loadByUsernameAndPopulateChannels(res.locals.oauth.token.user.username) + const user = res.locals.oauth.token.user const videoQuotaUsed = await UserModel.getOriginalVideoFileTotalFromUser(user) const videoQuotaUsedDaily = await UserModel.getOriginalVideoFileTotalDailyFromUser(user) diff --git a/server/models/account/user.ts b/server/models/account/user.ts index 8bd41de22..27262af42 100644 --- a/server/models/account/user.ts +++ b/server/models/account/user.ts @@ -19,14 +19,15 @@ import { Table, UpdatedAt } from 'sequelize-typescript' -import { hasUserRight, USER_ROLE_LABELS, UserRight, VideoPrivacy, MyUser } from '../../../shared' +import { hasUserRight, MyUser, USER_ROLE_LABELS, UserRight, VideoPlaylistType, VideoPrivacy } from '../../../shared' import { User, UserRole } from '../../../shared/models/users' import { isNoInstanceConfigWarningModal, + isNoWelcomeModal, isUserAdminFlagsValid, - isUserAutoPlayVideoValid, - isUserAutoPlayNextVideoValid, isUserAutoPlayNextVideoPlaylistValid, + isUserAutoPlayNextVideoValid, + isUserAutoPlayVideoValid, isUserBlockedReasonValid, isUserBlockedValid, isUserEmailVerifiedValid, @@ -38,8 +39,7 @@ import { isUserVideoQuotaDailyValid, isUserVideoQuotaValid, isUserVideosHistoryEnabledValid, - isUserWebTorrentEnabledValid, - isNoWelcomeModal + isUserWebTorrentEnabledValid } from '../../helpers/custom-validators/users' import { comparePassword, cryptPassword } from '../../helpers/peertube-crypto' import { OAuthTokenModel } from '../oauth/oauth-token' @@ -61,16 +61,17 @@ import { isThemeNameValid } from '../../helpers/custom-validators/plugins' import { getThemeOrDefault } from '../../lib/plugins/theme-utils' import * as Bluebird from 'bluebird' import { + MMyUserFormattable, MUserDefault, MUserFormattable, MUserId, MUserNotifSettingChannelDefault, - MUserWithNotificationSetting, MVideoFullLight + MUserWithNotificationSetting, + MVideoFullLight } from '@server/typings/models' enum ScopeNames { - WITH_VIDEO_CHANNEL = 'WITH_VIDEO_CHANNEL', - WITH_SPECIAL_PLAYLISTS = 'WITH_SPECIAL_PLAYLISTS' + FOR_ME_API = 'FOR_ME_API' } @DefaultScope(() => ({ @@ -86,28 +87,31 @@ enum ScopeNames { ] })) @Scopes(() => ({ - [ScopeNames.WITH_VIDEO_CHANNEL]: { + [ScopeNames.FOR_ME_API]: { include: [ { model: AccountModel, - required: true, - include: [ VideoChannelModel ] + include: [ + { + model: VideoChannelModel + }, + { + attributes: [ 'id', 'name', 'type' ], + model: VideoPlaylistModel.unscoped(), + required: true, + where: { + type: { + [ Op.ne ]: VideoPlaylistType.REGULAR + } + } + } + ] }, { model: UserNotificationSettingModel, required: true } ] - }, - [ScopeNames.WITH_SPECIAL_PLAYLISTS]: { - attributes: { - include: [ - [ - literal('(select array(select "id" from "videoPlaylist" where "ownerAccountId" in (select id from public.account where "userId" = "UserModel"."id") and name LIKE \'Watch later\'))'), - 'specialPlaylists' - ] - ] - } } })) @Table({ @@ -436,17 +440,14 @@ export class UserModel extends Model { return UserModel.findOne(query) } - static loadByUsernameAndPopulateChannels (username: string): Bluebird { + static loadForMeAPI (username: string): Bluebird { const query = { where: { username: { [ Op.iLike ]: username } } } - return UserModel.scope([ - ScopeNames.WITH_VIDEO_CHANNEL, - ScopeNames.WITH_SPECIAL_PLAYLISTS - ]).findOne(query) + return UserModel.scope(ScopeNames.FOR_ME_API).findOne(query) } static loadByEmail (email: string): Bluebird { @@ -625,11 +626,11 @@ export class UserModel extends Model { return comparePassword(password, this.password) } - toFormattedJSON (this: MUserFormattable, parameters: { withAdminFlags?: boolean, me?: boolean } = {}): User | MyUser { + toFormattedJSON (this: MUserFormattable, parameters: { withAdminFlags?: boolean } = {}): User { const videoQuotaUsed = this.get('videoQuotaUsed') const videoQuotaUsedDaily = this.get('videoQuotaUsedDaily') - const json: User | MyUser = { + const json: User = { id: this.id, username: this.username, email: this.email, @@ -690,15 +691,18 @@ export class UserModel extends Model { }) } - if (parameters.me) { - Object.assign(json, { - specialPlaylists: (this.get('specialPlaylists') as Array).map(p => ({ id: p })) - }) - } - return json } + toMeFormattedJSON (this: MMyUserFormattable): MyUser { + const formatted = this.toFormattedJSON() + + const specialPlaylists = this.Account.VideoPlaylists + .map(p => ({ id: p.id, name: p.name, type: p.type })) + + return Object.assign(formatted, { specialPlaylists }) + } + async isAbleToUploadVideo (videoFile: { size: number }) { if (this.videoQuota === -1 && this.videoQuotaDaily === -1) return Promise.resolve(true) diff --git a/server/tests/api/users/users.ts b/server/tests/api/users/users.ts index 3c3ee3ed7..24203a731 100644 --- a/server/tests/api/users/users.ts +++ b/server/tests/api/users/users.ts @@ -2,7 +2,7 @@ import * as chai from 'chai' import 'mocha' -import { User, UserRole, Video, MyUser } from '../../../../shared/index' +import { User, UserRole, Video, MyUser, VideoPlaylistType } from '../../../../shared/index' import { blockUser, cleanupTests, @@ -251,7 +251,7 @@ describe('Test users', function () { it('Should be able to get user information', async function () { const res1 = await getMyUserInformation(server.url, accessTokenUser) - const userMe: User & MyUser = res1.body + const userMe: MyUser = res1.body const res2 = await getUserInformation(server.url, server.accessToken, userMe.id) const userGet: User = res2.body @@ -271,6 +271,7 @@ describe('Test users', function () { expect(userGet.adminFlags).to.equal(UserAdminFlag.BY_PASS_VIDEO_AUTO_BLACKLIST) expect(userMe.specialPlaylists).to.have.lengthOf(1) + expect(userMe.specialPlaylists[0].type).to.equal(VideoPlaylistType.WATCH_LATER) }) }) diff --git a/server/typings/models/user/user.ts b/server/typings/models/user/user.ts index a2750adc7..6ac19c20b 100644 --- a/server/typings/models/user/user.ts +++ b/server/typings/models/user/user.ts @@ -12,6 +12,7 @@ import { import { MNotificationSetting, MNotificationSettingFormattable } from './user-notification-setting' import { AccountModel } from '@server/models/account/account' import { MChannelFormattable } from '../video/video-channels' +import { MVideoPlaylist } from '@server/typings/models' type Use = PickWith @@ -65,6 +66,13 @@ export type MUserDefault = MUser & // Format for API or AP object +type MAccountWithChannels = MAccountFormattable & PickWithOpt +type MAccountWithChannelsAndSpecialPlaylists = MAccountWithChannels & + PickWithOpt + export type MUserFormattable = MUserQuotaUsed & - Use<'Account', MAccountFormattable & PickWithOpt> & + Use<'Account', MAccountWithChannels> & PickWithOpt + +export type MMyUserFormattable = MUserFormattable & + Use<'Account', MAccountWithChannelsAndSpecialPlaylists> diff --git a/shared/models/users/user.model.ts b/shared/models/users/user.model.ts index 1434dca81..328b69df6 100644 --- a/shared/models/users/user.model.ts +++ b/shared/models/users/user.model.ts @@ -5,6 +5,7 @@ import { UserRole } from './user-role' import { NSFWPolicyType } from '../videos/nsfw-policy.type' import { UserNotificationSetting } from './user-notification-setting.model' import { UserAdminFlag } from './user-flag.model' +import { VideoPlaylistType } from '@shared/models' export interface User { id: number @@ -47,6 +48,14 @@ export interface User { createdAt: Date } +export interface MyUserSpecialPlaylist { + id: number + name: string + type: VideoPlaylistType +} + export interface MyUser extends User { - specialPlaylists: Partial[] + specialPlaylists: MyUserSpecialPlaylist[] } + + -- 2.41.0