+ static listUserSubscribersOf (actorId: number): Promise<MUserWithNotificationSetting[]> {
+ const query = {
+ include: [
+ {
+ model: UserNotificationSettingModel.unscoped(),
+ required: true
+ },
+ {
+ attributes: [ 'userId' ],
+ model: AccountModel.unscoped(),
+ required: true,
+ include: [
+ {
+ attributes: [],
+ model: ActorModel.unscoped(),
+ required: true,
+ where: {
+ serverId: null
+ },
+ include: [
+ {
+ attributes: [],
+ as: 'ActorFollowings',
+ model: ActorFollowModel.unscoped(),
+ required: true,
+ where: {
+ targetActorId: actorId
+ }
+ }
+ ]
+ }
+ ]
+ }
+ ]
+ }
+
+ return UserModel.unscoped().findAll(query)
+ }
+
+ static listByUsernames (usernames: string[]): Promise<MUserDefault[]> {
+ const query = {
+ where: {
+ username: usernames
+ }
+ }
+
+ return UserModel.findAll(query)
+ }
+
+ static loadById (id: number): Promise<MUser> {
+ return UserModel.unscoped().findByPk(id)
+ }
+
+ static loadByIdFull (id: number): Promise<MUserDefault> {
+ return UserModel.findByPk(id)
+ }
+
+ static loadByIdWithChannels (id: number, withStats = false): Promise<MUserDefault> {
+ const scopes = [
+ ScopeNames.WITH_VIDEOCHANNELS
+ ]
+
+ if (withStats) {
+ scopes.push(ScopeNames.WITH_QUOTA)
+ scopes.push(ScopeNames.WITH_STATS)
+ }
+
+ return UserModel.scope(scopes).findByPk(id)
+ }
+
+ static loadByUsername (username: string): Promise<MUserDefault> {
+ const query = {
+ where: {
+ username
+ }
+ }
+
+ return UserModel.findOne(query)
+ }
+
+ static loadForMeAPI (id: number): Promise<MUserNotifSettingChannelDefault> {
+ const query = {
+ where: {
+ id
+ }
+ }
+
+ return UserModel.scope(ScopeNames.FOR_ME_API).findOne(query)
+ }
+
+ static loadByEmail (email: string): Promise<MUserDefault> {
+ const query = {
+ where: {
+ email
+ }
+ }
+
+ return UserModel.findOne(query)
+ }
+
+ static loadByUsernameOrEmail (username: string, email?: string): Promise<MUserDefault> {
+ if (!email) email = username
+
+ const query = {
+ where: {
+ [Op.or]: [
+ where(fn('lower', col('username')), fn('lower', username) as any),
+
+ { email }
+ ]
+ }
+ }
+
+ return UserModel.findOne(query)
+ }
+
+ static loadByVideoId (videoId: number): Promise<MUserDefault> {
+ const query = {
+ include: [
+ {
+ required: true,
+ attributes: [ 'id' ],
+ model: AccountModel.unscoped(),
+ include: [
+ {
+ required: true,
+ attributes: [ 'id' ],
+ model: VideoChannelModel.unscoped(),
+ include: [
+ {
+ required: true,
+ attributes: [ 'id' ],
+ model: VideoModel.unscoped(),
+ where: {
+ id: videoId
+ }
+ }
+ ]
+ }
+ ]
+ }
+ ]
+ }
+
+ return UserModel.findOne(query)
+ }
+
+ static loadByVideoImportId (videoImportId: number): Promise<MUserDefault> {
+ const query = {
+ include: [
+ {
+ required: true,
+ attributes: [ 'id' ],
+ model: VideoImportModel.unscoped(),
+ where: {
+ id: videoImportId
+ }
+ }
+ ]
+ }
+
+ return UserModel.findOne(query)
+ }
+
+ static loadByChannelActorId (videoChannelActorId: number): Promise<MUserDefault> {
+ const query = {
+ include: [
+ {
+ required: true,
+ attributes: [ 'id' ],
+ model: AccountModel.unscoped(),
+ include: [
+ {
+ required: true,
+ attributes: [ 'id' ],
+ model: VideoChannelModel.unscoped(),
+ where: {
+ actorId: videoChannelActorId
+ }
+ }
+ ]
+ }
+ ]
+ }
+
+ return UserModel.findOne(query)
+ }
+
+ static loadByAccountActorId (accountActorId: number): Promise<MUserDefault> {
+ const query = {
+ include: [
+ {
+ required: true,
+ attributes: [ 'id' ],
+ model: AccountModel.unscoped(),
+ where: {
+ actorId: accountActorId
+ }
+ }
+ ]
+ }
+
+ return UserModel.findOne(query)
+ }
+
+ static loadByLiveId (liveId: number): Promise<MUser> {
+ const query = {
+ include: [
+ {
+ attributes: [ 'id' ],
+ model: AccountModel.unscoped(),
+ required: true,
+ include: [
+ {
+ attributes: [ 'id' ],
+ model: VideoChannelModel.unscoped(),
+ required: true,
+ include: [
+ {
+ attributes: [ 'id' ],
+ model: VideoModel.unscoped(),
+ required: true,
+ include: [
+ {
+ attributes: [],
+ model: VideoLiveModel.unscoped(),
+ required: true,
+ where: {
+ id: liveId
+ }
+ }
+ ]
+ }
+ ]
+ }
+ ]
+ }
+ ]
+ }
+
+ return UserModel.unscoped().findOne(query)
+ }
+
+ static generateUserQuotaBaseSQL (options: {
+ whereUserId: '$userId' | '"UserModel"."id"'
+ withSelect: boolean
+ daily: boolean
+ }) {
+ const andWhere = options.daily === true
+ ? 'AND "video"."createdAt" > now() - interval \'24 hours\''
+ : ''
+
+ const videoChannelJoin = 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+ 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
+ `WHERE "account"."userId" = ${options.whereUserId} ${andWhere}`
+
+ const webtorrentFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
+ 'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
+ videoChannelJoin
+
+ const hlsFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
+ 'INNER JOIN "videoStreamingPlaylist" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist".id ' +
+ 'INNER JOIN "video" ON "videoStreamingPlaylist"."videoId" = "video"."id" ' +
+ videoChannelJoin
+
+ return 'SELECT COALESCE(SUM("size"), 0) AS "total" ' +
+ 'FROM (' +
+ `SELECT MAX("t1"."size") AS "size" FROM (${webtorrentFiles} UNION ${hlsFiles}) t1 ` +
+ 'GROUP BY "t1"."videoId"' +
+ ') t2'
+ }
+
+ static getTotalRawQuery (query: string, userId: number) {
+ const options = {
+ bind: { userId },
+ type: QueryTypes.SELECT as QueryTypes.SELECT
+ }
+
+ return UserModel.sequelize.query<{ total: string }>(query, options)
+ .then(([ { total } ]) => {
+ if (total === null) return 0
+
+ return parseInt(total, 10)
+ })
+ }
+
+ static async getStats () {
+ function getActiveUsers (days: number) {
+ const query = {
+ where: {
+ [Op.and]: [
+ literal(`"lastLoginDate" > NOW() - INTERVAL '${days}d'`)
+ ]
+ }
+ }
+
+ return UserModel.count(query)
+ }
+
+ const totalUsers = await UserModel.count()
+ const totalDailyActiveUsers = await getActiveUsers(1)
+ const totalWeeklyActiveUsers = await getActiveUsers(7)
+ const totalMonthlyActiveUsers = await getActiveUsers(30)
+ const totalHalfYearActiveUsers = await getActiveUsers(180)
+