Question 113750 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Probably a raw query will do

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

    and counting:

    function getFollowersMutualCount(userId: User["id"]) {
  return Number(
    (await prisma.$queryRawUnsafe<{ count: number }[]>(
      `SELECT COUNT(*) FROM "Follows" AS f1
        INNER JOIN "Follow" AS f2
          ON f1."followingId" = f2."followerId"
       WHERE f1."followingId" = '${userId}'
       AND f2."followingId" = f1."followerId"`
    ))[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).


  2. 0

    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' }
  }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search