Hi, a question about queries in Prisma: A User can have many Posts. How would we show a list of users and their total number of Posts?

users { id posts { id } }

and then counting

user.posts.length

in the frontend would work, but we cannot sort users by post amount from DB if we have pagination, right? I think requesting

users { id postsConnections { aggregate { count } } }

or similar is not supported by Prisma (in one single request), and if it was probably we cannot order by that in the same request. Another solution would be adding a

totalPosts

counter to the user and increment it every time a user writes a post, but atomic increments are not supported either so there could be issues with this as well. Therefore, what’s the recommended way to do this?

If you're using a server in front of Prisma you can add a field to the type with a custom resolver that basically runs

ctx.prisma.postConnection({where: { someIdField: parent.id }}).aggregate.count

something like that? P.S. I'm not 100% on the syntax there - but that's the general gist.

@Jenkins that would work to show the post number but I think there are two issues: I might be wrong on this but I think it makes 1 request to get N users and then N request to count posts. Also, I don’t think it’s possible to add pagination and sort by this field 🤔

You're right... Yeah, I would probably make a feature request for this on GitHub. That way it'll either be added as a feature or you'll get an explanation for why it's not doable.

I think there are feature requests for all of this already. I just want to know what’s the recommended workaround for now. Perhaps @marcus or @divyendu have some advice?

Possible! Their experience is a little higher than mine 😛

I think the proposal for the number of posts by @Jenkins looks good. And that should be 1 request in my opinion. Right now i don’t see why this should be N requests. @Fran Dios: Then you mention pagination and sorting by this field. I am not sure i understand you correctly. Do you basically want to sort the users by their number of posts? Yes this is a feature we are lacking at the moment. In this case you probably fallback to the raw access API.

@Jenkins Thanks for your help! @marcus If I understood correctly how it works, the main resolver “getUsers” would do

return db.query.users()

. Then, I will have a “computed”

User.totalPosts

field only in my API, not in my DB, and add a resolver for this field which basically does

return db.query.postsConnection({ author: { id: "xxx" }, '{ aggregate { count } }')

. Doesn’t this create 1 request for

query.users

and many requests for

query.postsConnection

(one per user)? Or is it batched somehow?

Regarding the first one: Ah yeah that would indeed be the case. I missed that part. I would not add this field to your application

User

type. I would add a new toplevel query field to your schema

totalPosts

which allows you to retrieve those counts for multiple users at once.

totalPosts

which allows you to retrieve those counts for multiple users at once.

How would be the query to Prisma in that case? I thought we would need a “group by user id” in order to make this in 1 request?