GraphQL filter/order based on result count of relation

Question

I am kind of new to GraphQL world and i am stuck at a, i would say, very basic problem. I dug around couple of docs and guides and i could not find any hint if it is even possible or not with GraphQL or what might be a workaround.

What i want to achieve, is a simple query, from a famous example (Post/Author) found on various guides: get all authors with more than one post or at least get all authors sorted by their count of posts.

It is meant from the perspective of consuming a GraphQL from the browser. I know it might also depend on the GraphQL server implementation, i am using Prisma. But also hints to solutions in whatever server solution are appreciated.

After quite some digging i found no hints if something like this might be possible and where to start even. I wonder if that is such a special use-case that it is not mentioned at all in any guide i found. So quite frankly i am not sure how to tackle this. Is it even possible? If not what might a workaround be? Filtering after the query might not work, because of course a scenario with thousands of authors come to mind, where pagination and performance might collapse.

model User {
  id                          String              @id @default(cuid())
  name                        String              @default("")
  email                       String              @unique @default("")
  password                    String
  posts                       Post[]              @relation("Post_author")
  createdAt                   DateTime?           @default(now())
}

model Post {
  id       String  @id @default(cuid())
  title    String  @default("")
  content  Json    @default("[{\"type\":\"paragraph\",\"children\":[{\"text\":\"\"}]}]")
  author   User?   @relation("Post_author", fields: [authorId], references: [id])
  authorId String? @map("author")
  tags     Tag[]   @relation("Post_tags")

  @@index([authorId])
}

Any help appreciated.

Answer 1

The important bits of Doc are located here: https://www.prisma.io/docs/orm/prisma-client/queries/aggregation-grouping-summarizing#return-a-relations-count-with-include

With that i ended up writing a query like this:

   await prisma.User.findMany({
        take: PAGE_SIZE,
        skip: PAGE_SIZE * (pageNum - 1),
        orderBy: {
            posts: {
              _count: 'desc',
            },
        },
        where: {
            posts: {
                some: {
                    status: 'active',
                }
            },
        },
        include: {
            _count: {
                select: {
                    posts: {
                        where: { status: 'active' },
                    }
                }
            }
        },
    });

The important part is the include directive which includes the count in the query result. I also added sorting and a custom filter based on all users having an 'active' post here.