Servoy Forum

Hi -

I've been using the new QueryBuilder in Servoy with great success- I'm using it with the Velocity webclient to drive a RESTful API - generally I'll decode a querystring from a request and loop through it to build a Query.

The only problem I've not been able to sort out yet is abstracting the standard pagination functionality - something like /employees?limit=50&offset=50 or /employees?page=1

The current functional yet ugly solution is to build the query, load the foundset, set the specified index, loop through the foundset for limit number of times and copy the record into an array for return.

I don't see any public references but I assume standard foundset lazy-loading is doing something similar in the backend, so perhaps it could be exposed?

Any suggestions? It would be most excellent to be able to do something like:

Code: Select all

_query.add.limit(50);
_query.add.offset(50);


Created a case:
https://support.servoy.com/browse/SVY-4092

I think it's a good idea. Limit/offset is indeed crucial for pagination, and is missing in the Query Builder right now.
I've voted for it, anyone agreeing should do the same.

Hi,

This may be a bit more tricky then it seems.

First, Servoy translates the QueryBuilder object to sql using Hibernate and not all Hibernate dialects support limit and offset the same way, or support it even at all.
Probably because not all databases support this.

Second, we use the limit and offset internally to move to the next foundset pk chunk.
A foundset.loadRecords(qb) with a qb that contains limit/offset would interfere with that.

A possibility would be to exten a databaseManager.createDatasetByQuery(qb) with an option for start and end record, like we now have just the nr of records.

Rob

In my use cases, I don't want datasets, but instead a foundset. My intended use is actually with the JSFoundSetUpdater. Customer is migrating from an existing system which has the ability to fill the next x set of records. So say you are on row 10 of 1M, and user can say fill the next 500 rows, so rows 10 - 510 in the current foundset with a value. Currently, Servoy doesn't provide a fast way to do this without looping over the foundset.

I'd like to use the JSFoundSetUpdater to load a foundset, which could loaded from the QueryBuilder. However, in order for that to work, I'd need the QueryBuilder API to support limit and offset. Alternatively, if the databaseManager.getFoundSetUpdater(foundset) supported additional limit and offset parameters, that would also do what I need.

Its difficult to tell customers converting from things like FoxPro over to Servoy, that Servoy doesn't have a fast way to do what their legacy system can do.