Search Library

This page explains query or search operations through a dynamic logic in Oracle Health Insurance. It mentions the different operations to perform unique searches.

Be careful with dynamic logic searches that return a high number of rows (more than 50).

A search operation retrieves data from a database. The performance of the returned data depends on the number of rows and the available access paths (complex with indexes).
The data retrieved from the database is loaded into the memory. Each object may be associated with other objects that also load into memory. Therefore, it is strongly recommended to avoid loading big sets and paginate the results instead. The pagination subsection within this page describes how to do that. Furthermore, try to reduce the number of retrieved objects through added conditions on the search query. This avoids the need to filter out unneeded objects in groovy later.

Model

The search library uses a search builder. The builder operates through the SearchBuilder object to build up the search and perform the search operation using a builder pattern. The object returns the filtered objects from the underlying data store.

The Search Builder

The search builder has the following aspects:

Searched Object

The searched object specifies the object to search. Following is an example that searches an object:

SearchBuilder searchBuilder = new SearchBuilder(Claim.class)

The Claim.class argument refers to the object that is being queried.

Search Expression

A search expression is the basic building block of a search. It replicates a SQL WHERE statement that searches an object with code='A' and name like %john%. A search requires one or more search expressions. A search expression has a syntax like attribute.operator(operand).

Attribute

An attribute defines the direct or joined attribute of an object to perform the search. Following are examples to search within direct or joined attributes:

Search a claim for a matching claim code

List<Claim> claims = new SearchBuilder(Claim.class).by("code").eq("CLAIMCODE").execute()

Search a claim for a claim line code
```
List<Claim> claims = new SearchBuilder(Claim.class)
  .join("claimLineList")
  .by("code").eq("CLAIMLINECODE")
  .execute()
```
The search builder uses a join operation to connect the claimLineList and the Claim objects. The search expression is then built on the joined claim line.

Search a claim for a payer code, also a brand on the claim

List<Claim> claims = new SearchBuilder(Claim.class)
  .join("brand")
  .join("payer")
  .by("code").eq("PAYERCODE")
  .execute()

Operator

Following is a list of available operators for a search expression within a search builder:

Table 1. Operator
Operator	Description	Example
`eq`	Checks for matching values. Checks for null values for an empty argument.	`by("name").eq("king")`
`eqIgnoreCase`	Checks for matching values and ignores the case.	`by("name").eqIgnoreCase("king")`
`neq`	Checks for unmatched values. Checks for non-empty fields for an empty argument.	`by("name").neq("john")`
`lt`	Checks for values less than the specified argument.	by("startDate").lt("2015-11-11")
`lte`	Checks for a value less than or equal to the specified argument.	by("length").lte(30)
` gt`	Checks for values greater than the specified argument.	by("length").gt(30)
` gte`	Checks for values greater than or equal to the specified argument.	by("startDate").gte("2015-11-11")
`in`	Checks for values that match the list in the passed argument.	by("code").in("AB","CD","XY")
`between`	Checks for a value within the range of the passed arguments.	by("age").between(6,10)
`like`	Check if the values that contain the string passed as argument. Works like the like operation and supports the `%`and `_ as wildcards. A second argument is a backslash (\) character to indicate presence of a backlash in the search expression and that the search must look for values with the wildcard character placed before the backslash.	`by("name").like("J%")` searches for that start with the letter J. `by("name").like("b_g")` searches for values with big and bug. `by("name").like("G_/_%","/")` searches for names that start with \\G and returns results like \\G_123 and \\G_1234.
`likeIgnoreCase`	Works same as the `like` operation but ignores the case.	by("name").likeIgnoreCase("J%25") ` `by("name").likeic("g/_%","/")
`notIn`	Checks for values that are not in the list passed in the argument.	`by("code").notIn("AB","CD","XY")`

Joining Search Expressions

This aspect of the search builder joins search expressions. Following are the operations that join search expressions:

The and Operation

SearchExpression1.and().SearchExpression2

The following example searches a claim for a specified brand code and a specified start date:

List<Claim> claims = new SearchBuilder(Claim.class)
  .join("brand")
  .by("code").eq("BRANDCODE")
  .and()
  .by("startDate").gte("2015-01-01")
  .execute()

Multiple join statements do not result in multiple queries to the database. The search builder combines the join statements to prevent multiple database join operations on the same table. The following example searches a countryRegion object and uses two join statements on the country table:

def countryRegion = new SearchBuilder(CountryRegion.class)
.join("country").by("active").eq("true")
.and()
.join("country").by("code").eq("CODE")
.execute()

Here, the search builder combines the join statements on two attributes - code and active - and prevents join of two tables in the database. This way, multiple joins on the search builder do not impact the performance of the application.

The or Operation

SearchExpression1.or().SearchExpression2

The following example searches a claim on either a brand code or a start date:

List<Claim> claims = new SearchBuilder(Claim.class)
  .join("brand")
  .by("code").eq("BRANDCODE")
  .or()
  .by("startDate").gte("2015-01-01")
  .execute()

Searching Reference Sheets

Consider a reference sheet refSheetA using a dynamic record definition with two fields, field1 and field2, as definition.

To search the reference sheet lines based on a single criteria, in this case searching for lines with field1 having the value A, use the following dynamic logic:

def search = new SearchBuilder(ReferenceSheetLine.class)
.join("refSheetA").by("field1").eq("A")
.execute()

To search the reference sheet lines based on multiple criteria, in this case searching for lines with field1 having the value A and field2 having value B, use the following dynamic logic.
Note that you must use .join() for every criteria/search-field.

def search = new SearchBuilder(ReferenceSheetLine.class)
.join("refSheetA").by("field1").eq("A")
.and()
.join("refSheetA").by("field2").eq("B")
.execute()

Grouping Search Expressions

The search builder has the group and end operations to group different search expressions together. The group operations inform the search builder about the start of grouped expressions and the end operation denotes end of a group. It is also possible to have nested groups.

The following example searches a claim on a start date greater than the specified date and an empty end date, or an end date less than a specified date:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .and()
  .group()
  .by("endDate").lte("2017-01-01").or().by("endDate").eq(null)
  .end().execute()

Sub-Query Expressions

The search builder searches with a subquery using the inSubQuery and notInSubQuery operations.

Example 1a: The following example searches for a claim with ABC in its message within a claim message list:

List<Claim> claims = new SearchBuilder(Claim.class)
  .join("claimMessageList").join("message").by("id")
  .inSubQuery().select().("id")
  .from(Message.class)
  .by("code").eq("ABC")
  .subQueryEnd().execute()

Example 2: The following example searches for a claim with ABC in a message within a claim message list:

List<Claim> claims = new SearchBuilder(Claim.class)
  .join("claimMessageList").join("message").by("id")
  .notInSubQuery().select().("id")
  .from(Message.class)
  .by("code").eq("ABC")
  .subQueryEnd().execute()

Following are the subparts of the inSubQuery and notInSubQuery operations:

inSubQuery()/ notInSubQuery(): Informs the builder when a query (in or not in) is started.
subQueryEnd(): Informs the builder when a subquery has ended.
select(): Informs the builder of the scope of the select operation. In the example, it is the id on the message. This operation also takes a join operation as an argument.
from(<CLASS>): Informs the builder about the table name to perform the search as part of the subquery.
Next comes the search expressions for the select operation within the subquery.

Exists Query Expressions

The search builder searches for a keyword using the existsQuery and notExistsQuery operations.

Example 1a: The following example searches a claim with the XYZ brand and ends an access restriction ACRE for that brand:

List<Claim> claims = new SearchBuilder(Claim.class)
  .join("brand").("code").eq("XYZ").and()
  .existsQuery().select(true)
  .from(Brand.class)
  .by("code").eq("XYZ").and()
  .join("accessRestriction").by("code").eq("ACRE")
  .existsQueryEnd().execute()

Example 2: The following example searches a claim with the brand XYZ and retains the access restriction ACRE for that brand:

List<Claim> claims = new SearchBuilder(Claim.class)
  .join("brand").("code").eq("XYZ").and()
  .notExistsQuery().select(true)
  .from(Brand.class)
  .by("code").eq("XYZ").and()
  .join("accessRestriction").by("code").eq("ACRE")
  .existsQueryEnd().execute()

Following are the subparts of the existsQuery and notExistsQuery operations:

existsQuery()/notExistsQuery(): Suggests start of an exists query (in or not in) to the builder.
existsQueryEnd(): Suggests end of an exists query to the builder.
select(boolean): Suggests to the builder that the select operation is part of the exists query. A true selects id and a false selects 1.
from(<CLASS>): Suggests a table to the builder for the existsQuery operation.
Search expression: The expression to select fields using the existsQuery operation.

Sorting

The search builder uses orderBy(<ATTR>) to sort the queried objects. The search can be on a single or a joined attribute.

The following example orders the result on a direct attribute (claim):

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("code").execute()

The following examples searches on a joined attribute:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code").execute()

The following example returns null results first:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code NULLS FIRST").execute()

The following example returns null results last:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code NULLS LAST").execute()

The following example sorts the results in an ascending order, which is default for every search:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code").ascending().execute()

The following example sorts the results in a descending order:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code").descending().execute()

The following example sorts the results by attribute with outer join:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList(+).code").descending().execute()

Pagination

The search builder has setPage() and setPageSize() operations to paginate the results.

Example 1

The following example searches for five claims on start date and orders the results by code:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("code").setPageSize(5).execute()

Example 2

The following example searches the next five claims (six to ten) by start date and orders the results by their code:

List<Claim> claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("code")
  .setPageSize(5).setPage(2).execute()

Example 3

The following example performs a read-only search on claims based on their start date and orders the results on their claim’s code. The results display five claims at a time until there are none left:

SearchBuilder searchBuilder = new SearchBuilder(Claim.class)
for (int i = 1; i <= 10; i++ ) {
    claims = searchBuilder.by("startDate").gte("2015-01-01")
        .orderBy("code")
        .setReadOnly(true)
        .setPageSize(5).setPage(i).execute()

    if (claims.isEmpty()) {
        break
    }

    // do something with these claims
}

Pagination reduces memory footprint only when the setReadOnly(true) operator is set. This makes the search builder read attributes and make it so that it cannot manipulates the queried object. This is because changes made to an object stay in the memory until the dynamic logic completes, by that point all changes commit to the database.

Executing the Search

The execute() operation is added after providing all the aspects to the search builder, depending on the requirement. It performs the search operation and returns the results. It returns a list of objects that match the search criteria.