Collection projection
Projection – Abstract
Purpose: Collects values for the Property of collection and integrates them into the search via the Property to join.
A Collection projection returns a list of values from the projection in the Property of collection parameter, which have a relationship to the search defined via the Property to join parameter.
If the projection configured as a Property of collection returns 'No value' ($null), no entry is added to the collection. The collection may therefore contain fewer entries than the data source provides for the Property to join. This characteristic corresponds to that for the Collect values resolver.
►NOTE◄ Because the collection ignores $null values, a Case projection can be useful in the context of the configuration for the Property of collection in order to achieve the following goals (possibly simultaneously):Instead of a $null value in the original data, a specific replacement value can be assigned that identifies 'empty spaces' (or undefined values) within the collection.
Conversely, specific values can be replaced by 'No value' ($null) depending on a condition, so that the collection only lists a subset of the values that are actually available.
As a default, the property 'ID' (id) is preset as the Property to join, as the Collection projection is typically used to collect data that is directly related to the entity being searched for.
In order to 'collect' the values for the collection, an additional database query is performed internally, which lists the values for the Property of collection in conjunction with key values for the Property to join. From this 'master collection', a specific subset is assigned to each entity in the search as a value list via the Property to join.
►IMPORTANT◄ The query executed in the context of the Collection projection to 'collect' values takes place without regard to access restrictions that would apply in the context of a direct search for the same data. Therefore, internally (as with a fully-fledged join) there is full access to all instances of the entity being searched for.
What does this mean?
As long as the default value 'ID' (id) is selected for the Property to join or there is no selection, this is not critical, as the Collection projection effectively only 'delivers' data from instances of the entity being searched for that could also be retrieved directly.
If a Property to join other than 'ID' (id) is selected, the Collection projection can also 'reveal' data from instances for which there is no direct access but a relationship to a retrievable entity via the Property to join.
Example: A search for Company accounts uses a Collection projection in which the 'owner' (ownerId) property is selected as the Property to join. For each directly retrievable company, values (from the projection defined as a Property of collection) are then assigned as a list that were collected in connection with any company that has the same 'owner' as the directly retrievable company (see 'Examples' below for more details).
Configuration
|
Parameter |
Type |
Description |
|
Name |
String |
The optional Name parameter can be used to assign an (alias) name to the projection.
|
|
Property to join |
String |
The Property to join determines which field is used to integrate the values provided as a collection into the context of the search. The Property to join parameter defines a path to a field as a character string that can be specified by direct input or selection from the dropdown (for details, see Property projection, 'Property' parameter). If no selection is made for the Property to join, the 'ID' (id) property is used. This property is also set as the default value. |
|
Property of collection |
Projection |
Any Projections can be used for the Property of collection parameter. The projection determines which values are collected and then assigned via the Property to join in the context of the search. |
Examples
Simple example: Listing the assigned roles per user
A tuple search for Users should output a list of the Roles assigned in the user account for each user in connection with the 'Username' (username) property.
Configuration:
|
The screenshot shows the configuration for the tuple search Projections:
►NOTE◄ We do not make explicit assignments for the Name parameter in both Projections, so that the username and roles property names identify the columns |
|
Runtime example:
|
In addition to the text value (username), the result of the tuple search (right) shows a list value (roles) that references a different selection of roles for different Users via Long values. The Roles assigned to each user are assigned to them. ►NOTE◄ The user account itself does not provide any more information about the role. Detailed role data could be included via Entity join and then also taken into account in the Collection projection per Property of collection. ►NOTE◄ The XML format also makes it clear that the order of the entry elements for list entries appears 'unsorted'. In fact, there is also no option to sort the values collected in a collection column within the query. If necessary, the result must therefore be edited afterwards, if possible. |
<core:TupleSearchResult maxResults="100" count="43"> ... |
Simple example: Listing all users with the same owner
A tuple search for Users in connection with the field 'username' (username) should display a list of usernames of all users whose accounts refer to the same company account as 'owner' (ownerId) as the user in question.
|
The screenshot on the right shows the Projections for a tuple search:
|
|
Runtime example:
|
The result of the tuple search (right) shows a list value (SAME_OWNER) in addition to the text value (username), which specifies the names of all Users for which the 'owner' company of the user is also considered the 'owner'. ►NOTE◄ As the example data should illustrate, the user name output in the username column always appears in the collection as well. This cannot be prevented in the context of the Collection projection, as the configuration does not provide any secondary conditions for the assignment of the collected data apart from the Property to join. However, the Collection projection can be used to ensure that only user names other than the one in the username column appear in the SAME_OWNER column. The following more complex example shows how this works. |
<core:TupleSearchResult maxResults="100" count="3"> |
More complex example: Listing all other users with the same owner
Deviating from the previous example, the aim is to ensure that the 'username' output in the username column is not mentioned again in the associated list in the SAME_OWNER column. So the list should have all other users with the same 'Owner' company (ownerId).
Configuration:
|
Since no arbitrary conditions for the assignment of the collected data can be formulated within the Collection projection, we have to set up a Entity join in our search, which explicitly serves to identify the other users with the same owner. The Entity join is configured as shown on the right:
|
|
|
The screenshot on the right shows the Projections, with which the desired result can be achieved:
|
|
Runtime example:
|
With the same sample data as in the previous example, the list output in the SAME_OWNER column now contains one entry less, because the main user now only appears in the username column. The result appears much clearer in a CSV search:
►NOTE◄ This example also shows that the order of the list entries within the SAME_OWNER property is 'unsorted'. It is not possible to influence the order within the collection. Sorting according to a Property projection such as u2.username is also not helpful. On the contrary: The Collection projection still returns unsorted and the sorting multiplies the output lines (user x u2) although the option Optional is selected in the Entity join (u2) (see above). |
<core:TupleSearchResult maxResults="100" count="3"> |
More complex example: For each user, list the account numbers of the companies that can be selected when logging in
A tuple search for Users should display the account numbers of all companies that can be selected as Company of session when logging in in a list column for each user.
Configuration:
The Users search uses a Entity join for the 'Company account' entity, which assigns the company accounts referenced in the 'Companies' (companies) property to each user account under the alias name ca (for CompanyAccount).
The address field 'Account number' (address.accountNumber) is transferred to a list from the data of the company accounts assigned via Entity join using Collection projection:
|
The Collection projection shown on the right for the list column with the Name 'CA_ACC_NUMBERS') uses a property projection with the path ca.address.accNumber specified in the Property to join parameter to directly access the 'account number' property of each company that the Entity join (ca) assigns to the respective user account With this configuration, the collection does not provide any references to companies for which no 'account number' information is available. |
|
The Collection projection should now be adapted so that the text 'UNIDENTIFIED_ACCOUNT' appears in the CA_ACC_NUMBERS property if a company without an account number is assigned to a user:
Specifically, the 'account number' should be considered completed if it contains at least one arbitrary character. Within a Case projection for the Property of collection, a Field restriction defines the condition 'like _%'.
If the 'account number' is filled in (left branch of the case differentiation), the return value (as above) is passed directly from the Property projection for the path ca.address.accNumber.
If the 'account number' is not filled in (right branch of the case differentiation), the static text UNIDENTIFIED_ACCOUNT is assigned as the return value via a Literal projection.
Based on the existing configuration, it should now be prevented that the collection in the CA_ACC_NUMBERS list column takes into account company accounts in whose account the 'Metatype' property refers to the Company meta type 'Group' (GROUP).
The 'group' company accounts should explicitly not be filtered out in the Entity join so that their data can be output separately in the Collection projection for a further list column.
The following screenshot shows an additional branch in the Case projection for the Property of collection, in which the 'meta type' of the company (ca.metaType) is evaluated before all other conditions in order to assign 'No value' ($null) if the Company meta type 'Group' (GROUP) is present.
If 'No value' ($null) is assigned as the return value, neither any assigned 'account number' nor the 'UNIDENTIFIED_ACCOUNT' text appears in the collection.
