Datasource Paging Issue (Revised)

Question

A simple testing setup: department: employee, 1:M and a search form that allows filtering on Emploee FirstName =, lastname =, email contains, age >=, join date <= and related department =.

A search form with widgets bound to parameters of a cloud SQL datasource query script.

A Submit button on the search form which opens up a query results page with a table bound to the cloud SQL query script datasource.

query script

var params = query.parameters;

return getEmployeeRecords_( 
  params.param_FirstName, 
  params.param_LastName, 
  params.param_Email, 
  params.param_StartDate, 
  params.param_Age, 
  params.param_Department
);

and

function getEmployeeRecords_( firstName, lastName, email, startDate, age,     
department) {

 var ds = app.models.Employee.newQuery();

 if ( firstName !== null ) {
    ds.filters.FirstName._equals = firstName;
 }
 if ( lastName !== null ) {
    ds.filters.LastName._equals = lastName;
 }
 if ( email !== null) {
    ds.filters.Email._contains = email;
 }
 if ( startDate !== null) {
    ds.filters.StartDate._greaterThanOrEquals = startDate;
 }
 if ( age !== null) {
    ds.filters.Age._lessThanOrEquals = parseInt(age, 10);
 }
 if ( department !== null) {
    ds.filters.Department.Department._equals = department;
 }

 var records = ds.run();

 // intention is to store this value for future use
 var recs = records.length;

 return records;
}

On the results page for the query script datasource paging is just broken. A query that correctly returns 8 records where the query page size is set to 5 allows me to get the pager to go to page 1000 if I wished, but the datasource always stays on the first page of records. With page size set to e.g., 100 the correct result set is clearly displayed.

In fact everything I do with this sort of query has paging issues. If I insert this code

var ds = app.models.Employee.newQuery();
//ds.filters.FirstName._equals = firstName;
//ds.filters.LastName._equals = lastName;
//ds.filters.Email._contains = '.com';
//ds.filters.StartDate._greaterThanOrEquals = startDate;
ds.filters.Age._lessThanOrEquals = 40;
//ds.filters.Department.Department._equals = department;
ds.sorting.Age._ascending();
var records = ds.run();
return records;

directly into the datasource query script I still have similar paging issues.

If I use a query builder script such as

(
FirstName =? :param_FirstName and
LastName =? :param_LastName and
Email contains? :param_Email and
StartDate >=? :param_Startdate and
Age <=? :param_Age and
Department.Department =? :param_Department
)

and bindings such as

@datasources.Search_Query_Builder.query.parameters.param_FirstName

this works without issue. The same with direct filtering, where we use bindings such as

@datasources.Employee.query.filters.FirstName._equals

Anyone any ideas in terms of what is wrong with this stuff. We need query scripts for more controle, e.g., the ability to retrieve a count of records and where you have to filter for a condition where you restrict data, e.g. a logged in user is related to a client which in turn is related to a property and the property value is restricted according to client.

... Just looking at a real application under development and the use of a query script within the datasource query script editor, no parameters, no binding, just this code:-

var ds = app.models.Incident.newQuery();
ds.filters.Id._greaterThanOrEquals = 200;
ds.filters.Id._lessThanOrEquals = 300;
var records = ds.run();
return records;

and a page size set to 20 and again the paging is up the creek, never moves beyond the first page of records despite the page number incrementing.

Answer 1

I have some suggestions how to address this issue, although it is still unclear what exactly is causing the paging issue and whether or not my suggestions will fix the underlying issue. However, in my own application environment I have several instances where I use a standard SQL model and I apply filters to a datasource from that model and then have a concurrent calculated model (datasource) that returns the total count of records that meet the filters applied to my other datasource. Here we go:

Create a new datasource under your Employee model, leave it on the default 'Query Builder' type, adjust the query page size to your preference, but preferably to something that you know will return more than one page of records when querying the datasource. Uncheck the 'automatically load data' property unless you want to load all records when first going to your page where you set your filters. Do not enter anything in the query builder.

For the calculated datasource that you called Employee_RecordCount add your parameters, FirstName_equals, LastName_equals, Email_contains, StartDate_greaterequals, Age_lessequals, and Departments_equals, if you have not already. In this calculated model you should have a field called RecordCount. In the query script section of this datasource you should put your function as return getEmployeeRecords_(query).

In your server script section where your getEmployeeRecords function is the code should be as follows:

function getEmployeeRecords_(query) {
 var params = query.parameters;
 var ds = app.models.Employee.newQuery();

 if (params.FirstName_equals !== null ) {
    ds.filters.FirstName._equals = params.FirstName_equals;
 }
 if (params.LastName_equals !== null ) {
    ds.filters.LastName._equals = params.LastName_equals;
 }
 if (params.Email_contains !== null) {
    ds.filters.Email._contains = params.Email_contains;
 }
 if (params.StartDate_greaterequals !== null) {
    ds.filters.StartDate._greaterThanOrEquals = params.StartDate_greaterequals;
 }
 if (params.Age_lessequals !== null) {
    ds.filters.Age._lessThanOrEquals = parseInt(params.Age_lessequals, 10);
 }
 if (params.Department_equals !== null) {
    ds.filters.Department.Department._equals = params.Department_equals;
 }
 var records = ds.run();

 // update calculated model with record count
 var calculatedModelRecord = app.models.Employee_RecordCount.newRecord();
 calculatedModelRecord.RecordCount = records.length;

 return [calculatedModelRecord];
}

Now go to your search page, create a new panel or form set it to the same new datasource that you created. Make sure you have all your appropriate fields and change the binding of these fields to:

@datasource.query.filters.firstName._equals
@datasource.query.filters.lastName._equals
@datasource.query.filters.email._contains
@datasource.query.filters.StartDate._greaterThanOrEquals
@datasource.query.filters.Age._lessThanOrEquals
@datasource.query.fitlers.Department.Department._equals

The button that initiates your search should have the following code:

var ds = widget.datasource;
ds.load(function() {
  app.showPage(app.pages.YourSearchResultPage);
}
var calculatedDs = app.datasources.Employee_RecordCount;
var props = calculatedDs.properties;
props.FirstName_equals = ds.query.filters.firstName._equals;
props.LastName_equals = ds.query.filters.lastName._equals;
props.Email_contains = ds.query.filters.email._contains;
props.StartDate_greaterequals = ds.query.filters.StartDate._greaterThanOrEquals;
props.Age_lessequals = ds.query.filters.Age._lessThanOrEquals;
props.Department_equals = ds.query.filters.Department.Department._equals;
calculatedDs.load();

Now go to your search result page and make sure the you have the following elements:

1. A panel that the datasource is set to Employee_RecordCount. Inside this panel create a label and set the binding to @datasource.item.RecordCount.
2. A table that has the datasource set to the same datasource as created in the first step. Make sure your table has 'pagination' turned on.

That should be all, and this works in my application. It is a pain to set up, but I'm afraid it is the only workaround to have a total count of records. I should note that I have never had any paging issues either.