It's Prisma Time

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,
  },
});

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;
    };
}

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,
      },
    },
  },
});

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

It's Prisma Time - Aggregate and GroupBy

Comments

It's Prisma Time - Transactions

More from this blog

Automating my Codemotion Badge with Antigravity

Take control of your job with GitLens Launchpad

The Mood Mirror App

Improve Your Git Commit Messages with GitLens Tips

Do you really need the Cloud to work with AI?

Command Palette

Comments

It's Prisma Time

It's Prisma Time - Transactions

More from this blog