Datasources

Datasources define how your application queries for data associated with your models and how your UI accesses this information. For example, every datasource has an item property that points to the selected item in a datasource. In your UI, widgets use the item property to display or edit information about the currently selected record in the datasource.

By default, a model comes with a query datasource that has the same name as the model. A query datasource stores information about a query against a single model and the records from the last execution of the query. The query in a datasource can be changed and re-executed through UI widgets and client-side scripts. Similarly the results of the last query can be bound to widget properties and accessed in client-side scripts. When creating your UI, you typically associate a query datasource with the top-level panels of pages or the composite widgets. From there, all child widgets of the panel automatically inherit that datasource, and you can easily associate widget properties with fields in the records stored by the datasource.

Relation datasources allow you to display data about associated records. You create a relation datasource from a parent datasource, another query or relation datasource, for a particular relation end. Relation datasources display associated records for a relation end from the parent datasource's currently selected record. A common use of relation datasources is to implement a master-detail view. For example, if you had two models Order and Item with a one-to-many relation between them, you could build a UI to display an order and its related items. You can find more information on using relation datasources in the UI editor in the documentation on binding data to your UI.

Life cycle

Every datasource can be in one of four states:

1. Unloaded
2. Unloaded + Loading
3. Loaded
4. Loaded + Loading

Conceptually a datasource that is unloaded does not have any data; the items of the datasource will be an empty array and the currently selected item will be null. A datasource that is loading is in the process of getting data. A datasource that is loaded has data, although it is possible that the data will be empty.

For a datasource to become in one of the loading states, a load must be triggered. Loads may be triggered in different ways:

1. If the datasource is set to automatically load data, which is the default for all datasources, then if there is any binding to a datasource's data properties, a load is triggered. For example, if you have a label widget in a page A that is bound to a @datasources.Model.items.length, then Model datasource will be loaded when the page A is shown.

2. If the datasource is set to automatically load data, which is the default for all datasources, then if there is widget that uses that datasource, when the widget is loaded, it will trigger a load on its datasource. The most common way for a widget to be loaded is for it to appear on the screen when a user opens its parent page.

3. You can also trigger a load using a client-script by calling the load, loadPage, prevPage, or nextPage methods on DataSource.

You can disable automatically loading data for query datasources, which prevents the first two aforementioned load triggers. You can also unload a datasource by calling the unload method on a datasource.

Query datasources have an onLoad event. The onLoad event is triggered when a datasource transitions to the loaded state. You can specify a piece of code to run when the event occurs where you edit query datasources. The onLoad event is useful when you want to perform an action after a data source has loaded data. For example, you could use the onLoad event to make a widget visible once data is available.

Client Script - onLoad:

var widget = app.pages.MyPage.descendants.MyWidget;
widget.visible = true;

Creating and editing query datasources

When you create a model, a query datasource is automatically created with the same name as the model. Whenever, you change the model name, any query datasource with the same name will also have its name changed to match the new model name. You can create additional query datasources for a model. In the model editor, click Add chevron_right Datasource. A new datasource is created with a default name. Often you will create additional query datasources because you want to have different options selected for a single model such as having different sort orders or different page sizes.

To edit an existing query datasource, open the model editor for the data source. Then expand the Datasources tree item and select the datasource.

Manual Save Mode

The default behavior for datasources is that when any changes are made to an item, they are saved to the server right away. For example if a Text Field widget's value changes in the UI, it will automatically save the change to the model field that it is bound to.

However if you wish to disable this autosave behavior, you can put the datasource in manual save mode. On the Datasources tab in the Model editor, when you check the Manual save mode box, your input fields and forms will behave like traditional web forms. To save changes, a user will have to click a Save button that has the Save Changes to Datasource action, which calls the saveChanges() method on the client.

In general when using manual save mode, any changes made to records accessed through the datasource item and items properties are stored locally on the client, and can be saved or rolled back. This includes records accessed through relation properties from records in the datasource results.

A datasource in manual save mode has the following methods that can be called from client-side scripts:

• saveChanges: Saves the current changes to the server.
• clearChanges: Abandons any changes since the last call to saveChanges.
• hasChanges: Returns true if the datasource has unsaved changes.

If a datasource in manual save mode has changes, you cannot load data until either those changes have been saved or cleared.

Automatically Load Data

By default, datasources automatically load their data whenever a widget is bound to data on the datasource. If you turn off the automatically loading data setting, then the datasource will only load data when you call load, loadPage, nextPage, or prevPage methods. This is often useful, when you want the user to provide the value of a query filter before you load data.

Page Size

This controls the number of records shown by this datasource for a single query. By default, the page size is 100 records. For example, suppose the server has 10,000 records and you need to make all of them available to the user. Because loading them all at once into the UI would be both slow and unmanageable, you would instead set a page size and create a Next button to call the nextPage method on the datasource.

The page size is also a property of the datasource and can be bound to in the UI as well as accessed and modified in client-side scripts. For example, you can bind the pageSize property to a slider widget's value property to allow a user to control how many datasource results are displayed to them.

Prefetch

Normally when a record is loaded by a datasource, associated records are not loaded from the server. This means that if your UI or client-side scripts navigate relations from records loaded by the datasource, they need to load the associated records from the server. Sometimes, if there are lot of records and you need to load the associated records for each one, the application will be slow since many requests will be sent to the server. For example, let's say you have an Employee model with a Manager relation to itself with two relation ends: Manager and Subordinates. If your UI displays a list of Employee records using a query datasource, and for each employee displays the name of their Manager, then App Maker will request the associated manager record for each employee record in the datasource.

Specifying prefetch can help improve performance by telling App Maker to load related records with query results.

On Load Action

Used to run client-side JavaScript whenever the datasource loads (goes into the loading state. Each time the results of a query are returned from the server to the client, this action is triggered. For information, see the section on the life cycle of a datasource.

On Item Change Action

Used to run client-side JavaScript whenever the current item for the data source changes. The current item can change for different reasons.

• The datasource reloads its data and the existing current item is not in the returned data.
• A client-side script calls the next or prev methods on the data source.
• A client-side script or binding assigns a value to the item property on the datasource.
• A client-side script calls the selectIndex or selectKey methods on the datasource.
• Some widgets automatically change the current item for the datasource. For example, the list widget will change the current item of its datasource whenever the user clicks on a row.

Query Script

The query script property allows you to override the normal query behavior of a datasource by specifying custom server-side logic. Or, in the case of a calculated model, the query script allows you to completely define the query behavior of a datasource.

Normally, App Maker executes the query specified by the datasource using the appropriate data backend. For example, if your query returns all the records in the Employee table, then App Maker returns all those records as the data for the datasource. However, if you specify a query script, you can override that behavior. For example, let's say that you want to only return employees whose age is an even number and apply a minimum age specified in the query. You could write the following code snippet in the Query Script code editor:

var employeeResult = [];
// Modify passed query to additionally filter by minimum age.
query.filters.Age._greaterThan = 20;
var employeesAboveMinimumAge = query.run();
for (var i = 0; i < employeesAboveMinimumAge.length; i++) {
  if (employeesAboveMinimumAge[i].age % 2 == 0) {
    employeeResult.push(employeesAboveMinimumAge[i]);
  }
}
return employeeResult;

In the case of a calculated model, since actual EmployeesByLocation records don't exist in the data backend, the query script needs to create record objects on the fly by calling newRecord(), for example:

var calculatedModelRecords = [];
var recordsByLocation = {};
var allEmployees = app.models.Employees.newQuery().run();
for (var i = 0; i < allEmployees.length; i++) {
  var employee = allEmployees[i];
  if (!recordsByLocation[employee.location]) {
    var calculatedModelRecord = app.models.EmployeesByLocation.newRecord();
    calculatedModelRecord.numberOfEmployees = 1;
    calculatedModelRecord.location = employee.location;
    calculatedModelRecords.push(calculatedModelRecord);
    recordsByLocation[employee.location] = calculatedModelRecord;
  } else {
    recordsByLocation[employee.location].numberOfEmployees++;
  }
}
return calculatedModelRecords;

Since query scripts often contain more code than this example, you can use the Query Script code editor to call a server-side script function. Note that you can access some of the query parameters in your query script. You must return an array of records that belong to the model of the datasource.

A query script is often useful if you need complex filtering logic for records that the data backend cannot provide. For example, if you might want to filter data based on the current stock price for a company (obtained using a web service).

You can pass parameters from the client to your query script on the server by adding Custom Properties . To do so, click on the Add Properties under Custom Properties and select the type of the parameter and edit the name. The parameter can be accessed and bound to under the properties property of the datasource on the client. For example, you can bind a text field widget's value property to the query script parameter using bindings:

textField.value <-> datasource.properties.MyParam

In your query script, you can access the parameter as a query parameter, e.g. we can modify the example above to only add the minimum age filter if a boolean custom property parameter called RestrictAge is true:

// Modify passed query to additionally filter by minimum age
// if client asks us to do so.
if (query.parameters.RestrictAge) {
  query.filters.Age._greaterThan = 20;
}
var employees = query.run();

SQL Query

The SQL query field is only used for SQL datasources. You can learn more about SQL datasources here.

Query Builder

Use query builder to create user interfaces that can execute complex queries based on information submitted by your app’s users.

For example, you could build an app that lets users search an HR database to identify employees who qualify for different retirement plans. Typically this type of search would require you to understand SQL syntax and to write a custom script to interpret user input and apply it to a database search.
However, query builder helps you create the logical expressions your app needs to find data. Query builder also supports code completion (use Ctrl+Space), making it even easier for your app to extract the right information from data models.

Constructing a query

For our HR database example, the app needs to return employees who are within a given age range or who started work after a specified date.

The query looks like this:

(Age >= :AgeMin and Age < :AgeMax) or StartDate > :StartDate

• Age—A field in the app’s model. It contains the age for each employee. The app asks users to enter minimum (:AgeMin) and maximum (:AgeMax) ages. The query returns employees who fall within that range.
• or—An operator. It tells the app to return employees who meet either criteria.
• StartDate—A field that contains the date each employee started work. Users enter a :StartDate, and the app returns employees who started after that date.
• Colon (:)—Query builder uses colons to identify parameters that allow the app’s UI to pass values to the expression.
• Parentheses—Identify a subquery. App Maker evaluates the subquery before moving on to :StartDate.
  

Binding a query expression to a UI

As you add parameters (preceded with colons) to your expression, query builder automatically recognizes them and creates query parameters in the area below query builder. They also appear in the data-binding UI, which you’ll use to bind the parameters to widgets in your app.

Using the HR database example, the next steps are:

1. Open Widgets widgets and add two text boxes text_fields to a page, and then bind their value properties to:
   • @datasource.query.parameters.AgeMin
   • @datasource.query.parameters.AgeMax
2. Add a date box widget date_range to the UI, and then bind its value property to:
   • @datasource.query.parameters.StartDate
3. Change the onValueChange event to Reload Datasource for the input widget.

More about query syntax

Remember these rules when you construct queries:

• Query expressions are evaluated from left to right.
• The and operator takes precedence over the or operator.
• Subquery expressions within parentheses takes precedence over and and or operators.
• The "!" operator negates the boolean value of an expression.
• The "?" modifier for leaf operators considers a null value for the right-hand side of the expression to be true. You cannot negate expressions that use the "?" modifier.

Here are some additional examples of query builder expressions:

Create Mode Datasource

Use a create mode datasource to create new items in a parent datasource. This mode is supported in query and relation datasources. The create mode datasource belongs to the same model as the records in its parent datasource and is used as a template when creating new records.

For example, if you had a model Person with a Name field, and you set the Name property of the create modes' record to "Jane Smith", then a record created through that datasource would initially have the name "Jane Smith".

var createDatasource = app.datasources.person.modes.create;
var draft = createDatasource.item;
draft.Name = 'Jane Smith';
createDatasource.createItem(function(createdRecord) {
 alert('Created record name: ' + createdRecord.Name) // Jane Smith
});

You can also bind UI elements to fields of the create datasource's item, allowing users to specify the initial value of fields for created records. A common pattern is to create an insert form that contains input elements for every field in a record where the value of the input elements is bound to fields on the create datasource's item:

1. Create a datasource called Person with a string field of name.
2. From Widgets widgets, drag a Form onto the page to the right of the edit form.

The datasource of the form is bound to Person (create). Each field is bound to datasource.item.FIELD_NAME.

The Clear button's onClick action is widget.datasource.clearChanges(), which clears the create datasource's record. The Submit button's onClick action is datasource.item.createItem(), which creates the item and adds it to its parent datasource.

Setting field values on the create datasource's record is a way to make sure that required fields on your records have a value when they are created. Once a create datasource initiates a record creation, the create datasource's record field values are reset to their default values.

Query datasources

A query datasource contains some properties and methods for specifying a query that will be executed against a data backend. If you trigger a load on a query datasource, the query datasource will send its current query to the server and update its data with the results of that query. Any changes to the records that are in the result of the query are also updated on the client. This means that even if which records are in the query result do not change, you may see a change in record values on the client, because your application made a change to a record in a server-side script, or another client updated a record.

Query

The query object represents the query sent to the server when a datasource is loaded. It contains properties that specify the query conditions and the page of results to return. The query object's filters property holds the query conditions on the fields of a model. For example, the following code adds a query filter that matches records that have the name "John Smith".

var datasource = widget.datasource;
datasource.query.filters.Name._equals = 'John Smith';

For a list field, one or more items in the list must satisfy the query filter. For example, this record:

record.Emails = ['smith.j@example.com', 'john@example.com']

satisfies this query filter:

datasource.query.filters.Emails._startsWith = 'john@';

because one of the items in the Emails list field starts with john@.

Each field that can have filters is a property under filters, in the example above, name is the name of a field. Those fields in turn have properties for each filter operator they support. The supported filter operators depends on the type of the field and the database backend. The API documentation contains more information about filter operators. The value assigned to the operator property is the parameter used for the field and operator in the query. The type of the value must match the type of the field except for the in and notIn operator whose parameter values should be arrays of the field type. For example, you can use the in operator to add a query filter that matches records whose favoriteColor field's value is blue or green.

var datasource = widget.datasource;
var colors = ['blue', 'green'];
datasource.query.filters.FavoriteColor._in = colors;

If the value of a query operator is null, then no filter is added for that field. If you need to compare a field against null, then you need to use the query builder feature. If you assign multiple values to properties under filters, then records must match all the filters. The following code adds query filters that match records that start with the name "John" and whose age is more than 18.

var datasource = widget.datasource;
datasource.query.filters.Name._startsWith = 'John';
datasource.query.filters.Age._greaterThan = 18;

You can find out more information on available operators in the API documentation for Query.

The query object also contains relation filter properties, that allow you to filter records by an associated record, its field or key. For example, if your application has a model Employee with a relation to itself representing managers and their subordinates, you could match records that have a particular manager by assigning the corresponding relation filter property the manager record:

 var datasource = widget.datasource;
 datasource.query.filters.Manager._equals = managerRecord;

Here's how you can filter by manager's record keys:

 datasource.query.filters.Manager._key._in = ['manager1RecordKey', 'manager2RecordKey'];

And here's how you can filter by manager's manager name:

 datasource.query.filters.Manager.Manager.Name._equals = 'John Doe';

Like field filters, assigning null to a relation filter property clears that restriction.

Please note that filtering is available for one-to-one and many-to-one relations only.

Sorting

You can sort the data in the datasource by any sortable field of the model it's based on. The sort order can be specified in two ways; in the datasource configuration UI or using a script . The datasource configuration UI can only specify a single field. However, with scripting you can specify multiple fields to sort by. Read more about sorting with scripts under the sorting property for Query.

You can also sort by fields in related records, as long as the relations ends are one-to-one or many-to-one. For example, if you have a many-to-one City-State relation, you can sort cities by the name of the state they are in.

Keywords

The keywords property can be used to specify general search terms. The exact behavior is database backend specific.

Page Index

The page index property specifies the page number (1-based). You can change this property to any positive number and the next time the query datasource is loaded, that page number will be loaded.

Page Size

The page size property specifies the number of records to return from the server for a query result.

Relation datasources

Relation datasources allow you to easily display associated records for a record and a particular relation end. Every relation datasource has a parent query or relation datasource. The relation datasource contains the associated records for its parent datasource's current record for the specific relation.

You can set a relation datasource for a widget by using the simple data source picker if the widget's parent widget has a query or relation data source. The simple picker will show you a relation datasource for each relation end that starts from the parent datasource's model. Select the relation datasource that you would like to use. You can find more information on using relation datasources in the UI in the documentation on binding data to your UI.

If you trigger a load on a relation datasource, then it loads the associated records for the current record of its parent datasource from the server. This means you can reload associated records from the server by calling load on a relation datasource. You might want to do this if the associated records were changed as a result of a server script rather than through client-side scripts or bindings. You can also use the _reload method on the current Record of the relation datasource's parent datasource to reload the data for the relation datasource from the server.