Prisma-client-js: Parameterized `.count()` queries

[madebysid]

Essentially, this comment: https://github.com/prisma/photonjs/issues/202#issuecomment-529818807
Also note that after and skip params should also be accounted for.

This issue is relevant for https://github.com/prisma/studio/issues/119

Creating this issue for documentation, feel free to move wherever.

[madebysid]

These are things Studio needs w.r.t. the new designs:

1. It needs to be able to account for after & skip parameters, since these two params will directly affect the total number of records that are fetched. Studio allows you to manually specify these values for saved / detached queries in the databrowser. Since .count() does not take any parameters yet, the count on the databrowser might be wrong if the user changes them. Accounting for skip is simple enough by subtraction, but after might not always be possible.

2. It needs to be able to count the number of relations. The idea is to use these counts to populate the relation counts in the results table rather than include the full relation as a field to be returned (as it is done right now).

[divyenduz]

Internal note: dropping from this sprint because Jan did not want to read this long message during the meeting.

[janpio]

Internal Note: (Meeting was over time already - we will look into this again next sprint as it will "keep" the candidate label.)

[timsuchanek]

Internal Note: Dropping this for now in favor of other query engine work like query batching, needed for dataloader.

[ctrlplusb]

Dropping this for now in favor of other query engine work like query batching, needed for dataloader.

That sounds really exciting.

[steebchen]

Just wanted to note in general that this is not required for pagination and is not necessarily related to that. It can be used in addition to pagination, i.e. showing the total count of a table, but usually is not used for e.g. relay pagination (since it's not performant on huge tables anyway).

[janpio]

Internal Note: Dropping from current sprint, and picking up later.

[pierreyves-lebrun]

Just wanted to note in general that this is not required to pagination and is not necessarily related to that. It can be used in addition to pagination, i.e. showing the total count of a table, but usually is not used for e.g. relay pagination (since it's not performant on huge tables anyway).

I get what you are trying to say but a lot of datatable libraries do require the total number of items in order to handle pagination properly.

Some features simply can't function without it:

• displaying the number of pages
• letting the user go the last page (doesn't concern all libraries though)
• adapting the number of items to show per page (if there are only 10 results, there is no point to let the user select 50 items per page)

[steebchen]

@pierreyves-lebrun Agreed! I'm just saying for the very core feature of pagination this is not required, as this assumption has popped up a few times.

Of course, if the user wants advanced pagination, we also want to build this feature.
However, that should be up to the user, as often count queries can easily get slow with big tables, while the core of pagination will always be fast regardless of the table size.

[justicejoe]

is this feature gonna be add in next nexus-prisma update ?

[maartenraes]

FYI; someone in the Slack mentioned this and we've built a workaround for this which I posted there, I'll post it here for some other people that might not be in the prisma-2 Slack :)

We've implemented a query that returns the count of items with a provide where clause; this way we can get the count of a filtered list
In the frontend it looks like this

 query fetchDepartmentList($where: DepartmentWhereInput!, $skip: Int, $first: Int) {
    departments(
      where: $where, 
      skip: $skip, 
      first: $first
    ) {
      id
      name
      address {
        street
        number
        bus
        zip
        city
      }
      email
    }
    departmentCount(where: $where)
  }
`;

Not the most efficiënt way to do things, but it's a temporary solution and only a single query extra while fetching the data :slightly_smiling_face:

[dpetrick]

The engine now supports regular filtering / pagination on count queries.

[guerrerocarlos]

@dpetrick great, is there any documentation around this?

[janpio]

This is implemented in the Query Engine, but not the client that exposes this functionality yet. You will have to wait a bit for that - this issue will be closed then.

[timsuchanek]

Thanks a lot for reporting 🙏
This issue is fixed in the latest alpha version of @prisma/cli.
You can try it out with npm i -g @prisma/cli@alpha.

In case it’s not fixed for you - please let us know and we’ll reopen this issue!

[nnennajohn]

@timsuchanek, is there a doc on this? Thanks.

[divyenduz]

Thank you, here is a link to the docs: https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-client/crud#count

You can use it like following:

prisma.user.count({ where: { name: 'Bob' }})

count has exactly the same params as findMany.

[nnennajohn]

Awesome. Thanks @divyenduz