DEV Community

Cover image for It's Prisma Time - Aggregate and GroupBy
Luca Del Puppo for This is Learning

Posted on

It's Prisma Time - Aggregate and GroupBy

Welcome back Folks πŸ‘‹
Today we are seeing how to aggregate and group data.

Let's start from the first one.
To aggregate data in Prisma you have to use the aggregate method.
Using this method, you can aggregate data of type number, and you can do these operations:

  • max
  • min
  • count
  • sum
  • avg

N.B. I won't spend time explaining what these operations do because I think their names speak for themselves

Therefore, let's give an example to see the aggregate method at work.

const aggregate = await prisma.author.aggregate({
  _avg: {
    age: true,
  },
  _max: {
    age: true,
  },
  _min: {
    age: true,
  },
});
Enter fullscreen mode Exit fullscreen mode

As you can see, in this example, we aggregated the authors and we got as result the authors' ages average, the age of the older author and the age of the younger author. The result is composed in this way:

{
    _avg: {
        age: true;
    };
    _max: {
        age: true;
    };
    _min: {
        age: true;
    };
}
Enter fullscreen mode Exit fullscreen mode

In addition, the aggregate method allows us to use these operations:

  • where
  • orderBy
  • take
  • skip
  • cursor

So as you can immagine, you can filter and sort your data before aggregate them.
I think this feature might be clear that way, so let's move on to the groupBy feature.

The groupBy feature allows us to group data by one o more fields.
Let’s see an example to start

const commentsGroupByPost = await prisma.comment.groupBy({
  by: ["postId"],
  _count: {
    authorId: true,
    _all: true,
  },
  orderBy: {
    _count: {
      authorId: "desc",
    },
  },
  having: {
    authorId: {
      _count: {
        gt: 1,
      },
    },
  },
});
Enter fullscreen mode Exit fullscreen mode

In this example, we grouped the comments by post, and after that, we counted the number of comments with the author and the number of comments for that post.
Moreover, we sorted the result by the authors' count and, using the having option, we took only the posts that have at least a comment with an author.
As you can see, this method is not difficult to use and understand. It's also possibile to filter the data with the where option.

I think for today that's all.
We saw how to aggregate and how to group data using Prisma and, if you want to deepen, the prisma team created this video to explain better the groupBy feature.

In the next article we will see how to run our queries under transaction, but for today it's time to close πŸ˜ƒ

See you soon guys
Bye bye πŸ‘‹

To show the code of this article in action follow this link

Discussion (0)