You can design tables that search, filter, page, and order the correct set of records, whether you are showing a large database result or objects already present in a ViewModel.
Choose the data strategy before designing the table
A table can look identical to the user while its data is obtained in two very different ways. Choose the strategy based on how many objects exist and where the objects are currently available.
| Situation | Recommended approach | Example |
|---|---|---|
| You need to find records among tens of thousands of database rows. | Use a Seeker search. Define a search expression in the ViewModel. OCLps (the SQL-translatable subset of OCL) is executed in the database, so only the matching result needs to be loaded. | A user searches a customer database for Adam.
|
| You need to show objects related to the object already on screen. | Use the existing context and its related objects. Decide separately whether all rows can be sent to the client. | A customer form shows that customer's orders. |
| The result is too large to send to the client at once. | Page or otherwise limit the result, and keep operations that affect the complete result on the server/database side. | Search returns 1,000 customers, but the table displays 100 rows at a time. |
| All rows are already loaded and shown in one client-side list. | Client-side ordering can be correct because the client has the complete set being presented. | A customer has 12 orders and all 12 are displayed. |
For the procedure to create a Seeker and its search expressions, see Documentation:Searching. For page-based result handling, see Documentation:Search result pages.
Search and filter are different inputs
Search is text entered by the user to find matching records. A Seeker commonly uses the vSeekParam variable as the search parameter and populates vSeekerResult with the result.
Filter is a selected or known condition that reduces the result set. It can come from the current context or from a choice in the user interface.
For example, a customer table may use both:
- Search text:
Adam. - Filter: customers in a selected country.
- Order: last name, then first name.
These are independent concerns. A search determines what text matches; a filter determines what conditions apply; an order determines how the matching rows are presented.
When the database contains many rows, perform search and filtering through the database query rather than loading all objects into server memory. OCLps is intended for this type of database search because it is translated to SQL. Read the Seeker and OCLps search documentation for the ViewModel setup.
Ordering must apply to the complete result
Ordering is the sequence in which the rows that match the search and filters are displayed. It is not a cosmetic operation when the table contains only part of a larger result.
Consider this search:
- A user searches for
Adam. - The database finds 1,000 matching customers.
- The application loads and displays the first 100 rows, initially ordered by first name.
- The user requests ordering by last name.
Sorting only the 100 rows already in the browser does not produce the first 100 customers by last name. It produces an order for an incomplete subset. The user expects the order to apply to all 1,000 matches.
In this situation, rerun the search in the database with the requested ordering and return the appropriate page of the newly ordered result. The same rule applies when filtering or pagination is server-side: server-side ordering is required for a correct result.
Decide where ordering runs
Use this rule: the component that holds the complete set to be ordered must perform the ordering.
| Data available to the client | Where to order | Why |
|---|---|---|
| All rows in the displayed list | Client side | The client can order the entire displayed set correctly. |
| One page or partial result from a database search | Database/server side; rerun the search with the selected order | The client has only a subset and cannot determine the correct global order. |
| A larger set is loaded on the server but only a subset is sent to the client | Server side | The server has the full loaded set; the client does not. |
| Server-side filtering or pagination | Server/database side | Ordering must use the same complete filtered result as paging. |
Do not add client-side header sorting to a paged Seeker result as a substitute for database ordering. It can make the table appear sorted while omitting rows that should occur earlier in the requested order.
Implement search, filter, and order in a ViewModel
For a database-backed result, use the following design sequence:
- Create the ViewModel that will contain the search result.
- Add search expressions to the ViewModel as described in Documentation:Searching. Use OCLps criteria so the search can execute as SQL in the database.
- Define variables for the user-entered search text and any selected filter values.
- Display
vSeekerResultin the table. - If the result is paged, follow Documentation:Search result pages so that the requested page is obtained from the complete result.
- Store the requested ordering as part of the search state, then apply that ordering when the search is run or rerun.
- When the user changes the order, rerun the server/database search before displaying the page.
A ViewModel can also contain multiple possible order expressions. An active expression can determine which ordering is used. This is useful when, for example, a user can choose between name order and telephone-number order. Apply the selected order to the final result, not to an intermediate part of a combined search.
Handle combined criteria carefully
Combined search criteria can form intersections: each criterion narrows the same final result. Ordering belongs to that final result. Do not treat an order by as an order for an individual part of an intersection.
For example, if one condition selects customers named Adam and another selects customers in a country, first determine the customers that meet both conditions. Then order that resulting customer set by the requested attribute.
Context lists can use client ordering when complete
A list based on an existing relationship does not automatically need a database search. For example, a customer detail screen can show the customer's orders through the current customer context.
If every related order is sent to the client, client-side ordering of that list is valid. If the relationship can contain too many rows to send at once and you page or filter it on the server, use server-side ordering instead.
Table interaction, row selection, multi-selection, and table presentation are documented separately in Documentation:Tables and Grids. Do not use this page to configure editable cells; see Documentation:Nesting.Editable.
Checklist
Before enabling ordering in a table, verify the following:
- Do you search the database when the possible result set is large?
- Are search text, selected filters, and requested ordering modeled as separate inputs?
- Does the ordering run where the complete result set is available?
- If the table is paged, does changing the order rerun the query before the page is shown?
- If client-side ordering is used, are all rows that the user expects to order already present on the client?