Query to get data ordered by the number of items in a relation

Question

Let’s say I have this typical datamodel, the one used in many tutorials:

type User {
  id: ID! @unique
  name: String!
  posts: [Post!]!
}

type Post {
  id: ID! @unique
  title: String!
  content: String!
  published: Boolean! @default(value: "false")
  author: User!
}

Is there a query I can build to get a list of, let’s say, the 10 Users with more Posts?? Basically I need to query ordering by “count” of Posts… but I haven’t found a way to do it

Any help will be highly appreciated

Cheers

Have you gone through https://github.com/prisma/prisma/issues/95 thread? — Shivam Pandey, Dec 05 '18 at 05:54
I haven't. but is it what I'm asking for? what I want is to get the users ordered by the number of posts. so count(Posts) for every user and ordered by that number. I don't think they are talking about the same in that issue, are they? — JV Lobo, Dec 05 '18 at 06:13

Errorname · Answer 1 · 2018-12-06T08:45:48.447

3

As @shivam-panday said in a comment, this is currently not implemented in Prisma (See issue: https://github.com/prisma/prisma/issues/95 )

This comment especially explains your problem:

It would be great to be able to order by "to-many" related fields as well (by the count of related items).

For example, to get a list of the top 10 most voted-for links (assuming votes is a related field of type [Vote!]!):
query {
  allLinks(first: 10, orderBy: votes_DESC) {
    id
    url
    description
    _votesMeta {
      count
    }
  }
}
Currently, to get that list you'd have to query for every Link and then sort/slice it on the client, which is potentially a ton of overfetching.

Comment in question: https://github.com/prisma/prisma/issues/95#issuecomment-320433296

edited Dec 06 '18 at 08:45

answered Dec 05 '18 at 14:53

Errorname

2,228
13
23

thank you for your answer! yes that is true. that's what I need... since it's not possible to do it with the current Prisma implementation, any workarounds you guys can think of? cheers :) – JV Lobo Dec 06 '18 at 05:27
Using Prisma api, I'm not sure there is a way to do so which doesn't require retrieving all the users with there votes, and then doing the sort yourself. I don't think this is a good solution as it would be resource-heavy on your backend. However, you can use the raw access to the database to query your users and sort them. You would retrieve their IDs and then do a simple Prisma query to get what you need on them. (https://www.prisma.io/docs/prisma-graphql-api/reference/raw-database-access-qwe4/#overview) – Errorname Dec 06 '18 at 09:20

score 0 · Answer 2 · answered Aug 27 '19 at 06:56

I came across the same issue with Prisma and this is a major problem. Retrieving all the users with all their posts, and sorting by the number of their posts is not a practical solution.

The workaround I can think of is to track the number of posts (every time when adding/deleting)and store it in User.

type User {
  id: ID! @unique
  name: String!
  postsCount: Int!
  posts: [Post!]!
}

This way, Prisma will expose the sorting options for the postsCount.

I am not sure if Prisma 2 offers a proper solution for this issue... Does anybody know?

Query to get data ordered by the number of items in a relation

2 Answers2