Find users that follow each other on a Prisma self-relation-CodePudding

Given a user id, I want to find the followers that the user follows (i.e. follow each other)

My Prisma model looks like the following:

model User {
  id         Int       @id @default(autoincrement())
  name       String?
  followedBy Follows[] @relation("following")
  following  Follows[] @relation("follower")
}

model Follows {
  follower    User @relation("follower", fields: [followerId], references: [id])
  followerId  Int
  following   User @relation("following", fields: [followingId], references: [id])
  followingId Int

  @@id([followerId, followingId])
}

I am also interested in counting them - this could be done in a separate query since the former might require pagination at some point.

Thank you in advance for your help.

CodePudding user response：

Probably a raw query will do

function getFollowersMutual(userId: User["id"]) {
  return prisma.$queryRawUnsafe<User[]>(
    `
    SELECT u.* FROM "Follow" AS f1
    INNER JOIN "Follow" AS f2
      ON f2."followerId" = f1."followingId"
    INNER JOIN "User" AS u
      ON f2."followingId" = u."id"
    WHERE f1."followingId" = '${userId}';`.trim()
  );
}

and counting:

function getFollowersMutualCount(userId: User["id"]) {
  return Number(
    (await prisma.$queryRawUnsafe<{ count: number }[]>(
      `
      SELECT COUNT(*) FROM "Follow" AS f1
      INNER JOIN "Follow" AS f2
        ON f2."followerId" = f1."followingId"
      WHERE f1."followingId" = '${userId}';`.trim()
    ))[0].count
  )
}

sadly it seems that Prisma's queryRaw doesn't support AS so I had to use queryRawUnsafe and need to sanitize the input (not done in the code above).

CodePudding user response：

To find the mutual followers you can do something like this:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  await prisma.user.createMany({
    data: [
      {
        id: 1,
        name: 'User 1',
      },
      {
        id: 2,
        name: 'User 2',
      },
      {
        id: 3,
        name: 'User 3',
      },
    ],
  });

  // // User 1 follows User 2 and User 3
  // // User 2 follows User 1
  await prisma.follows.createMany({
    data: [
      {
        followerId: 1,
        followingId: 2,
      },
      {
        followerId: 1,
        followingId: 3,
      },
      {
        followerId: 2,
        followingId: 1,
      },
    ],
  });

  // For User id 1, get all the followers and all the users they follow
  const users = await prisma.follows.findMany({
    where: {
      OR: [
        {
          followerId: 1,
        },
        {
          followingId: 1,
        },
      ],
    },
    include: {
      following: true,
    },
  });

  console.log(users);

  const mutuals = [];

  // For each user, check if they follow each other
  for (let i = 0; i < users.length; i  ) {
    const user = users[i];

    for (let j = i   1; j < users.length; j  ) {
      const innerUser = users[j];

      if (
        user.followerId === innerUser.followingId &&
        user.followingId === innerUser.followerId
      ) {
        mutuals.push(user);
      }
    }
  }

  console.log(mutuals);
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

This requires some post processing of records to find the mutual follows of each other.

Here's the output:

[
  {
    followerId: 1,
    followingId: 2,
    following: { id: 2, name: 'User 2' }
  }
]