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 github.com/prisma/prisma/issues/95 thread? — Shivam Pandey
– Shivam Pandey, Commented Dec 5, 2018 at 5: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
– JV Lobo, Commented Dec 5, 2018 at 6:13

Errorname · Accepted Answer · 2018-12-06 08:45:48Z

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 6, 2018 at 8:45

answered Dec 5, 2018 at 14:53

Errorname

2,48115 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JV Lobo Over a year ago

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 :)

Errorname Over a year ago

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. (prisma.io/docs/prisma-graphql-api/reference/…)

Noby Fujioka · Accepted Answer · 2019-08-27 06:56:52Z

0

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?

answered Aug 27, 2019 at 6:56

Noby Fujioka

1,8541 gold badge14 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related