6.1 QueryBuilder - use LIMIT and OFFSET?

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

6.1 QueryBuilder - use LIMIT and OFFSET?

Postby robwormald » Wed Mar 06, 2013 11:15 pm

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);
robwormald
 
Posts: 20
Joined: Sun Feb 13, 2011 7:55 am

Re: 6.1 QueryBuilder - use LIMIT and OFFSET?

Postby robwormald » Wed Mar 06, 2013 11:45 pm

robwormald
 
Posts: 20
Joined: Sun Feb 13, 2011 7:55 am

Re: 6.1 QueryBuilder - use LIMIT and OFFSET?

Postby ptalbot » Thu Mar 07, 2013 12:35 am

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.
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
https://www.servoyforge.net
Velocity rules! If you don't use it, you don't know what you're missing!
User avatar
ptalbot
 
Posts: 1654
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: 6.1 QueryBuilder - use LIMIT and OFFSET?

Postby rgansevles » Sat Mar 09, 2013 3:19 pm

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: 6.1 QueryBuilder - use LIMIT and OFFSET?

Postby sbutler » Wed Apr 04, 2018 10:17 pm

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.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 10 guests