Filter on count of relations

[OllieJennings]

Problem

Lets image the following models

model User {
  id String @id @default(cuid())
  name String

  followers Follower[] @relation('userFollowers')
}

model Follower {
  id String @id @default(cuid())
  following User @relation('userFollowers', field: [followingId], references: [id])
  followingId String
}

Now saying if l am trying to show the following: Give me all users who have greater than 50 followers

I want to be able to do this in a single findMany query

Suggested solution

This is the type of query l would want to run:

await prisma.user.findMany({
  where: {
    followers: {
       _count: {
        gt: 50
      }
    }
  }
});

Alternatives

I have looked at the groupBy and aggregate methods, but none allow me to achieve what l want above.

Annoyling l have had to use rawSQL which means l need to fully implement the whole take / include / where functionality by hand, so this would be a big bonus to that.

Additional context

These type of filters / filtering are extremely common, and it would be great if prisma supported such features.

[janpio]

Thanks for your issue @OllieJennings. Is this a duplicate of #8935?

[OllieJennings]

@janpio yes it is! apologies tried searching and couldn't find the issue you just linked, happy to close this as this is effectively a dupe

[janpio]

All good, I understand how it can be a lot of issues to go or search through. Closing this now.

Make sure to 👍 the other issue and also subscribe to it via the right hand sidebar so you are notified of updated. Thanks!