List of users who share at least one group

[lethal moat]

I have the following models:

model User {
  id String @id
  username String
  displayName String
  wishlist String
  groups Group[]
}

model Group {
  id String @id
  name String
  members User[]
}

When a user looks at the wishlists page, it should contain a list of all users who are in any of the groups that the user is in. What's the best way to accomplish this?

Is there a way to do it with one query or do I need to fetch the groups the user is in then fetch all of the users who have any of the groups?

[granite socket]

Hi

To fetch a list of users who are in any of the groups that a specific user is in, you can use Prisma's findMany function with the where and include options.

Then, you can use the findMany function with the where option to filter users based on the groups. In the where clause, you can use the some keyword to filter users who are in any of the groups that the specific user is in.

[lethal moat]

Yes, but can I go from having the ID of a single user to all of the data for all users that share a group in a single query? Or do I need to fetch what groups they're in first then fetch users in those groups?

[granite socket]

You will have to do a JOIN query for that. Something like that:

const userId = 'some-user-id';
const users = await prisma.$queryRaw(`
 SELECT u.*
 FROM "User" u
 JOIN "Group" g ON u."id" = ANY(g."members")
 WHERE g."id" IN (
   SELECT g."id"
   FROM "Group" g
   WHERE g."id" = ANY(
     SELECT u."groups"
     FROM "User" u
     WHERE u."id" = $1
   )
 )
`, userId);