A Subsearch projection offers a simple way of integrating a single simple value (or in special cases multiple simple values) from the data of entities of any entity type into the context of the parent search (_parentQuery).

The term subselect refers to the methodology for integrating data from an 'external' entity as a 'subquery', which can provide specific values for different functional sections (e.g. output columns or Restrictions) of the higher-level search.

The area of application of Subsearch projection overlaps with that of Joins. However, there are constellations when integrating or looking up data that can only be realized in one way or the other.

• On the one hand, a Subsearch projection is often 'more expensive' in terms of the performance of a search because the data of the integrated entity type is retrieved 'line by line' and therefore often repeatedly (and possibly redundantly).

• On the other hand, the Subsearch projection offers a degrees of freedom for constraints and the preparation of the integrated data (e.g. Group by and Aggregate projection), which are not available in the context of Joins.

The following restrictions must be observed when using the Subsearch projection:

1. Only Projections that provide values that are 'simply' represented in the database (by exactly one column) can be used as a Projection for column subselect.
   
   

   • Example: Enumeration values
     

     • An enumeration value is stored at database level either as a Long value ('Ordinal') or, in exceptional cases, as a String ('Name'). In any case, the database uses a simple value, even if it appears complex in the XML.

     • Values from dynamic or static enumerations are therefore easily suitable as Projection for column of subselect.
       
       

   • Example: Date values
     

     • Timestamp field – e.g. the 'Created' (created) for any entity – is easily suitable as a Projection for column of subselect.

     • A DateTime field – e.g. the 'password expiry date' (passwordExpiryDate) for Users – is not suitable as a Projection for column of subselect, because a date/time field at database level requires two 'columns' (for UTC milliseconds and time zone).

     • The UTC milliseconds of a DateTime field – e.g. the 'PasswordExpiryDate.dateValue' (passwordExpiryDate.dateValue) property for Users – can easily be used as a Projection for column of subselect.
       
       

   • Example: List fields

     • In principle, 'list fields' – e.g. 'parentCompanies' (parentCompanies) for Company accounts – can be used as a Projection for column of subselect.

     • However, the Projection for column of subselect may only provide several values where these can also be processed:

       • If the Subsearch projection defines an output column (in a tuple or CSV search), then only a single value is permitted as a return value. With a 'list field' as a Projection for column of subselect, the search is aborted at runtime with an error as soon as the list field returns multiple values for the same result row.

       • Where 'multi-value' content is permitted – e.g. on the right-hand side of a Field restriction with the in compare type – a 'list field' can easily be used as a Projection for column of subselect.

     • ►IMPORTANT◄ The use of a Collection projection as a Projection for column of subselect within a Subsearch projection to project a list into an output column is not possible for technical reasons.

       • However, the reverse approach is more effective: A Subsearch projection with a 'list value' as a Projection for column of subselect can be used in a Collection projection to provide values for a collection.
         
         

2. The Subsearch projection may only return exactly one result, unless the context is explicitly aimed at a list.
   
   

   • The uniqueness of the result can only be enforced in the parent search by setting the 'Maximum results' parameter to 1. The Subsearch projection, on the other hand, has no 'limit' for the number of 'result rows', so you cannot simply define the 'first result' as the return value.

   • If necessary, an Aggregate can be used in the Projection for column of subselect, e.g. to select the minimum or maximum value from several values.

   • Within Collection projection.

►IMPORTANT◄ In contrast to the parent search, no access restrictions apply to the subselect. If the Entity parameter refers to the same entity type as the parent search, the subselect always takes into account all existing instances of this type, while the parent search only returns a real or fake subset, namely the entities for which there is read access in the execution context.

String