6

I am using LIMIT and OFFSET in InfluxQL queries to handle pagination.

For example, in a measurement with 3 rows.

> SELECT *::field FROM i2QYtZBVSnuXjLhQhuAV6w;
name: i2QYtZBVSnuXjLhQhuAV6w
time                 hello
----                 -----
2018-02-23T18:00:00Z 1000
2018-02-23T18:30:00Z 1200
2018-02-23T19:00:00Z 990

Supposing I read the rows one by one using LIMIT and OFFSET:

> SELECT *::field FROM i2QYtZBVSnuXjLhQhuAV6w LIMIT 1;
name: i2QYtZBVSnuXjLhQhuAV6w
time                 hello
----                 -----
2018-02-23T18:00:00Z 1000
> SELECT *::field FROM i2QYtZBVSnuXjLhQhuAV6w LIMIT 1 OFFSET 1;
name: i2QYtZBVSnuXjLhQhuAV6w
time                 hello
----                 -----
2018-02-23T18:30:00Z 1200
> SELECT *::field FROM i2QYtZBVSnuXjLhQhuAV6w LIMIT 1 OFFSET 2;
name: i2QYtZBVSnuXjLhQhuAV6w
time                 hello
----                 -----
2018-02-23T19:00:00Z 990

Is there a way to know that there is no more data after that without performing an additional query?

EDIT: My use case is generating a "next page token" for a user-facing REST API. I'd like to avoid giving the user a token that will just return an empty row set.

LodeRunner
  • 7,975
  • 3
  • 22
  • 24
  • 1
    You can count in advance and stop when you reach the count. "SELECT COUNT(column_name) FROM series_name group by time(10m)". Obviously, more data might enter the DB since the first count - but if you want realtime streaming that's a different case, you would need Kapacitor for that. Another option, depending on your implementation, is to catch an empty result and stop your pagination. – Tom Feb 26 '18 at 12:41
  • Thanks for the hints. I thought of the first method, but it's still one extra request. I'll go down that path if I can't find another way. The second method has one broken case: when the row set is an exact multiple of the page. Then there's no way to know. – LodeRunner Feb 26 '18 at 12:45

1 Answers1

5

To avoid the additional query, you can always make the DB query using limit as (limit + 1).

Ex : Set limit as 6, if the actual limit is 5 and give the next page token only if the result set has 6 rows. Also, return the data only for 5 rows to the client.

Deva Gerald
  • 284
  • 2
  • 6