cube

cube copied to clipboard

Is your feature request related to a problem? Please describe. When calling the /load endpoint with a query that has a limit parameter and is grouped (default behavior), it is sometimes necessary to know the total number of rows that would be returned by the query without a limit. Fetching all the rows and counting them is not always a viable option when the query is expensive and returns a very large number of rows.

Describe the solution you'd like The total number of rows to be returned as part of the /load endpoint's response when a limit parameter is specified in the query. (This could be an opt-in behavior, i.e. the total number of rows is only returned if the user specifically requests it in the query).

Describe alternatives you've considered

• Sending a different query which only counts the number of rows and returns the count. This would be an ok alternative but doesn't seem possible at the moment.
• Fetching all the rows and counting them. This isn't feasible because of the large number of rows and how expensive the query is to run.
• Operating without knowing the total number of rows. This seems to be working fine at the moment but may limit the optimizations that can be done on the front-end.

This would be very helpful for our use case I've described in https://github.com/cube-js/cube.js/issues/251#issuecomment-781601834

Just adding to this, One way of tackling this would be to add a secondary "count" query which will allow you to customise the query for the rows returned. This would be important when wanting to apply all the filters and other options you've selected in the query but not having a limit on the returned rows.

Make sense?

I've got a couple of use cases where this would be really helpful:

• When paginating through tabular report data, it'd be great to show users that we are Showing x-y of n
• When first loading a particular report, some of our users would like to see the total count of rows as part of the report title/header
• Building pagination for tabular report data. In the current state, it's extremely challenging to build pagination like this (captured from GitHub's own issues listing page): To achieve this now requires making additional queries, which can become expensive.

Happy to provide more details as needed on the above use-cases.

Would you guys be willing to accept and merge a PR that partially implements this (i.e. for just one database backend)?

I can probably arrange some time for myself or another resource to look at implementing it for Postgres (which is what we'll be using at work), but I don't think I've got time or expertise to attack this across the board for every backend.

@rdwoodring what backend have you implemented?

I will review and possible extend so we can make this for all available backends.

Thanks, Karl.

Well, I haven't done anything yet since I hadn't heard anything back. I'm using postgres, so I'd be implementing that, were I to contribute here.

We’re also using Postgres but I could manage a few more RDBMs

Has anyone actually mapped out the implementation, or should we discuss a design?

Thanks, Karl.

On Wed, 15 Sep 2021 at 20:53, Ryan Woodring @.***> wrote:

Well, I haven't done anything yet since I hadn't heard anything back. I'm using postgres, so I'd be implementing that, were I to contribute here.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/cube-js/cube.js/issues/1927#issuecomment-919913190, or unsubscribe https://github.com/notifications/unsubscribe-auth/AATM3Z77XYTZWPBDY6BKDQDUCB3JZANCNFSM4WXTZDBA . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

Just wanted to second this. True pagination is not possible without this. The documentation's example only works if the dimension variable (Order Number) is unique to each order. If you add a different dimension, like Order City, then the count measure will be wrong.

For instance, suppose you have a schema like this:

cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  measures: {
    count: {
      type: `count`,
    },
  },

  dimensions: {
    number: {
      sql: `number`,
      type: `number`,
    },
    
    city: {
      sql: `city`,
      type: `string`,
      title: `City`,
      description: `The order's city, town, or village.`,
    },

 },
});

And a query like this:

{
    measures: ["Orders.count"],
    order: {
      "Orders.count": "desc",
    },
    dimensions: ["Orders.city"],
    limit: 10
 }

Then there's no way to get the length of the response without just getting the full response. The query in the documentation won't work because it's not grouped by city.

We're using Postgres so @rdwoodring we'd love to see your solution. It's pretty critical for us.

@kmclaugh did you find a solution? We are running into this exact same problem. We are using multiple different cubes to aggregate and group the data so there is not one cube where we can add a measure to get the count properly

@paveltiunov Is there any chance for the issue gets included in the 2022 Roadmap?

It's already shipped. We need to document this.

@paveltiunov sweet! if you have a quick preview of how it works that would be most helpful.

@paveltiunov If you can provide me the commit's for this - I will happily write the documentation.

@inventionlabsSydney, you can look at tests from here to get how it works.

cc @hassankhan

nice job! thank you. I've tested it with /load and works like a charm.

But I can't figure it out how to make it work with query-builder (vue, latest version). I receive null (same value w/ and w/o total: true). Am I missing something or should I wait for an update?

I think this is related to #4987 total: true does not work when cube has pre-agg's enabled. cc @hassankhan

Closing as resolved

@paveltiunov Where is the doc for this? I can see that in API reference. But looks like not available in the GraphQL query, will this support be added to GraphQL soon?