Prisma-client-js: Aggregations

[schickling]

type DynamicResult2 = (User & { aggregate: { age: { avg: number } } })[]
const dynamicResult2: DynamicResult2 = await prisma.users({
  select: { aggregate: { age: { avg: true } } },
})

type DynamicResult3 = User & {
  posts: (Post & { aggregate: { count: number } })[]
}
const dynamicResult3: DynamicResult3 = await prisma.users.findOne({
  where: 'bobs-id',
  select: { posts: { select: { aggregate: { count: true } } } },
})

const deletedCount: number = await prisma.users.deleteMany()

[marcocastignoli]

Is this going to be added?
I'm trying to add a Like table (that's the first time I use prisma in my life). I succesfully created it using

model Post {
  ...
  likes Like[]
}
model Like {
  id    String  @default(cuid()) @id @unique
  user User
  post Post
}

And I can see the list of likes in the feed with

app.get('/feed', async (req, res) => {
  const posts = await photon.posts.findMany({ where: { published: true }, select: {
    title: true,
    likes: true // aggregation function needed!
  } })
  res.json(posts)
})

But there is no function to count them instead of selecting all of them.

[nickreynke]

@marcocastignoli Aggregations are speced out. See here https://github.com/prisma/prisma2/issues/234#issuecomment-515116563 and here https://github.com/prisma/specs/tree/master/photon-aggregration-api

So I assume they will be added till the final release of Prisma2 (which may be this fall I heared)

[nhuesmann]

Since this issue has been latent for a little while, I wanted to check the status of this feature—how's progress? I read the specs here and was really excited about what I saw, _especially_ ordering by aggregated values as seen in the example in the spec readme:

const nestedResult1 = await prisma.posts({
    first: 10,
    // See #1
    orderBy: (post: PostExpression) => post.comments.likes.sum()
    select: {
        posts: { },
        friends: true
    }
})

I need aggregations for a few features I'm trying to write and am super excited to hear how this is going :)

[schickling]

@nhuesmann it'll still take a little while until these features are fully available but we're working towards it. Given the current state, I'd say still a few months out.

[nhuesmann]

@schickling awesome, thanks for your reply. I understand it's one among many features under work, I'm happy to wait and look forward to updates on this down the line!

[nhuesmann]

Hey @schickling, it's been a few months since the last activity on this issue. I just wanted to follow up to see if you have any updated progress/idea about this feature. Thanks!

[nhuesmann]

Just wanted to ping this issue again to see if we could get any sort of status update, it's been 4 months since the last comment by someone from Prisma. Also please know I'm not trying to sound impatient/pressuring, I simply want to keep the conversation about this feature alive :)

[chmac]

Looks like .count() is the only aggregation that's been implemented so far. I had a hard time tracking down where I could see this in code, I never found it. But that's what the docs seem to say here: https://github.com/prisma/prisma2/blob/master/docs/prisma-client-js/api.md#aggregations

I'd guess at this point that sum, average, etc are not yet implemented.

[nhuesmann]

@chmac thanks for the heads-up, I hadn't caught that addition! This will certainly help a few use cases I have. Looking forward to news from Prisma team eventually too.

[timsuchanek]

It's correct, that the simple count() without parameters is already implemented.
You can e.g. do prisma.user.count().
The next feature after that will probably be parameterized count (providing a where filter).

We can't promise the timeline yet, but this is definitely something we gonna tackle.

[Sytten]

I think sum should be implemented after the parameterized count, it is pretty common.

[ryancwalsh]

My sense is that if I want to write a "group by" query so that I can count how many results there are per value of a certain column, this isn't supported yet.

Is _this issue_ the best one for me to subscribe to so that I know when this functionality is supported?

Any idea when that might be? Thanks!

P.S. I know that "count" exists from this comment, but that seems not to achieve my goal (unless I loop over many queries).

[pantharshit00]

@ryancwalsh You can subscribe to https://github.com/prisma/prisma-client-js/issues/1

[pantharshit00]

Some basic aggregation support got merged today: https://github.com/prisma/prisma/pull/2937

Subscribe to the releases of https://github.com/prisma/prisma for updates on this :D

[thebiglabasky]

Since we released a first level of support for aggregations (https://github.com/prisma/prisma/issues/2838) I will close this issue.
I would suggest opening a new one if the current solution doesn't cater for some other specific needs, in which case we'd love to know more about them.
Thanks!