QUERY BY EXAMPLE - Add max rows to be returned

Questions and answers regarding general SQL and backend databases

QUERY BY EXAMPLE - Add max rows to be returned

Postby dlclark » Fri Feb 25, 2022 9:15 pm

Wondering how to add LIMIT = max rows to query

/**
* param {Numbber} maxReturnedRows
* @properties={typeid:24,uuid:"8F6D517D-15BE-45CA-B84C-34F4C8060305"}
*/
function createSubscribersFullList(maxReturnedRows) {

var query = datasources.db.gob_business_system.subscribers.createSelect();

query.result.addPk(); // return only the primary key

// TODO: NEED TO ADD MAX RETURNED ROWS TO QUERY
// add where clause
query.where.add(query.columns.electronic_issues_remaining .gt(0));


plugins.dialogs.showInfoDialog("Subscribers Full List for Export", 'Records found: ' + foundset.getSize() );

}
dlclark
 
Posts: 38
Joined: Sat Jan 08, 2022 5:39 pm

Re: QUERY BY EXAMPLE - Add max rows to be returned

Postby rgansevles » Sat Feb 26, 2022 2:21 pm

The query builder does not have a limit, limits are used when the query is used, it is not a thing of the query itself.

The most natural way is to just not traverse the foundset beyond the maxReturnedRows.
Since a foundset is loading in a lazy way, the other records and pks are never read from the database.

If this is not feasible, you can read all pks (with limit) in a dataset and load the foundset with those.
Note that the pks are loaded twice from the database in that case.

Code: Select all
   var dataset = databaseManager.getDataSetByQuery(query, maxReturnedRows)
   foundset.loadRecords(dataset)


Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 5 guests