Preview feature feedback: Prisma count by filtered relation (filteredRelationCount)

[matthewmueller]

Please share your feedback about the filteredRelationCount functionality released in v4.3.0 in this issue.

• If you encounter a bug, please open a bug report in this repo.
• If the feature is working well for you, please share this in a comment below or leave a 👍 on this issue.

If you have any questions, don't hesitate to ask them in the #prisma-client channel in the Prisma Slack.

[chason]

This works great except that without the ability to have multiple _count annotations for the same relation¹ the use cases are limited

Footnotes

1. Captured in this feature request https://github.com/prisma/prisma/issues/15423 ↩

[sneko]

@matthewmueller did you think about allowing multiple counts for the same relation? I agree with @chason it's limited otherwise.

I guess it would make the typing a bit more complicated since multiple counts probably imply multiple names?

[ssukienn]

Good feature, great job!

For cases where I am interested in _count as an extra additional column, the select API placement makes it less ergonomic as I need to manually specify all of the remaining columns interesting for me from the model (and nested models). This is probably more related to the select semantics but count here is in the scope of the findMany operation, so opting out from all columns when specifying an "extra" _count creates a struggle (think about a table with 20 cols which you need to retype in select block).

[SC0d3r]

Is there a way that this _count returns the total number of users not just its relations? I need it for pagination, currently I have to send 2 requests: prisma.user.findMany({,,.}) to get the users, prisma.user.count({...}) to get the total number of users

await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: { where: { title: 'Hello!' } },
      },
    },
  },
})

[bezenson]

There are some differences in not.

In common not:

where: {
  something: {
    not: null
  }
}

But with filteredRelationCount isNot:

where: {
  something: {
    isNot: null
  }
}

[Weakky]

Hey @inferusvv,

Would you mind sharing a Prisma Schema that reproduces your issue? I'm mostly interested in the type of something. Thank you 🙏🏻

[bezenson]

@Weakky Sorry, I am not using this flag for now, but just implement basic count with this feature enabled. There is just no consistency in naming.

[Weakky]

I'm afraid I'm unable to reproduce your issue.

prisma.user.findMany({
  where: { address: { isNot: { user: { isNot: { id: 1 } } } } },
  includes: {
    _count: { where: { user: { isNot: { address: { isNot: { id: 1 } } } } } }
  }
})

We're aware the naming is inconsistent between scalar and relations, but that has no relevance with filteredRelationCount.

[bezenson]

Got you. This is weird. Here is my model:

model PointOfSale {
  id                       String   @id @default(auto()) @map("_id") @db.ObjectId

  menu           Menu?         @relation(fields: [menuId], references: [id])
  menuId         String?       @db.ObjectId
}

And prisma request:

      this.prismaService.pointOfSale.findMany({
        where: {
          menuId: {
            not: null,
          },
        },
      });

There is no isNot, only not

[Weakky]

Thanks for the follow-up! That confirms my thoughts. As mentioned above, scalar fields and relations indeed have a different not, but that inconsistency should be equally present when filteredRelationCount is not enabled.

To-one relations use isNot to stay consistent with the is. I'm honestly not entirely sure why we introduced is for to-one relations in the first place.

To conclude, this is a completely different topic, which won't be treated in the scope of making this feature GA.

[joshuaavalon]

Will there be a way to order by _count?

For example, I want to sort the post by the number of matching tags.

db.post.findMany({
  include: {
    _count: {
      select: {
        tags: {
          where: { name: { in: tags } }
        }
      }
    },
    tags: true
  },
  orderBy: [
    { tags: { _count: "desc" } } // <- this only sort by all tag count
  ]
})

[Weakky]

Hey folks,

For cases where I am interested in _count as an extra additional column, the select API placement makes it less ergonomic as I need to manually specify all of the remaining columns interesting for me from the model

@ssukienn Have you tried using _include: { _count: { ... } } instead? That would allow you to additively add _count to your selection set without having to re-specify every single field you want to select.

Is there a way that this _count returns the total number of users not just its relations? I need it for pagination, currently I have to send 2 requests: prisma.user.findMany({,,.}) to get the users, prisma.user.count({...}) to get the total number of users

@SC0d3r Good piece of feedback. We understand the pain point so I've created an issue for you to track here #19779

Will there be a way to order by _count?
For example, I want to sort the post by the number of matching tags.

@joshuaavalon This is an unfortunate limitation the Prisma Client has atm. The root problem is that you have no way of referring to an existing join (expressed by the where clause). While your use case is totally valid, the way it currently behaves is also valid and we should have a mechanism that enables you to choose whether you want to order by a subset of the data or on all the data. While this is something we have on our sight, we haven't started thinking about how we'll allow that just yet. There is an existing issue that I believe expresses your concern here #14598

[janpio]

Thanks everyone, filteredRedlationCount is Generally Available since 4.16.0 🥳
If you have further feedback, you can open a new issue now. Thanks again!