Ability to filter count in "Count Relation Feature"

[sandbox-apps]

Problem

Currently count relation feature just accepts true or false values and count all the related records.
Sometimes we just need to count some of the records depends on the query.

For example

this.prisma.groupExamAttempt.findMany({
  where: {
    groupId: param.groupId,
    examId: param.examId,
  },
  include: {
    _count: {
      select: {
        answers: true,
      },
    },
  },
});

This will just passively count all the records there.

Suggested solution

To filter maybe we can add a where clause

this.prisma.groupExamAttempt.findMany({
  where: {
    groupId: param.groupId,
    examId: param.examId,
  },
  include: {
    _count: {
      select: {
        answers: {
          where: {
            isCorrect: true,
          },
        },
      },
    },
  },
});

Maybe if applicable, It can also be based on the deeper relationship filter. (IF ONLY THIS IS DOABLE)

[sandbox-apps]

Another use case:
Instead of

return this.prisma.group.create({
      data: {
        name: dto.name,
        shortname: dto.shortname,
        description: dto.description,
      },
      include: {
        _count: {
          select: {
            groupContents: true,
            groupMembers: true,
          },
        },
      },
    });

We can do

return this.prisma.group.create({
      data: {
        name: dto.name,
        shortname: dto.shortname,
        description: dto.description,
      },
      include: {
        _count: {
          select: {
            groupContents: {
              where: {
                createdAt: "within 28 days here...",
              },
            },
            groupMembers: {
              where: {
                createdAt: "within 28 days here...",
              },
            },
          },
        },
      },
    });

So we can have a flexible counts

[sandbox-apps]

Another example would be:

return this.prisma.product.findMany({
  data: {
    // ...
  },
  include: {
    _count: {
      as: "stats",
      select: {
        ratings: [
          {
            as: "fiveStars",
            where: { rateValue: 5 },
          },
          {
            as: "fourStars",
            where: { rateValue: 4 },
          },
          {
            as: "threeStars",
            where: { rateValue: 3 },
          },
          {
            as: "twoStars",
            where: { rateValue: 2 },
          },
          {
            as: "oneStars",
            where: { rateValue: 1 },
          },
        ],
      },
    },
  },
});

The json output would be

{
  "stats": {
    "fiveStars": 11, // ...count of 5 stars here,
    "fourStars": 4, // ...count of 4 stars here,
    "threeStars": 5, // ...count of 3 stars here,
    "twoStars": 1, // ...count of 2 stars here,
    "oneStars": 0, // ...count of 1 stars here,
  }
}

[omar-dulaimi]

We currently have a need for this feature. Would be awesome to have it soon.

[sandbox-apps]

I will be happy if this will be covered in #8631

[janpio]

It will most probably not @sandbox-apps, as this is an independent feature request that is not required to make relation counts usable and valuable by itself.

[matthewmueller]

Thanks for the feedback @sandbox-apps! For now you'd need to filter them further in your application (or use $queryRaw), but I can see the value of offering this within the Prisma Client.

By the way, I've seen your name pop up quite a bit in our issues recently. Would you be up for a 30 minute call? https://calendly.com/matthewmueller/prisma. I'd love to learn more about what you're building.

[dickfickling]

Running into a need for this too. Would love to be able to do something like e.g. find users with >50 followers

prisma.user.findMany({ where: { followers: { _count: { gt: 50 } } } });

[dariusj18]

This is also pretty essential for soft deletes. Ex. no reason for deleted records to be included in the count.

[gtolarc]

Running into a need for this too. Would love to be able to do something like e.g. find users with >50 followers

prisma.user.findMany({ where: { followers: { _count: { gt: 50 } } } });

#8935

[adherb]

Hey guys,

I would also love this feature to be added. I am building a workout app and would love to have the ability on nested relations to return a count for specific fields.

For example, I would like to get a count for all userWorkouts where the boolean field 'isCompleted' equals true.

On the front end I would like to show the percentage of a program that has been completed, so ideally I would like to return the count for all workouts within a program (which you can currently do) and the userWorkouts where 'isCompleted:true'.

For now of course I can calculate on the frontend, but it would be much better if I could use Prisma to achieve this.

Please see my prisma query below:

const response = await prisma.user.findUnique({
  where: {
    id: 1,
  },
  select: {
    id: true,
    programs: {
      select: {
        program: {
          select: {
            name: true,
            blocks: {
              select: {
                id: true,
                name: true,
                week: {
                  select: {
                    id: true,
                    number: true,
                    workouts: {
                      select: {
                        userWorkouts: {
                          where: {
                            isCompleted: true,
                          },
                        },
                        _count: {
                          select: {
                            userWorkouts: true,
                          },
                        },
                      },
                    },
                    _count: {
                      select: {
                        workouts: true,
                      },
                    },
                  },
                },
              },
            },
          },
        },
      },
    },
  },
});
res.json(response);
};

[wodCZ]

I'm currently struggling with this. My use case: for each category of the e-shop, return the number of published (available) products.

Haven't found a better way than these two so far:

  async getProductCount1(id: string) {
    // stable no. of queries, but one huge query (IN (1..4900)), count done at JS level with redundant data
    const counts = await this.prismaService.category.findUnique({
      where: { id },
      include: {
        products: {
          where: { status: { equals: ProductStatus.PUBLISHED } },
          select: { id: true },
        },
        secondaryProducts: {
          where: { status: { equals: ProductStatus.PUBLISHED } },
          select: { id: true },
        },
      },
    });

    if (!counts) {
      return 0;
    }

    return counts.products.length + counts.secondaryProducts.length;
  }

  async getProductCount2(id: string) {
    // N+1 queries, count done at DB level
    const counts = await this.prismaService.product.count({
      where: {
        status: ProductStatus.PUBLISHED,
        OR: [
          { categoryId: { equals: id } },
          { secondaryCategories: { some: { id } } },
        ],
      },
    });

    if (!counts) {
      return 0;
    }

    return counts;
  }

None if them is scalable enough for large datasets.

[ZhassulanKuan]

Please do this, its impossible to implement good soft delete because of this

[ghost]

Any update on this? This would be a really useful feature.

[rwniddery]

I'm just trying Prisma first time today and of course immediately tried to recreate a query I'm currently doing using Sequelize. With Sequelize and I can query a model and include any number of raw subqueries in addition to all the higher level filtering available. I've have no problems with Sequelize but saw your product and it seems lighter and more to my liking if I can meet my needs with it.

In this example, I can get complete counts just fine with:

  let projects = await prisma.projects.findMany({
    include: {
      _count: {
        select: {
          Orders: true,
          Shipments: true
        },
      }
    },
  })

However, in the case of Shipments, I need to filter count with conditions and cannot currently do it. Quite honestly this seems like a pretty basic need. Is there any other way to include a subquery without resorting to making the entire query raw?

[steven-tey]

I'm currently trying to implement soft deletes and this is a pretty big blocker for it. I decided to go with a workaround where I use onDelete SetNull for the related records and when the root records gets deleted and when it gets added back, we connect the related records again using InteractiveTransactions.

It's far from ideal but it gets the job done (for now). Would be great to have this feature built into Prisma though!

[matthewmueller]

@steven-tey do you mind sharing how you're implementing soft deletes?

It seems like a possible workaround until we get this implemented would be to move the propose where clause in the count to the top-level where and performing a count on the results.

Using the example above (untested):

this.prisma.groupExamAttempt.findMany({
  where: {
    groupId: param.groupId,
    examId: param.examId,
  },
  include: {
    _count: {
      select: {
        answers: {
          where: {
            isCorrect: true,
          },
        },
      },
    },
  },
});

To:

const result = this.prisma.groupExamAttempt.findMany({
  where: {
    groupId: param.groupId,
    examId: param.examId,
    isCorrect: true,
  }
});

console.log(result.length)

Would that work?

[EugenVolosciuc]

I am also in need of this feature. I'm implementing a filtering mechanism where I need a filtered by status count to show the available job posts by tag.

For example, 27 in the above case is the total count of job posts with the Blender tag, but I only need to count the Active job posts.

Something like this would help out tremendously:

prisma.tag.findMany({
      take: tagSearchCount,
      where: {
        label: { contains: label, mode: "insensitive" },
      },
      include: {
        _count: {
          select: {
            jobPosts: {
              where: {
                status: {
                  equals: JobPostStatus.ACTIVE
                }
              }
            }
          }
        },
      },
      orderBy: {
        jobPosts: {
          _count: "desc",
        },
      },
    });

[juttameerhamza]

any progress on this feature ?

[solomonhawk]

+1 👍

[agustints]

+1 👍 any progress on this feature?

[dickfickling]

@SevInf @Jolg42 I saw you closed this issue, but I'm struggling to use the filteredRelationCount feature to accomplish what I brought up in my comment above (#8413 (comment)). Should I make a new issue? Or am I doing it wrong

[janpio]

New issue or comment on #15069 @dickfickling should work.

[crypt0legendx]

Author

Another example would be:

return this.prisma.product.findMany({
  data: {
    // ...
  },
  include: {
    _count: {
      as: "stats",
      select: {
        ratings: [
          {
            as: "fiveStars",
            where: { rateValue: 5 },
          },
          {
            as: "fourStars",
            where: { rateValue: 4 },
          },
          {
            as: "threeStars",
            where: { rateValue: 3 },
          },
          {
            as: "twoStars",
            where: { rateValue: 2 },
          },
          {
            as: "oneStars",
            where: { rateValue: 1 },
          },
        ],
      },
    },
  },
});

The json output would be

{
  "stats": {
    "fiveStars": 11, // ...count of 5 stars here,
    "fourStars": 4, // ...count of 4 stars here,
    "threeStars": 5, // ...count of 3 stars here,
    "twoStars": 1, // ...count of 2 stars here,
    "oneStars": 0, // ...count of 1 stars here,
  }
}

To fetch data with this template is possible on prisma version 4.3.0?