Querying Data from a Collection

You can query the collection at any point where you can enter a Web SQL expression. The collection must be visible to you.

The following collections are visible to you in modules and components.

• Self-defined collections
• Virtual collections
• Collections in session modules

To define a Web SQL query

FROM <Collection> SELECT [CURRENT] <Column>

- OR -

SELECT [CURRENT] <Column> FROM <Collection>

The query listed first is advantageous in terms of auto completion because both the collection name and the columns of the collection can be auto-completed.

Example for a multi-column query

Query with Web SQL:

FROM Persons SELECT FirstName

FROM Persons SELECT (FirstName, LastName)

The Web SQL query relates to the collection. Persons is the name of the collection. LastName and FirstName are properties of a collection in Web SQL.

To show several rows (the names of all loaded employees for example) from a collection there are several Web Designer node types that are iterated through a collection. If you want to reference the current data set within one of these iterations, use the Web SQL keyword CURRENT.

Example for a multi-row query

FROM Persons SELECT CURRENT FirstName

There are also aggregate functions, which group rows of a collection together into a single value.

Example of querying a single value of a collection

This query returns the number of rows of the collection Persons

FROM Persons SELECT Count(*)

This query returns the minimum, maximum and average number of days

FROM AttestationPolicies
SELECT (Min(SolutionDays), Max(SolutionDays), Avg(SolutionDays))

Filtering Data from a Collection

You can limit a query to the relevant rows by using a filter condition when querying a collection with Web SQL. Enter the keyword WHERE and a condition in the Web SQL SELECT statement. The condition is a comparison of one or more columns with a preset value.

Example of a simple filter

Example of a filter with nested Web SQL expressions

Example of a filter to use within an iteration

You can also combine a filter with the keyword CURRENT if you are in an iteration.

Example of a filter with a condition

There are several places where you can only enter a filter condition with Web SQL.

If all employees are loaded in the collection "Persons" and you want to display men and women separately, proceed as follows:

• Define two grids that iterate on "Persons" and only differ in their filter conditions:
  1. Gender = 1
  2. Gender = 2

Collections

Collections play an important role when you are using the Web Designer. The data contained in a collection is required for working the components. Data in a collection is stored in rows and columns as in database tables.

A collection is referenced by name within a component or module. You can query collections with Web SQL. For more information, see Defining with Web SQL.

A component's data model defined with collections server as the basis for displays and further processing of data. The contents of collection can be loaded from the database but other data sources are also possible.

Detailed information about this topic

• "Database Objects" as a collection
• Working with a Database based Collection
• Loading Database Object through Relations
• Loading Database Objects from Multiple Tables
• Using the Database Query Wizard
• Loading a Historical Object State
• Loading a Change History
• View Definitions
• Collections as Data Sources for Controls

Database Objects as Collection

"Database Objects" as a collection

In most use cases, the database is the best data source for collections. All table defined in One Identity Manager can be used as data source for collections. Not all database table entries must be loaded in this case. Normally, you need to limit which data sets are loaded from the database with a WHERE clause on performance grounds.

To load a simple database based collection

1. Open the module or component to which to add the collection.
2. Select the Collections node with a right click.
3. Select the Database object option.
4. Click the new node.
5. Select the database table in the Node editor window, under Object type.
6. Enter the name of the new collection under Identifier.

To load the contents of a database table

1. Select the Initializer node of the component with a right click.
2. Select the option Load collection in the submenu Data actions.
3. Click the new node.
4. Select the relevant collection under Collection in the Node editor view.
5. Using the Filter condition property, enter an filter condition expression if you wish.