Page History
...
When a user types a value into a text field (which is bound to a specific column of the database table) and clicks out, the Servoy Application Server issues a SQL update command to the database to modify the selected record. The resulting change is also broadcast|display/DOCS/Data+Broadcasting||||||||||||||||\ to all connected clients.
...
The fundamental unit of data binding in both the GUI and the API is the Servoy Foundset|display/DOCS/Foundsets+Concepts||||||||||||||||\ object.
Client Cache
...
One of the primary jobs of a Foundset is to load records from the table to which it is bound. A Foundset object is always based on an underlying SQL query, which may change often during the lifetime of the Foundset. However the query will always take the form of selecting the Primary Key columns from the table and will also always include an Order By clause, which in its simplest form will sort the results based on the Primary Key columnscolumn(s).
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT customerid FROM customers ORDER BY customerid ASC |
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT * FROM customers WHERE customerid IN (?,?,?,?,?,?,?,?) ORDER BY customerid ASC |
Sorting
All foundsets contain a sorting definition that determines the order in which records are loaded and displayed. Sorting is always expressed in the ORDER BY clause of a foundset's query and thus handled by the database.
A foundset's sorting definition is encapsulated in a String property, which can be programmatically read using the getCurrentSort method, and written using the sort method.
Parameters for this property include an ordered list of one or more data providers, each of which having a sort direction, either ascending or descending. The string takes a form such that each data provider and its sort direction are separated by white space. The direction is abbreviated either asc or desc. Multiple data providers are separated by commas.
Example: Sort String Format
Code Block |
---|
'column1 asc, column2 desc' // Sort on column1 ascending, then column2 desceding
|
The order of the data providers determines their relative priority when sorting, such that when two records contain the same value for a higher priority data provider, the sorting will be deferred the next lowest priority data provider.
Example: The following sort string will sort, first on last name, and second on first name.
Code Block |
---|
foundset.sort('last_name asc, first_name asc');
|
The result is that all records are sorted by last name. But in the case where the last names are the same, then the first name is used.
last_name | first_name |
---|---|
Sloan | Zachary |
Smith | Jane |
Smith | Jon |
Snead | Aaron |
Available Data Provider Types
The following data provider types may be used as sort criteria:
- Any Column
- Any Related Column
- Any Related Aggregate
Example: Sort a customers foundset based on the number of orders each customer has, in this case a related aggregation.
Code Block |
---|
foundset.sort('customers_to_orders.order_count asc');
|
Results in the following query:
Code Block |
---|
SELECT customers.customerid FROM customers
INNER JOIN orders ON customers.customerid=orders.customerid
GROUP BY customers.customerid ORDER BY count(orders.orderid) ASC
|
Tip |
---|
Sorting on related columns and aggregates changes is simple and powerful. However this changes the nature of the foundset's query. One should be advised of this and ensure that the database is tuned accordingly. |
Scrolling Result Set
The Foundset maintains a scrollable interface for traversing record data. This interface includes a numeric index for every record that is returned by the Foundset's query.
...