Mysql 如何有效地计算共同的朋友追随者

我需要计算用户之间的共同好友，目前它工作正常，但我不认为它将工作的用户谁有一个大的追随者基础，它将需要很长的时间加载和比较.请建议我良好的数据库设计/查询(如果需要). 我创建了表，并有一个如下查询:

表:

export enum FollowStatus {
  REQUESTED = "REQUESTED",
  APPROVED = "APPROVED",
  DECLINED = "DECLINED"
}

@Entity("followers")
export class Followers {
  @PrimaryGeneratedColumn()
  @Property()
  id: number;

  @Property()
  @Column()
  followerId: string;

  @ManyToOne(() => Users, { eager: true })
  @JoinColumn({ name: "followerId" })
  follower: Users;

  @Property()
  @Column()
  followedId: string;

  @ManyToOne(() => Users, { eager: true })
  @JoinColumn({ name: "followedId" })
  followed: Users;

  @Column({
    type: "enum",
    enum: FollowStatus,
    default: FollowStatus.REQUESTED
  })
  @Property()
  status: FollowStatus;
}

帖子主题:回复:

  async getFollowers(userId: string, page: number, limit: number) {
    const builder: SelectQueryBuilder<Followers> = this.createQueryBuilder("followers")
      .where("followers.followedId = :userId", { userId })
      .andWhere("followers.status = :status", { status: "APPROVED" })
      .leftJoinAndSelect("followers.follower", "follower")
      .leftJoinAndSelect("follower.followers", "followerFollowers")
      .take(limit)
      .skip((page - 1) * limit);

    const followers = await builder.getManyAndCount();
    return followers;
  }

主要功能:

async getFollowers(userId: string, page: number, limit: number) {
    page = page || DEFAULT_PAGE_NUMBER;
    limit = limit || DEFAULT_PAGE_SIZE;

    const followers = await this.followersRepository.getFollowers(userId, page, limit);
    const myFollowers = followers[0].map((follower) => follower.followerId);

    const response: SearchResponse<FollowerUsersInterface> = {
      data: await Promise.all(
        followers[0].map(async (user) => {
          const isFollowing = user.follower.followers.findIndex((follower) => follower.followerId == userId);

          const userFollowers = user.follower.followers;

          // count mutual followers, exluding the user itself
          const mutualFollowersCount = userFollowers.filter((follower) => {
            if (follower.followerId !== user.followerId) {
              return myFollowers.some((myFollower) => myFollower === follower.followerId && follower.status === FollowStatus.APPROVED);
            }
          }).length;

          return {
            isFollowing: isFollowing > -1,
            userId: user.follower.id,
            userName: user.follower.userName,
            profilePic: await this.s3.getSignedUrl(user.follower.profilePic),
            mutualFollowers: mutualFollowersCount
          } as FollowerUsersInterface;
        })
      ),
      totalCount: followers[1]
    };

    return response;
  }

数据库记录示例:

# id    status     followerId     followedId
20    APPROVED    3              PBpq
22    APPROVED    1              PBpq
24    APPROVED    2              PBpq
25    APPROVED    PBpq           2
26    APPROVED    1              2
28    APPROVED    INOr           PBpq
29    APPROVED    2NAo           PBpq
34    APPROVED    1              2NAo

• 预期输出:
• 假设你的userId是PBpq
• 如果我访问userId 2，我会看到我共同关注者计数为1
• 解释:me和userId 1都在userId 2之后，所以我会得到mutual count 1