Prisma - Sort by _sum

Question

I do have simple groupBy query in my prisma which looks like this:

const groupBy = await prisma.referral.groupBy({
  by: ['recommenderId'],
  _sum: {
     points: true,
  },
});

What I am looking for is the way to sort by this _sum value.

The current response is:

{
    "groupBy": [
        {
            "_sum": {
                "points": 20000
            },
            "recommenderId": 3
        },
        {
            "_sum": {
                "points": 19000
            },
            "recommenderId": 2
        },
        {
            "_sum": {
                "points": 34000
            },
            "recommenderId": 1
        }
    ]
}

What I need is to get is:

{
    "groupBy": [
        {
            "_sum": {
                "points": 34000
            },
            "recommenderId": 1
        },
        {
            "_sum": {
                "points": 20000
            },
            "recommenderId": 3
        },
        {
            "_sum": {
                "points": 19000
            },
            "recommenderId": 2
        },
    ]
}

Based on documentation (https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting#sorting) I tried something like this:

const groupBy = await prisma.referral.groupBy({
      by: ['recommenderId'],
      _sum: {
        points: true,
      },
      orderBy: [
        {
          _sum: 'desc',
        },
      ],
    });

But with code I'm getting error:

Argument _sum: Got invalid value 'desc' on prisma.groupByReferral. Provided String, expected ReferralSumOrderByAggregateInput

Answer 1

You can use _sum on different fields at the same time, so you also need to provide field name that you want to sort on:

const groupBy = await prisma.referral.groupBy({
      by: ['recommenderId'],
      _sum: {
        points: true,
      },
      orderBy: [
        {
          _sum: {
            // Add `points` key here
            points: 'desc'
          }
        },
      ],
    });