Queries

Queries serve to request data from the underlying LSPS database or an external database.

Important: It is not possible to query system shared Records, such as, human::Person, human::SavedDocuments, etc. Use functions of the Standard Library to acquire their values.

Defining Queries

To define a query, do the following:

1. Create a query definition file in your Module: right-click the Module, go to New > Query Definition.
2. In the query editor, click the add button for the type of query you want to create:
   • standard queries defined in the Expression Language
   • HQL queries defined in HQL
   • native queries defined as database statements

Calling Queries

A query is called in the same way as a function: <query_name>(<parameters>).

Standard Queries

A standard query returns shared Records from the database. Its properties are defined in the Expression Language, which makes it simple to create a query; however, for more complex queries, it might not be a suitable option (consider using either HQL or native queries).

A standard query defines the following properties:

• Name: name used when calling the query
• Record type: entries of the shared Record type retrieved from the database

• Single value: amount of the returned entries

  If true, only the first entity is returned. If false, all entities are returned.

• Distinct: whether to return only unique entities

  If enabled, identical entities result in a sole entity in the returned data.

• Public: availability of the query in Module imports.

  If not public, importing Modules cannot use the query.

• Iterator: object used to refer to the currently processed entity
• Join: joins to other tables via Record relationships
• Parameter: query parameters
• Condition: Boolean expression which must be true for the entity to be included in the result (equivalent to WHERE)
  • Only the following functions with the entity iterator as their parameter define their database statement equivalent: toLowerCase(), toUpperCase(), trim(), length(), substring(): no other functions can be used in the query condition:
    • getDayOfMonth(book.published)=5 as query condition is invalid since the getDayOfMonth() function does not define its translation to SQL.
    • toLowerCase(b.Author)=="joseph heller" is a valid query condition since the toLowerCase() function does define its translation to SQL.
  • You can use the * and ? wildcards. Note that it is not possible to escape these characters. If required, consider using Native Queries instead.

    Important: the condition is interpreted into SQL and the interpretation does not fully correspond to its Expression Language interpretation mainly due to the fact that the null value is not considered a legitimate value in databases. Hence, != null is interpreted as is not null and == null is interpreted as is null. For example, if you use the condition x != true as a query condition, if x is null in the database, it will not be included in the results.

• Join Todo List: allows to create a join to a list of to-dos which are ALIVE

  If the shared Record has a relationship to the human::Todo record, you can use the join to get the to-dos related to your Record, typically, in the Condition. This mechanism makes up for the absence of the direct access to the to-do database tables.

  • Query Todo Iterator: object that holds the current To-Do
  • Todo List Criteria: criteria for the to-do entries

    new TodoListCriteria(
      person -> p,
      includeSubstituted -> true,
      includeAllocatedByOthers -> true
    )
    

• Paging: the query returns the number of entities defined by the Result size starting from the Start Index position.

  If the start index and result size are both null, the query returns all results: no paging is applied. If you keep track of the current start index, you can implement paging; you can, for example, save the current index in a variable and increment it on each request. Hence the following properties are required when paging the results:

  • Start index: index of the first entity returned in a result
  • Result size: size of the batch request (maximum number of entities returned by the query starting from the start index position)
  • Generate count query: if checked, the system generates a function that takes the same parameters as the query and returns the number of entities in the result set. The result is not influenced by the start index and result size properties.

  By default, the count function uses the name of the query with the _count suffix. To set a custom name, enter it in the entry field next to the check box.

• Dynamic Ordering and Static Ordering:

  Sorting of the result set with the dynamic ordering definition evaluated on runtime and static ordering remaining unchanged.

• Fetch joins: initialize join entities with the entity's parents with a single select

  Fetch joins prevent performance problems that occur when every record is fetched with a separate database select.

Filtering Results in Standard Queries

You can filter the query results depending on the values in:

• the queried Record: define the filter condition in the condition of the query definitions
• the related Records: define a join in the query definition.

Defining a Join in Standard Queries

Joins in Queries enable filtering of the Record entries based on related Record data, such as, get Authors who wrote a Book in 1983: in this case you would use an inner join to author's book and check if a book was published in 1983.

You can use joins on Records that are connected to the returned Record with a Data Relationship. Make sure, the end pointing to the related Record is named. Note that joining on a query does not fetch the joined table: the query still returns only the Records of the query return type.

To define a join in your standard query, do the following:

1. Open the query definition.
2. Click Add join below the Iterator or the + button in an existing join.
3. Define the join properties:
   • Iterator: iterator name of the related Record
   • Record type: the Record type of the joined Record
   • Join type: the type of join

     • full: all Records that meet the conditions

       The query will perform a cross join: it collects the Records and the joined Records, combines each Record with each joined Record, and applies the query condition on the results.

     • inner: the query will return only the entities, which have values in all shared records (if any shared record entry is missing, the resulting entity is not returned)
     • outer: the query will return any resulting entities with at least one missing value in any of the shared Records

       Note: Inner and outer joins are left joins.

   • Condition (for path expression): condition applied on the join table entities

     The condition is useful when the join is an outer join, since it is checked on a smaller set of entities as opposed to being checked on all entities when defined as the query condition. Under these circumstances, the condition can improve performance.

   • Path expression: path from the iterator Record to the Record for the join

     It must return a single instance of its type, or a list or set of instances of such a type.

     The path expression must start with one of the iterators, either a join iterator or a shared Record iterator.

Ordering in Standard Queries

To order the entities returned by a standard query, define the list of record properties used for ordering of the result entries: the entities are then ordered according to the values of the first property; if records contain the same value in the property, the second property is used for ordering, etc.

Note: Mind that the ordering is governed primarily by your database collation setting. Hence if you require change of ordering, such as, change of ordering characters with diacritics, change your database settings. The database of the embedded server uses utf8 character set and collation.

You can define ordering as dynamic or static:

• Dynamic ordering is based on an expression that is evaluated on runtime.
• Static ordering is based on a list of ordering paths.

If you define both the static and dynamic ordering, the static ordering takes precedence over the dynamic ordering: if you define static ordering of book records according to their author and dynamic ordering according to their title, the results will be ordered primarily based on their author and only on the next level ordered according to their title.

Defining Dynamic Ordering

Dynamic ordering defines an ordering expression which returns a list of order-enumeration values. It is evaluated for every query call.

The database query can return the records in a different order on different calls; for example, the ordering expression can use the query parameters, where the incoming parameter holds a list of ordering enumerations.

To define your query to return results ordered based on runtime data, do the following:

1. Create a query or open an existing query.
2. Expand Dynamic Ordering.
3. Optionally, define ordering enumeration with their ordering direction (you can then use the enumeration in the ordering expression):
   1. In the Ordering enumeration name, define the name of the ordering enumeration.
   2. In the table below, define the values of the ordering enumeration. Every enumeration value defines the following:
      • Name: name of the ordering value
      • Path: path to the record field that is used for ordering The path is defined as a path to the record field using the dot operator, that is, <ITERATOR_NAME>.<FIELD_NAME>, for example, book.author. Every path must define its sort order as either ASC to sort the records in the ascending order or DESC to use the descending order.
      • Nulls ordering: the way the null values are ordered (default: as set in the database setting; nulls first: null values come before any other values; nulls last: null values come after any other values)
4. Define the Ordering Expression.

The expression can use the incoming query parameters, where the incoming parameter holds a list of ordering enumerations and must return a list of ordering enumerations.

Example ordering expression

//query parameter of List<Ordering_enumeration>:
queryParameter
//possible literal value:
//[OrderEnum.AssetCurASC]

Dynamic ordering in a query

Defining Static Ordering

Static ordering of query output relies on a list of ordering paths: each query call uses the same paths for ordering.

To define static ordering of your query, do the following:

1. Create a query or open an existing query.
2. Expand the Static Ordering item.
3. In the Path table, define the ordering paths in the order you want to have them applied.

The ordering path must define the following:

• Path to the respective record field of the iterator in the form <ITERATOR_NAME>.<FIELD_NAME>, for example, assetIterator.owner.email
• Sort order as either ASC to sort in the ascending order or DESC to use the descending order according to the path field
• Nulls ordering: the way the null values are ordered (default: as set in the database setting; nulls first: null values come before any other values; nulls last: null values come after any other values)

Static ordering query

Generating Queries for Shared Records

The mechanism for generating queries creates standard queries for shared Records of the Module. Note that to get all shared records, you can use the findAll(<RECORD_TYPE>) function of the Standard Library.

For every shared Record, you can generate queries that return the following:

• all entries of the shared Record

  The queries are generated as findAll<RECORD_NAME>() queries.

• entries of the shared record with a particular ID

  The queries are generated as find<RECORD_NAME>ById(ID) queries. The ID Parameter has the data type of the primary key of the shared Record.

Note, that you can re-configure the name format on generation.

To generate the definitions of such queries perform the following steps:

1. Select the module containing the shared records.
2. Right-click and select Generate > Queries.
   

   Generating queries
3. Select which queries you want to generate. To re-configure the default prefixes and suffixes of generated queries, press the Configure button.
4. Click Finish.

HQL Queries

HQL queries are defined in the Hibernate's HQL language extension for LSPS-related features and are intended for cases where standard queries do not suffice, for example, when you want to group or subselect in a query.

Note: The underlying Hibernate is in version 4.2.19

HQL queries have the following restrictions:

• A shared record or field is accessed as in the Expression Language, that is, with its name and the dot operator to navigate.
• The query must return one or multiple shared records.
• To access properties, the query must always use an HQL alias.
• The query must contain the select clause;

An HQL query defines the following properties:

• Name: name used to call the query
• Result type: primitive data type, such as a String, or a non-shared record of the return value
• Single value: whether the query is required to return a single value (If selected and the call returns multiple values, it fails.)
• Public: availability of the query in importing Modules
• Parameter: query parameters
• Query: expression resolved to a String with the HQL query
• Start index: index of the first entity returned in a result
• Result size: size of the batch request
• Generate count query: if checked, the system generates a function that takes the same parameters as the query and returns the number of entities in the result set. The count value is not influenced by the start index and result size properties. Note that HQL count queries do not support queries with group by; for example, an HQL count query for the query select author from Author as author left join author.books group by author will fail: To create such count queries, create them as separate HQL queries.

Important: Single-to-single relationships (1:1) translate to Hibernate as 1:N relationships. If your HQL query is using such a relationship make sure you use the correct syntax. For example if you have a named 1:1 relationship toB from record A to B, using A.toB in such queries as select a from A a where a.toB.id = 2 will fail. Use an appropriate join to resolve the problem, for example, select a from A a inner join a.toB owner where owner.id = 2.

Here are a few HQL query string examples:

• get all records of type Author:

  "select author from Author author"

• get all records with a field matching a parameter:

  "select
     author from Author as author
     where author.id = :id"

• get all records with a value in a related record:

  "select b
     from Book b
     join b.authors as a
     where a.surname = :authorSurname"

• get records with a field that is empty:

  "select book from Book book where book.quote != null"

• get records with a field that contains a string:

  "select author from Author as author
    where authorBook.title like concat('%', :filter, '%')"

• get records with a value in a related record:

  "select author from Author as author
    left join author.books as authorBook
    where authorBook.title like :bookParam"

• get addresses of authors:

  //note the property path in select:
  "select author.address from Author author"

• get subrecords:

  //The record Communication has the subrecord Comment.
  //select all Communications, which are of the soc::Comment type:
  select c from Communication c
  where c.class = 'soc::Comment'

Native Queries

A native query is defined as a SQL database query. Note that, unlike standard queries, native queries do not rely upon shared records to query the underlying database. This allows you to make use of native database features and possibly secure better performance.

A native query is called from an expression in the same way as a function. When called, the query requests entities based on the defined query string. The results are stored in the defined Row type. If the query returns only the first entity, the entity is returned as an object of the row data type. If the query returns multiple entities, they are returned as a list of the row data type.

A native query defines the following properties:

• Name: name used to call the query

• Result type: type of the return value

  The type can be a primitive data type, such as a String, or a non-shared Record.

• Single value: amount of the returned values

  If true only the first returned value is provided as output; if false, all values are returned as a List of the return type. Note that these might be subject to paging.

• Public: availability of the query in importing Modules
• Database: JNDI name of the target database (if not defined, the default database is used)
• Parameter: query parameters

  Note: Parameter names must be valid identifiers unique within the query.

• Mapping: mapping of the fields of the Row type; the order of the fields defines the mapping to the returned entity values.
• Query: expression resolved to a String that contains the SQL query

  Example: Mapping and query

  • Mapping: Currency, Price, ISIN
  • Query:"SELECT CURRENCY, PRICE, ASSET_ISIN FROM ASSET WHERE CURRENCY=:curr”

  The CURRENCY will be mapped to Currency, PRICE to Price, and ASSET_ISIN to ISIN of the row type. Note that the order of the defined fields is preserved.