Convert followers/following in raw SQL queries

Prevent weird bug in SQL generation

2 files changed, 159 insertions, 0 deletions

diff --git a/server/models/actor/sql/shared/actor-follow-table-attributes.ts b/server/models/actor/sql/shared/actor-follow-table-attributes.ts
new file mode 100644
index 000000000..156b37d44
--- /dev/null
+++ b/server/models/actor/sql/shared/actor-follow-table-attributes.ts
@@ -0,0 +1,62 @@
+export class ActorFollowTableAttributes {
+
+  getFollowAttributes () {
+    return [
+      '"ActorFollowModel"."id"',
+      '"ActorFollowModel"."state"',
+      '"ActorFollowModel"."score"',
+      '"ActorFollowModel"."url"',
+      '"ActorFollowModel"."actorId"',
+      '"ActorFollowModel"."targetActorId"',
+      '"ActorFollowModel"."createdAt"',
+      '"ActorFollowModel"."updatedAt"'
+    ].join(', ')
+  }
+
+  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(', ')
+  }
+
+  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(', ')
+  }
+
+  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(', ')
+  }
+}
diff --git a/server/models/actor/sql/shared/instance-list-follows-query-builder.ts b/server/models/actor/sql/shared/instance-list-follows-query-builder.ts
new file mode 100644
index 000000000..1d70fbe70
--- /dev/null
+++ b/server/models/actor/sql/shared/instance-list-follows-query-builder.ts
@@ -0,0 +1,97 @@
+import { Sequelize } from 'sequelize'
+import { AbstractRunQuery } from '@server/models/shared'
+import { getInstanceFollowsSort } from '@server/models/utils'
+import { ActorImageType } from '@shared/models'
+import { ActorFollowTableAttributes } from './actor-follow-table-attributes'
+
+type BaseOptions = {
+  sort: string
+  count: number
+  start: number
+}
+
+export abstract class InstanceListFollowsQueryBuilder <T extends BaseOptions> extends AbstractRunQuery {
+  protected readonly tableAttributes = new ActorFollowTableAttributes()
+
+  protected innerQuery: string
+
+  constructor (
+    protected readonly sequelize: Sequelize,
+    protected readonly options: T
+  ) {
+    super(sequelize)
+  }
+
+  protected abstract getWhere (): string
+
+  protected getJoins () {
+    return 'INNER JOIN "actor" "ActorFollower" ON "ActorFollower"."id" = "ActorFollowModel"."actorId" ' +
+      'INNER JOIN "actor" "ActorFollowing" ON "ActorFollowing"."id" = "ActorFollowModel"."targetActorId" '
+  }
+
+  protected getServerJoin (actorName: string) {
+    return `LEFT JOIN "server" "${actorName}->Server" ON "${actorName}"."serverId" = "${actorName}->Server"."id" `
+  }
+
+  protected getAvatarsJoin (actorName: string) {
+    return `LEFT JOIN "actorImage" "${actorName}->Avatars" ON "${actorName}.id" = "${actorName}->Avatars"."actorId" ` +
+      `AND "${actorName}->Avatars"."type" = ${ActorImageType.AVATAR}`
+  }
+
+  private buildInnerQuery () {
+    this.innerQuery = `${this.getInnerSelect()} ` +
+      `FROM "actorFollow" AS "ActorFollowModel" ` +
+      `${this.getJoins()} ` +
+      `${this.getServerJoin('ActorFollowing')} ` +
+      `${this.getServerJoin('ActorFollower')} ` +
+      `${this.getWhere()} ` +
+      `${this.getOrder()} ` +
+      `LIMIT :limit OFFSET :offset `
+
+    this.replacements.limit = this.options.count
+    this.replacements.offset = this.options.start
+  }
+
+  protected buildListQuery () {
+    this.buildInnerQuery()
+
+    this.query = `${this.getSelect()} ` +
+      `FROM (${this.innerQuery}) AS "ActorFollowModel" ` +
+      `${this.getAvatarsJoin('ActorFollower')} ` +
+      `${this.getAvatarsJoin('ActorFollowing')} ` +
+      `${this.getOrder()}`
+  }
+
+  protected buildCountQuery () {
+    this.query = `SELECT COUNT(*) AS "total" ` +
+      `FROM "actorFollow" AS "ActorFollowModel" ` +
+      `${this.getJoins()} ` +
+      `${this.getServerJoin('ActorFollowing')} ` +
+      `${this.getServerJoin('ActorFollower')} ` +
+      `${this.getWhere()}`
+  }
+
+  private getInnerSelect () {
+    return this.buildSelect([
+      this.tableAttributes.getFollowAttributes(),
+      this.tableAttributes.getActorAttributes('ActorFollower'),
+      this.tableAttributes.getActorAttributes('ActorFollowing'),
+      this.tableAttributes.getServerAttributes('ActorFollower'),
+      this.tableAttributes.getServerAttributes('ActorFollowing')
+    ])
+  }
+
+  private getSelect () {
+    return this.buildSelect([
+      '"ActorFollowModel".*',
+      this.tableAttributes.getAvatarAttributes('ActorFollower'),
+      this.tableAttributes.getAvatarAttributes('ActorFollowing')
+    ])
+  }
+
+  private getOrder () {
+    const orders = getInstanceFollowsSort(this.options.sort)
+
+    return 'ORDER BY ' + orders.map(o => `"${o[0]}" ${o[1]}`).join(', ')
+  }
+}