4 files changed, 297 insertions, 0 deletions

diff --git a/server/models/actor/sql/instance-list-followers-query-builder.ts b/server/models/actor/sql/instance-list-followers-query-builder.ts
new file mode 100644
index 000000000..4a17a8f11
--- /dev/null
+++ b/server/models/actor/sql/instance-list-followers-query-builder.ts
@@ -0,0 +1,69 @@
+import { Sequelize } from 'sequelize'
+import { ModelBuilder } from '@server/models/shared'
+import { parseRowCountResult } from '@server/models/utils'
+import { MActorFollowActorsDefault } from '@server/types/models'
+import { ActivityPubActorType, FollowState } from '@shared/models'
+import { InstanceListFollowsQueryBuilder } from './shared/instance-list-follows-query-builder'
+
+export interface ListFollowersOptions {
+  actorIds: number[]
+  start: number
+  count: number
+  sort: string
+  state?: FollowState
+  actorType?: ActivityPubActorType
+  search?: string
+}
+
+export class InstanceListFollowersQueryBuilder extends InstanceListFollowsQueryBuilder <ListFollowersOptions> {
+
+  constructor (
+    protected readonly sequelize: Sequelize,
+    protected readonly options: ListFollowersOptions
+  ) {
+    super(sequelize, options)
+  }
+
+  async listFollowers () {
+    this.buildListQuery()
+
+    const results = await this.runQuery({ nest: true })
+    const modelBuilder = new ModelBuilder<MActorFollowActorsDefault>(this.sequelize)
+
+    return modelBuilder.createModels(results, 'ActorFollow')
+  }
+
+  async countFollowers () {
+    this.buildCountQuery()
+
+    const result = await this.runQuery()
+
+    return parseRowCountResult(result)
+  }
+
+  protected getWhere () {
+    let where = 'WHERE "ActorFollowing"."id" IN (:actorIds) '
+    this.replacements.actorIds = this.options.actorIds
+
+    if (this.options.state) {
+      where += 'AND "ActorFollowModel"."state" = :state '
+      this.replacements.state = this.options.state
+    }
+
+    if (this.options.search) {
+      const escapedLikeSearch = this.sequelize.escape('%' + this.options.search + '%')
+
+      where += `AND (` +
+        `"ActorFollower->Server"."host" ILIKE ${escapedLikeSearch} ` +
+        `OR "ActorFollower"."preferredUsername" ILIKE ${escapedLikeSearch} ` +
+      `)`
+    }
+
+    if (this.options.actorType) {
+      where += `AND "ActorFollower"."type" = :actorType `
+      this.replacements.actorType = this.options.actorType
+    }
+
+    return where
+  }
+}
diff --git a/server/models/actor/sql/instance-list-following-query-builder.ts b/server/models/actor/sql/instance-list-following-query-builder.ts
new file mode 100644
index 000000000..880170b85
--- /dev/null
+++ b/server/models/actor/sql/instance-list-following-query-builder.ts
@@ -0,0 +1,69 @@
+import { Sequelize } from 'sequelize'
+import { ModelBuilder } from '@server/models/shared'
+import { parseRowCountResult } from '@server/models/utils'
+import { MActorFollowActorsDefault } from '@server/types/models'
+import { ActivityPubActorType, FollowState } from '@shared/models'
+import { InstanceListFollowsQueryBuilder } from './shared/instance-list-follows-query-builder'
+
+export interface ListFollowingOptions {
+  followerId: number
+  start: number
+  count: number
+  sort: string
+  state?: FollowState
+  actorType?: ActivityPubActorType
+  search?: string
+}
+
+export class InstanceListFollowingQueryBuilder extends InstanceListFollowsQueryBuilder <ListFollowingOptions> {
+
+  constructor (
+    protected readonly sequelize: Sequelize,
+    protected readonly options: ListFollowingOptions
+  ) {
+    super(sequelize, options)
+  }
+
+  async listFollowing () {
+    this.buildListQuery()
+
+    const results = await this.runQuery({ nest: true })
+    const modelBuilder = new ModelBuilder<MActorFollowActorsDefault>(this.sequelize)
+
+    return modelBuilder.createModels(results, 'ActorFollow')
+  }
+
+  async countFollowing () {
+    this.buildCountQuery()
+
+    const result = await this.runQuery()
+
+    return parseRowCountResult(result)
+  }
+
+  protected getWhere () {
+    let where = 'WHERE "ActorFollowModel"."actorId" = :followerId '
+    this.replacements.followerId = this.options.followerId
+
+    if (this.options.state) {
+      where += 'AND "ActorFollowModel"."state" = :state '
+      this.replacements.state = this.options.state
+    }
+
+    if (this.options.search) {
+      const escapedLikeSearch = this.sequelize.escape('%' + this.options.search + '%')
+
+      where += `AND (` +
+        `"ActorFollowing->Server"."host" ILIKE ${escapedLikeSearch} ` +
+        `OR "ActorFollowing"."preferredUsername" ILIKE ${escapedLikeSearch} ` +
+      `)`
+    }
+
+    if (this.options.actorType) {
+      where += `AND "ActorFollowing"."type" = :actorType `
+      this.replacements.actorType = this.options.actorType
+    }
+
+    return where
+  }
+}
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(', ')
+  }
+}