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.

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.

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:

-[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.

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.

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).

Example: Return customized field names in a fluent query result set

In this example, we return information about 5 random entity-attribute pairs.

Copy

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

var P; //projection alias

var rows = server.query.from('entity', E)
    .innerJoin('attribute', A)
        .on(E.EntityId.eq(A.EntityId))
    .top(5)
    .selectProjection( P = 
    {
        EntityName : E.Name,
        AttributeName : A.Name,
        Type : A.AttributeType
    })
    .executeAndMap(P);

log(rows)

The result set for the above fluent query will include the customized EntityName, AttributeName, and Type field names.

Copy

-[START]---------------------------------------
Timestamp: 7/2/2020 2:00:55 PM
 Message: INFO [CID=200524bb-8d28-4079-80c3-a003a9ecad7d] [
    {
        "EntityName": "Account",
        "AttributeName": "Phone",
        "Type": "0a39db15-7634-4af3-8bd8-004fcf27e8a6"
    },
    {
        "EntityName": "Account",
        "AttributeName": "Accountid",
        "Type": "ddce8347-794d-4a8d-b9d0-42437f653ae4"
    },
    {
        "EntityName": "Account",
        "AttributeName": "Email",
        "Type": "0a39db15-7634-4af3-8bd8-004fcf27e8a6"
    },
    {
        "EntityName": "Account",
        "AttributeName": "modifiedOn",
        "Type": "2e33740e-5026-43b5-919c-cc2d422c280f"
    },
    {
        "EntityName": "Account",
        "AttributeName": "Id2",
        "Type": "0a39db15-7634-4af3-8bd8-004fcf27e8a6"
    }
]
 Severity: Information
-[END]---------------------------------------

Where Clauses

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

Example: Fluent query with multiple where clauses

Copy

var F = server.query.getAlias('FinChartItemValue');

var rows = server.query.from('FinChartItemValue', F)
    .top(5)
    .where(F.Percent.gte(100).and(F.Percent.lte(200)))
    .orWhere(F.Percent.gte(300).and(F.Percent.lte(400)))
    .execute();

Example: Fluent query with comparison operators

Copy

var F = server.query.getAlias('FinChartItemValue');

var rows = server.query.from('FinChartItemValue', F)
    .top(5)
    .where(F.Percent.isNull)
    .execute();

//equivalent query

var rows = server.query.from('FinChartItemValue', F)
    .top(5)
    .where(F.Percent.equals(null))
    .execute();

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.

Example: Get attribute aggregates for 5 random entities

In this example, we get the number of attributes, maximum attribute length, minimum attribute length, and total length of all attributes for 5 random entities.

Copy

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

var Count = server.query.getCountAlias();
var SumLength = server.query.getSumAlias(A.Length);
var MaxLength = server.query.getMaxAlias(A.Length);
var MinLength = server.query.getMinAlias(A.Length);

var P; //projection alias

var rows = server.query.from('entity', E)
    .innerJoin('attribute', A)
        .on(E.EntityId.eq(A.EntityId))
    .top(5)
    .where(E.Name.startsWith('a'))
    .selectProjection( P = 
    {
        'Entity Name' : E.Name,
        'Number of attributes' : Count,
        'Total attributes\' lengths': SumLength,
        'Maximum attribute length' : MaxLength,
        'Minimum attribute length' : MinLength
    })
    .executeAndMap(P);

log(rows);

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

Copy

-[START]---------------------------------------
Timestamp: 7/2/2020 11:21:28 AM
 Message: INFO [CID=200524bb-8d28-4079-80c3-a003a9ecad7d] [
    {
        "Entity Name": "Account",
        "Number of attributes": 15,
        "Total attributes legth": 1638,
        "Maximum attribute length": 500,
        "Minimum attribute length": 64
    },
    {
        "Entity Name": "action",
        "Number of attributes": 9,
        "Total attributes legth": 3200,
        "Maximum attribute length": 3000,
        "Minimum attribute length": 0
    },
    {
        "Entity Name": "actiongroup",
        "Number of attributes": 6,
        "Total attributes legth": 200,
        "Maximum attribute length": 200,
        "Minimum attribute length": 0
    },
    {
        "Entity Name": "ActionXSecurityRole",
        "Number of attributes": 3,
        "Total attributes legth": null,
        "Maximum attribute length": null,
        "Minimum attribute length": null
    },
    {
        "Entity Name": "applicationLanguage",
        "Number of attributes": 11,
        "Total attributes legth": 213,
        "Maximum attribute length": 100,
        "Minimum attribute length": 1
    }
]
 Severity: Information
-[END]---------------------------------------

Work with Fluent Query Result Sets

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

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:

-[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.

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.

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 (v22.1.4 or later)

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.

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:

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);