Fluent Queries

Fluent queries allow you to run database queries in your server automation scripts using an SQL-like fluent interface. Intelligent code completion is available in the code editor both for the query inputs and for the result sets.

IMPORTANT!  
  • Fluent queries can only be used in administrative context. See the Server SDK Reference Guide for information on how to temporarily change the transaction context.
  • Result sets include only the columns specified in the select statements. Lookup fields are not automatically expanded.
  • Data ownership is per organization.
  • Date and datetime values are not returned as strings. The wrapper for datetime values (JsDateTime) stores the date as UTC and provides utility methods for data manipulation.

Execute a Fluent Query

To execute a fluent query in a server automation script:

  1. Use the server.query.getAlias method to define an entity alias.
  2. Use the server.query.from method to run the query on the desired entity.
  3. Use the .selectColumns method to select the returned attributes.
  4. Use the .distinct() method to return only distinct values in the result set (no duplicates).
  5. Use the .execute() method to run the query.
Copy
var A = server.query.getAlias('Account');

var myFluentQuery = server.query.from('Account', A)
    .selectColumns (A.Name, A.Email)
    .distinct()
    .execute();

log(myFluentQuery)

The code above will log an output similar to the following in the trace_roll.log file:

Copy
-[START]---------------------------------------
Timestamp: 7/1/2020 5:01:51 PM
 Message: INFO [CID=200524bb-8d28-4079-80c3-a003a9ecad7d] [
    {
        "values": {
            "A_Name": "Jane Doe",
            "A_Email": "janedoe@fintechos.com"
        }
    },
    {
        "values": {
            "A_Name": "Andrew Jones",
            "A_Email": "adrew.jones@fintechos.com"
        }
    },
    {
        "values": {
            "A_Name": "John Doe",
            "A_Email": "john.doe@fintechos.com"
        }
    }
]
 Severity: Information
-[END]---------------------------------------

The entity alias definition enables intelligent code completion for the corresponding entity attributes:

Comparison Operators

The following comparison operators are supported in fluent queries: equals (eq), notEquals (neq), greaterThan (gt), greaterThanOrEquals (gt), lessThan (lt), and lessThanOrEquals (lte)

Logical Operators

The following logical operators are supported in fluent queries: and, or, andNot, and orNot.

Inner Joins

Use the .innerJoin and .on methods to define inner join clauses for the queried entities.

Copy
var O = server.query.getAlias('optionset');
var A = server.query.getAlias('attribute');


var myFluentQuery = server.query.from('attribute', A)
    .innerJoin('optionset', O)
        .on(A.OptionSetId.eq(O.OptionSetId))
    .top(5)
    .orderBy(A.Name)
    .execute()

Left Joins

Use the .leftJoin and .on methods to define left outer join clauses for the queried entities.

Copy
var O = server.query.getAlias('optionset');
var A = server.query.getAlias('attribute');

var myFluentQuery = server.query.from('attribute', A)
    .leftJoin('optionset', O)
        .on(A.OptionSetId.eq(O.OptionSetId))
    .top(5)
    .selectColumns(A.Name, O.DisplayName)
    .orderBy(A.Name)
    .execute();

Attribute Aliases (Projections)

To define aliases for the queried attributes, use the .selectProjection method instead of .selectColumns and the .executeAndMap method instead of .execute.

Projections are useful to customize the result set field names when different entities have similar attribute names or when attribute names are not expressive (see Work with Fluent Query Result Sets for more details about fluent query outputs).

Where Clauses

Where clauses are implemented using the .where, .wherenot, .andWhere, .orWhere, .andWhereNot, and .orWhereNot methods.

Aggregate Functions

To define aggregate functions on a set of values from the result set, use the .getCountAlias, .getSumAlias, .getMaxAlias, and .getMinAlias methods of the server.query property.

Work with Fluent Query Result Sets

Use the .field() method to get field values from a row in the result set.

Copy
var E = server.query.getAlias('entity');

var rows = server.query.from('entity', E)
    .top(5)
    .execute();

let output = '\n\nThe following entities were found:\n';
            
rows.map(function(r)
{
    output += (' ' + r.field(E.Name) + '\n')
});
            
log(output);

The code above will log an output similar to the following in the trace_roll.log file:

Copy
-[START]---------------------------------------
Timestamp: 7/2/2020 1:15:34 PM
 Message: INFO [CID=200524bb-8d28-4079-80c3-a003a9ecad7d] 

The following entities were found:
 entityBWTransitionActionGroup
 SystemUserSecurityRole
 BW
 optionset
 lookupCorrelationAttribute

 Severity: Information
-[END]---------------------------------------

Map Result Sets to POCO Objects

Use the .executeAndMap() method to map a fluent query result set to an entity alias. This creates a plain old CLR object (POCO) which allows you to access field values as object properties, instead of using the .field() method.

Copy
var E = server.query.getAlias('entity');

var rows = server.query.from('entity', E)
    .top(5)
    .executeAndMap(E);

let output = '\n\nThe following entities were found:\n';
            
rows.map(function(r)
{
    output += (' ' + r.Name) + '\n')
});
            
log(output);

To map a fluent query result set to multiple entity aliases, use the .executeAndMapComplex() method.

Copy
var E = server.query.getAlias('entity');
var A = server.query.getAlias('attribute');

var rows = server.query.from('entity', E)
    .innerJoin('attribute', A)
        .on(E.EntityId.eq(A.EntityId))
    .top(20)
    .selectColumns(
        E.Name,
        A.Name)
    .executeAndMapComplex({ entity : E,  attribute : A});

rows.map(function(r)
{
    var entityName = r.entity.Name;
    var attrName   = r.attribute.Name;
            
    // do
            
});

Fluent Query Caching

To improve query performance, you can cache the results of specific queries on their first run. Thus, on subsequent runs, the query results will be retrieved directly from the memory cache instead of accessing the database again.

NOTE  
Cached results are stored in the memory cache for 30 minutes.

To set or retrieve a cached result, you must assign a unique cache key to your query. Then, when you execute the query:

  • If the cache key is not found in the memory cache (on the first execution), the database is queried and the result set is saved in the memory cache with the corresponding cache key.
  • If the cache key is found in the memory cache (on subsequent executions within 30 minutes from the first execution), the result set is retrieved directly from the memory cache.

Assign a Cache Key to a Fluent Query

You can assign a cache key to a query with either the .cacheAs or the .cacheScopedAs methods:

  • .cacheAs will cache the result set at the global scope.
  • .cacheScopedAs will cache the result set at the server automation script's scope.

For example:

Copy
var acc = server.query.getAlias('Account');
var phoneId = '07111111';

var fq = server.query.from(acc)
    .where(acc.Phone.eq(phoneId))
    .cacheAs(phoneId);  //Cache the result of the fq query with the phoneID cache key.
    
    //To cache the result at the server automation script's scope use:
    //.cacheScopedAs(phoneId);

//Will be retrieved from the database.
var result1 = fq.executeAndMap(acc);

//Will be retrieved from the memory cache.
var result2 = fq.executeAndMap(acc);
 
IMPORTANT!  
Once a query is cached (on the first run), all subsequent runs will retrieve the cached result set, until the cache expires (in 30 minutes). You can however refresh the cache by providing a different cache key as input to the .cacheAs or .cacheScopedAs method.
IMPORTANT!  
.cacheAs and .cacheScopedAs set the same cache key on the query and will overwrite each other if you use both of them.
HINT  
The global scope of the .cacheAs method allows a server automation script to retrieve result sets cached by previous runs of the same script.