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.
