11 files changed, 151 insertions, 136 deletions

diff --git a/server/models/abuse/abuse.ts b/server/models/abuse/abuse.ts
index 4c6a96a86..5610077c9 100644
--- a/server/models/abuse/abuse.ts
+++ b/server/models/abuse/abuse.ts
@@ -40,7 +40,7 @@ import { ScopeNames as VideoScopeNames, VideoModel } from '../video/video'
 import { VideoBlacklistModel } from '../video/video-blacklist'
 import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
 import { ScopeNames as CommentScopeNames, VideoCommentModel } from '../video/video-comment'
-import { buildAbuseListQuery, BuildAbusesQueryOptions } from './abuse-query-builder'
+import { buildAbuseListQuery, BuildAbusesQueryOptions } from './sql/abuse-query-builder'
 import { VideoAbuseModel } from './video-abuse'
 import { VideoCommentAbuseModel } from './video-comment-abuse'
 
diff --git a/server/models/abuse/abuse-query-builder.ts b/server/models/abuse/sql/abuse-query-builder.ts
index 74f4542e5..854f0cda8 100644
--- a/server/models/abuse/abuse-query-builder.ts
+++ b/server/models/abuse/sql/abuse-query-builder.ts
@@ -2,7 +2,7 @@
 import { exists } from '@server/helpers/custom-validators/misc'
 import { forceNumber } from '@shared/core-utils'
 import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models'
-import { buildBlockedAccountSQL, buildDirectionAndField } from '../utils'
+import { buildBlockedAccountSQL, buildDirectionAndField } from '../../utils'
 
 export type BuildAbusesQueryOptions = {
   start: number
diff --git a/server/models/account/account.ts b/server/models/account/account.ts
index 8a7dfba94..fca1b7b6d 100644
--- a/server/models/account/account.ts
+++ b/server/models/account/account.ts
@@ -38,7 +38,7 @@ import { ApplicationModel } from '../application/application'
 import { ServerModel } from '../server/server'
 import { ServerBlocklistModel } from '../server/server-blocklist'
 import { UserModel } from '../user/user'
-import { getSort, throwIfNotValid } from '../utils'
+import { buildSQLAttributes, getSort, throwIfNotValid } from '../utils'
 import { VideoModel } from '../video/video'
 import { VideoChannelModel } from '../video/video-channel'
 import { VideoCommentModel } from '../video/video-comment'
@@ -251,6 +251,18 @@ export class AccountModel extends Model<Partial<AttributesOnly<AccountModel>>> {
     return undefined
   }
 
+  // ---------------------------------------------------------------------------
+
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
   static load (id: number, transaction?: Transaction): Promise<MAccountDefault> {
     return AccountModel.findByPk(id, { transaction })
   }
diff --git a/server/models/actor/actor-follow.ts b/server/models/actor/actor-follow.ts
index 9615229dd..a849817d5 100644
--- a/server/models/actor/actor-follow.ts
+++ b/server/models/actor/actor-follow.ts
@@ -38,7 +38,7 @@ import { ACTOR_FOLLOW_SCORE, CONSTRAINTS_FIELDS, FOLLOW_STATES, SERVER_ACTOR_NAM
 import { AccountModel } from '../account/account'
 import { ServerModel } from '../server/server'
 import { doesExist } from '../shared/query'
-import { createSafeIn, getSort, searchAttribute, throwIfNotValid } from '../utils'
+import { buildSQLAttributes, createSafeIn, getSort, searchAttribute, throwIfNotValid } from '../utils'
 import { VideoChannelModel } from '../video/video-channel'
 import { ActorModel, unusedActorAttributesForAPI } from './actor'
 import { InstanceListFollowersQueryBuilder, ListFollowersOptions } from './sql/instance-list-followers-query-builder'
@@ -140,6 +140,18 @@ export class ActorFollowModel extends Model<Partial<AttributesOnly<ActorFollowMo
     })
   }
 
+  // ---------------------------------------------------------------------------
+
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
   /*
    * @deprecated Use `findOrCreateCustom` instead
   */
diff --git a/server/models/actor/actor-image.ts b/server/models/actor/actor-image.ts
index f2b3b2f4b..83898cd38 100644
--- a/server/models/actor/actor-image.ts
+++ b/server/models/actor/actor-image.ts
@@ -22,7 +22,7 @@ import { isActivityPubUrlValid } from '../../helpers/custom-validators/activityp
 import { logger } from '../../helpers/logger'
 import { CONFIG } from '../../initializers/config'
 import { LAZY_STATIC_PATHS, MIMETYPES, WEBSERVER } from '../../initializers/constants'
-import { throwIfNotValid } from '../utils'
+import { buildSQLAttributes, throwIfNotValid } from '../utils'
 import { ActorModel } from './actor'
 
 @Table({
@@ -94,6 +94,18 @@ export class ActorImageModel extends Model<Partial<AttributesOnly<ActorImageMode
       .catch(err => logger.error('Cannot remove actor image file %s.', instance.filename, { err }))
   }
 
+  // ---------------------------------------------------------------------------
+
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
   static loadByName (filename: string) {
     const query = {
       where: {
diff --git a/server/models/actor/actor.ts b/server/models/actor/actor.ts
index d7afa727d..a62e6030a 100644
--- a/server/models/actor/actor.ts
+++ b/server/models/actor/actor.ts
@@ -55,7 +55,7 @@ import {
 import { AccountModel } from '../account/account'
 import { getServerActor } from '../application/application'
 import { ServerModel } from '../server/server'
-import { isOutdated, throwIfNotValid } from '../utils'
+import { buildSQLAttributes, isOutdated, throwIfNotValid } from '../utils'
 import { VideoModel } from '../video/video'
 import { VideoChannelModel } from '../video/video-channel'
 import { ActorFollowModel } from './actor-follow'
@@ -65,7 +65,7 @@ enum ScopeNames {
   FULL = 'FULL'
 }
 
-export const unusedActorAttributesForAPI = [
+export const unusedActorAttributesForAPI: (keyof AttributesOnly<ActorModel>)[] = [
   'publicKey',
   'privateKey',
   'inboxUrl',
@@ -306,6 +306,27 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
   })
   VideoChannel: VideoChannelModel
 
+  // ---------------------------------------------------------------------------
+
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+
+  static getSQLAPIAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix,
+      excludeAttributes: unusedActorAttributesForAPI
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
   static async load (id: number): Promise<MActor> {
     const actorServer = await getServerActor()
     if (id === actorServer.id) return actorServer
diff --git a/server/models/actor/sql/shared/actor-follow-table-attributes.ts b/server/models/actor/sql/shared/actor-follow-table-attributes.ts
index 156b37d44..7dd908ece 100644
--- a/server/models/actor/sql/shared/actor-follow-table-attributes.ts
+++ b/server/models/actor/sql/shared/actor-follow-table-attributes.ts
@@ -1,62 +1,31 @@
+import { logger } from '@server/helpers/logger'
+import { Memoize } from '@server/helpers/memoize'
+import { ServerModel } from '@server/models/server/server'
+import { ActorModel } from '../../actor'
+import { ActorFollowModel } from '../../actor-follow'
+import { ActorImageModel } from '../../actor-image'
+
 export class ActorFollowTableAttributes {
 
+  @Memoize()
   getFollowAttributes () {
-    return [
-      '"ActorFollowModel"."id"',
-      '"ActorFollowModel"."state"',
-      '"ActorFollowModel"."score"',
-      '"ActorFollowModel"."url"',
-      '"ActorFollowModel"."actorId"',
-      '"ActorFollowModel"."targetActorId"',
-      '"ActorFollowModel"."createdAt"',
-      '"ActorFollowModel"."updatedAt"'
-    ].join(', ')
+    logger.error('coucou')
+
+    return ActorFollowModel.getSQLAttributes('ActorFollowModel').join(', ')
   }
 
+  @Memoize()
   getActorAttributes (actorTableName: string) {
-    return [
-      `"${actorTableName}"."id" AS "${actorTableName}.id"`,
-      `"${actorTableName}"."type" AS "${actorTableName}.type"`,
-      `"${actorTableName}"."preferredUsername" AS "${actorTableName}.preferredUsername"`,
-      `"${actorTableName}"."url" AS "${actorTableName}.url"`,
-      `"${actorTableName}"."publicKey" AS "${actorTableName}.publicKey"`,
-      `"${actorTableName}"."privateKey" AS "${actorTableName}.privateKey"`,
-      `"${actorTableName}"."followersCount" AS "${actorTableName}.followersCount"`,
-      `"${actorTableName}"."followingCount" AS "${actorTableName}.followingCount"`,
-      `"${actorTableName}"."inboxUrl" AS "${actorTableName}.inboxUrl"`,
-      `"${actorTableName}"."outboxUrl" AS "${actorTableName}.outboxUrl"`,
-      `"${actorTableName}"."sharedInboxUrl" AS "${actorTableName}.sharedInboxUrl"`,
-      `"${actorTableName}"."followersUrl" AS "${actorTableName}.followersUrl"`,
-      `"${actorTableName}"."followingUrl" AS "${actorTableName}.followingUrl"`,
-      `"${actorTableName}"."remoteCreatedAt" AS "${actorTableName}.remoteCreatedAt"`,
-      `"${actorTableName}"."serverId" AS "${actorTableName}.serverId"`,
-      `"${actorTableName}"."createdAt" AS "${actorTableName}.createdAt"`,
-      `"${actorTableName}"."updatedAt" AS "${actorTableName}.updatedAt"`
-    ].join(', ')
+    return ActorModel.getSQLAttributes(actorTableName, `${actorTableName}.`).join(', ')
   }
 
+  @Memoize()
   getServerAttributes (actorTableName: string) {
-    return [
-      `"${actorTableName}->Server"."id" AS "${actorTableName}.Server.id"`,
-      `"${actorTableName}->Server"."host" AS "${actorTableName}.Server.host"`,
-      `"${actorTableName}->Server"."redundancyAllowed" AS "${actorTableName}.Server.redundancyAllowed"`,
-      `"${actorTableName}->Server"."createdAt" AS "${actorTableName}.Server.createdAt"`,
-      `"${actorTableName}->Server"."updatedAt" AS "${actorTableName}.Server.updatedAt"`
-    ].join(', ')
+    return ServerModel.getSQLAttributes(`${actorTableName}->Server`, `${actorTableName}.Server.`).join(', ')
   }
 
+  @Memoize()
   getAvatarAttributes (actorTableName: string) {
-    return [
-      `"${actorTableName}->Avatars"."id" AS "${actorTableName}.Avatars.id"`,
-      `"${actorTableName}->Avatars"."filename" AS "${actorTableName}.Avatars.filename"`,
-      `"${actorTableName}->Avatars"."height" AS "${actorTableName}.Avatars.height"`,
-      `"${actorTableName}->Avatars"."width" AS "${actorTableName}.Avatars.width"`,
-      `"${actorTableName}->Avatars"."fileUrl" AS "${actorTableName}.Avatars.fileUrl"`,
-      `"${actorTableName}->Avatars"."onDisk" AS "${actorTableName}.Avatars.onDisk"`,
-      `"${actorTableName}->Avatars"."type" AS "${actorTableName}.Avatars.type"`,
-      `"${actorTableName}->Avatars"."actorId" AS "${actorTableName}.Avatars.actorId"`,
-      `"${actorTableName}->Avatars"."createdAt" AS "${actorTableName}.Avatars.createdAt"`,
-      `"${actorTableName}->Avatars"."updatedAt" AS "${actorTableName}.Avatars.updatedAt"`
-    ].join(', ')
+    return ActorImageModel.getSQLAttributes(`${actorTableName}->Avatars`, `${actorTableName}.Avatars.`).join(', ')
   }
 }
diff --git a/server/models/server/server.ts b/server/models/server/server.ts
index ef42de090..f6cb40f60 100644
--- a/server/models/server/server.ts
+++ b/server/models/server/server.ts
@@ -4,7 +4,7 @@ import { MServer, MServerFormattable } from '@server/types/models/server'
 import { AttributesOnly } from '@shared/typescript-utils'
 import { isHostValid } from '../../helpers/custom-validators/servers'
 import { ActorModel } from '../actor/actor'
-import { throwIfNotValid } from '../utils'
+import { buildSQLAttributes, throwIfNotValid } from '../utils'
 import { ServerBlocklistModel } from './server-blocklist'
 
 @Table({
@@ -52,6 +52,18 @@ export class ServerModel extends Model<Partial<AttributesOnly<ServerModel>>> {
   })
   BlockedBy: ServerBlocklistModel[]
 
+  // ---------------------------------------------------------------------------
+
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
   static load (id: number, transaction?: Transaction): Promise<MServer> {
     const query = {
       where: {
diff --git a/server/models/utils.ts b/server/models/utils.ts
index 0b6ac8340..69ad123ac 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -1,6 +1,7 @@
-import { literal, Op, OrderItem, Sequelize } from 'sequelize'
+import { literal, Model, ModelStatic, Op, OrderItem, Sequelize } from 'sequelize'
 import validator from 'validator'
 import { forceNumber } from '@shared/core-utils'
+import { AttributesOnly } from '@shared/typescript-utils'
 
 type SortType = { sortModel: string, sortValue: string }
 
@@ -178,30 +179,6 @@ function buildBlockedAccountSQL (blockerIds: number[]) {
     'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
 }
 
-function buildBlockedAccountSQLOptimized (columnNameJoin: string, blockerIds: number[]) {
-  const blockerIdsString = blockerIds.join(', ')
-
-  return [
-    literal(
-      `NOT EXISTS (` +
-      `  SELECT 1 FROM "accountBlocklist" ` +
-      `  WHERE "targetAccountId" = ${columnNameJoin} ` +
-      `  AND "accountId" IN (${blockerIdsString})` +
-      `)`
-    ),
-
-    literal(
-      `NOT EXISTS (` +
-      `  SELECT 1 FROM "account" ` +
-      `  INNER JOIN "actor" ON account."actorId" = actor.id ` +
-      `  INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` +
-      `  WHERE "account"."id" = ${columnNameJoin} ` +
-      `  AND "serverBlocklist"."accountId" IN (${blockerIdsString})` +
-      `)`
-    )
-  ]
-}
-
 function buildServerIdsFollowedBy (actorId: any) {
   const actorIdNumber = forceNumber(actorId)
 
@@ -277,11 +254,34 @@ function searchAttribute (sourceField?: string, targetField?: string) {
   }
 }
 
+function buildSQLAttributes <M extends Model> (options: {
+  model: ModelStatic<M>
+  tableName: string
+
+  excludeAttributes?: (keyof AttributesOnly<M>)[]
+  aliasPrefix?: string
+}) {
+  const { model, tableName, aliasPrefix, excludeAttributes } = options
+
+  const attributes = Object.keys(model.getAttributes())
+
+  return attributes
+    .filter(a => {
+      if (!excludeAttributes) return true
+      if (excludeAttributes.includes(a)) return false
+
+      return true
+    })
+    .map(a => {
+      return `"${tableName}"."${a}" AS "${aliasPrefix || ''}${a}"`
+    })
+}
+
 // ---------------------------------------------------------------------------
 
 export {
+  buildSQLAttributes,
   buildBlockedAccountSQL,
-  buildBlockedAccountSQLOptimized,
   buildLocalActorIdsIn,
   getPlaylistSort,
   SortType,
diff --git a/server/models/video/sql/comment/video-comment-table-attributes.ts b/server/models/video/sql/comment/video-comment-table-attributes.ts
index cae3c1683..10e635e41 100644
--- a/server/models/video/sql/comment/video-comment-table-attributes.ts
+++ b/server/models/video/sql/comment/video-comment-table-attributes.ts
@@ -1,33 +1,23 @@
+import { Memoize } from '@server/helpers/memoize'
+import { AccountModel } from '@server/models/account/account'
+import { ActorModel } from '@server/models/actor/actor'
+import { ActorImageModel } from '@server/models/actor/actor-image'
+import { ServerModel } from '@server/models/server/server'
+import { VideoCommentModel } from '../../video-comment'
+
 export class VideoCommentTableAttributes {
 
+  @Memoize()
   getVideoCommentAttributes () {
-    return [
-      '"VideoCommentModel"."id"',
-      '"VideoCommentModel"."url"',
-      '"VideoCommentModel"."deletedAt"',
-      '"VideoCommentModel"."updatedAt"',
-      '"VideoCommentModel"."createdAt"',
-      '"VideoCommentModel"."text"',
-      '"VideoCommentModel"."originCommentId"',
-      '"VideoCommentModel"."inReplyToCommentId"',
-      '"VideoCommentModel"."videoId"',
-      '"VideoCommentModel"."accountId"'
-    ].join(', ')
+    return VideoCommentModel.getSQLAttributes('VideoCommentModel').join(', ')
   }
 
+  @Memoize()
   getAccountAttributes () {
-    return [
-      `"Account"."id" AS "Account.id"`,
-      `"Account"."name" AS "Account.name"`,
-      `"Account"."description" AS "Account.description"`,
-      `"Account"."createdAt" AS "Account.createdAt"`,
-      `"Account"."updatedAt" AS "Account.updatedAt"`,
-      `"Account"."actorId" AS "Account.actorId"`,
-      `"Account"."userId" AS "Account.userId"`,
-      `"Account"."applicationId" AS "Account.applicationId"`
-    ].join(', ')
+    return AccountModel.getSQLAttributes('Account', 'Account.').join(', ')
   }
 
+  @Memoize()
   getVideoAttributes () {
     return [
       `"Video"."id" AS "Video.id"`,
@@ -36,43 +26,18 @@ export class VideoCommentTableAttributes {
     ].join(', ')
   }
 
+  @Memoize()
   getActorAttributes () {
-    return [
-      `"Account->Actor"."id" AS "Account.Actor.id"`,
-      `"Account->Actor"."type" AS "Account.Actor.type"`,
-      `"Account->Actor"."preferredUsername" AS "Account.Actor.preferredUsername"`,
-      `"Account->Actor"."url" AS "Account.Actor.url"`,
-      `"Account->Actor"."followersCount" AS "Account.Actor.followersCount"`,
-      `"Account->Actor"."followingCount" AS "Account.Actor.followingCount"`,
-      `"Account->Actor"."remoteCreatedAt" AS "Account.Actor.remoteCreatedAt"`,
-      `"Account->Actor"."serverId" AS "Account.Actor.serverId"`,
-      `"Account->Actor"."createdAt" AS "Account.Actor.createdAt"`,
-      `"Account->Actor"."updatedAt" AS "Account.Actor.updatedAt"`
-    ].join(', ')
+    return ActorModel.getSQLAPIAttributes('Account->Actor', `Account.Actor.`).join(', ')
   }
 
+  @Memoize()
   getServerAttributes () {
-    return [
-      `"Account->Actor->Server"."id" AS "Account.Actor.Server.id"`,
-      `"Account->Actor->Server"."host" AS "Account.Actor.Server.host"`,
-      `"Account->Actor->Server"."redundancyAllowed" AS "Account.Actor.Server.redundancyAllowed"`,
-      `"Account->Actor->Server"."createdAt" AS "Account.Actor.Server.createdAt"`,
-      `"Account->Actor->Server"."updatedAt" AS "Account.Actor.Server.updatedAt"`
-    ].join(', ')
+    return ServerModel.getSQLAttributes('Account->Actor->Server', `Account.Actor.Server.`).join(', ')
   }
 
+  @Memoize()
   getAvatarAttributes () {
-    return [
-      `"Account->Actor->Avatars"."id" AS "Account.Actor.Avatars.id"`,
-      `"Account->Actor->Avatars"."filename" AS "Account.Actor.Avatars.filename"`,
-      `"Account->Actor->Avatars"."height" AS "Account.Actor.Avatars.height"`,
-      `"Account->Actor->Avatars"."width" AS "Account.Actor.Avatars.width"`,
-      `"Account->Actor->Avatars"."fileUrl" AS "Account.Actor.Avatars.fileUrl"`,
-      `"Account->Actor->Avatars"."onDisk" AS "Account.Actor.Avatars.onDisk"`,
-      `"Account->Actor->Avatars"."type" AS "Account.Actor.Avatars.type"`,
-      `"Account->Actor->Avatars"."actorId" AS "Account.Actor.Avatars.actorId"`,
-      `"Account->Actor->Avatars"."createdAt" AS "Account.Actor.Avatars.createdAt"`,
-      `"Account->Actor->Avatars"."updatedAt" AS "Account.Actor.Avatars.updatedAt"`
-    ].join(', ')
+    return ActorImageModel.getSQLAttributes('Account->Actor->Avatars', 'Account.Actor.Avatars.id').join(', ')
   }
 }
diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts
index fb9d15e55..28a3d723d 100644
--- a/server/models/video/video-comment.ts
+++ b/server/models/video/video-comment.ts
@@ -40,7 +40,7 @@ import {
 import { VideoCommentAbuseModel } from '../abuse/video-comment-abuse'
 import { AccountModel } from '../account/account'
 import { ActorModel } from '../actor/actor'
-import { buildLocalAccountIdsIn, throwIfNotValid } from '../utils'
+import { buildLocalAccountIdsIn, buildSQLAttributes, throwIfNotValid } from '../utils'
 import { ListVideoCommentsOptions, VideoCommentListQueryBuilder } from './sql/comment/video-comment-list-query-builder'
 import { VideoModel } from './video'
 import { VideoChannelModel } from './video-channel'
@@ -192,6 +192,18 @@ export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoComment
   })
   CommentAbuses: VideoCommentAbuseModel[]
 
+  // ---------------------------------------------------------------------------
+
+  static getSQLAttributes (tableName: string, aliasPrefix = '') {
+    return buildSQLAttributes({
+      model: this,
+      tableName,
+      aliasPrefix
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
   static loadById (id: number, t?: Transaction): Promise<MComment> {
     const query: FindOptions = {
       where: {