ftos.data.getByQueryAndCache

IMPORTANT!  
Starting with v24.3.0, this is renamed from getByQueryAndCache to ftos.data.getByQueryAndCache.

Returns a fetch result set. If available, the result set is retrieved from cache. Otherwise, it is saved in cache.

To improve fetch performance, you can cache the results of a fetch on its first run. Thus, on subsequent runs, the fetch 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 fetch:

This is a data service method for business service components.

Syntax

Copy 
function getByQuery(fetch : IFtosFetch, cacheKey: string): any[];
 
Parameter Type Description
fetch IFtosFetch The code of the fetch query.
cacheKey string Cache key that identifies the cached result set.

Type Aliases

The code of the fetch query.

Copy 
{
    distinct : boolean,
    entity : IFtosFetchEntity,
    orderBy : IFtosFetchOrderBy[],
    page : IFtosFetchPage,
    where : IFtosFetchExpression,
    options : {disableAutoLookupJoins : boolean}
}
 
Property Type Description
distinct boolean Returns only distinct values, ignoring any duplicates.
entity IFtosFetchEntity Details about the entity and attributes to be fetched.
orderBy IFtosFetchOrderBy[] Orders the result set ascending or descending based on the specified attributes.
page IFtosFetchPage Returns only a specific sequence of results from the result set.
where IFtosFetchExpression Criteria used to filter the result set.
options {disableAutoLookupJoins: boolean} Disables the auto joins added automatically for lookups and option sets (needed to retrieve the display names):
  • true - auto joins disabled
  • false - auto joins enabled

Details about the entity and attributes to be fetched.

Copy 
{
    alias : string;
    attributeList : IFtosFetchAttribute[];
    join : IFtosFetchJoin[];
    name : string;
}
 
Property Type Description
alias string Gives the entity a temporary name for the duration of the fetch to be used in the fetch code.
attributeList IFtosFetchAttribute[] List of attributes to be fetched.
join IFtosFetchJoin[] Combines records from multiple entities based on matching attributes.
name string Entity name.

Orders the result set ascending or descending based on the specified attributes.

Copy 
{
    attribute : string;
    type : "asc" | "desc";
}
 
Property Type Description
attribute string Attribute name.
type "asc" | "desc" Orders results either ascending or descending.

Returns only a specific sequence of results from the result set.

Copy 
{
    returnCount : boolean;
    skip : number;
    take : number;
}
 
Property Type Description
returnCount boolean Include the number of returned records in the result set.
skip number Skips the specified number of records from the beginning of the result set.
take number Specifies the number of records to return.

Criteria used to filter the result set.

Copy 
{
    conditionList : IFtosFetchCondition[];
    expressionList : IFtosFetchExpression[];
    type : "and" | "or";
}
 
Property Type Description
conditionList IFtosFetchCondition[] Filters the returned results based on the specified criterion.
expressionList IFtosFetchExpression[] Sub-expression to be used as filtering criterion in the expression.
type "and" | "or"
  • and - All criteria must be met for a record to be returned.
  • or - Any criterion must be met for a record to be returned.

Attribute whose values will be returned in the result set.

Copy 
{
    alias : string;
    name : string;
}
 
Property Type Description
alias string Gives the attribute a temporary name for the duration of the fetch to be used in the fetch code.
name string Attribute name.

Combines records from multiple entities based on matching attributes.

Copy 
{
    entity : IFtosFetchEntity;
    fromTo : IFtosFromTo[];
    type : "inner" | "left";
}
 
Property Type Description
entity IFtosFetchEntity Entity on the right side of the join.
fromTo IFtosFromTo[] Pairs attribute values to be matched on the left side and right side of the join.
type "inner" | "left"
  • inner - Returns records that have matching attributes in both joined entities.
  • left - Returns all record values from the entity on the left of the join and only record values that have a matching attribute on the right of the join.

Filters the returned results based on the specified criterion.

Copy 
{
    first : string;
    second : string;
    type :
        "contains" |
        "notcontains" |
        "equals" |
        "notequals" |
        "gt" |
        "gte" |
        "lt" |
        "lte" |
        "isnotnull" |
        "isnull" |
        "lastxdays" |
        "lastxmonths" |
        "lastxyears" |
        "nextxdays" |
        "nextxmonths" |
        "nextxyears" |
        "lastxweeks" |
        "nextxweeks";
}
 
Property Type Description
first string First operator of the condition.
second string Second operator of the condition.
type "contains" | "notcontains" | "equals" | "notequals" | "gt" | "gte" | "lt" | "lte" | "isnotnull" | "isnull" | "lastxdays" | "lastxmonths" | "lastxyears" | "nextxdays" | "nextxmonths" | "nextxyears" | "lastxweeks" | "nextxweeks" Condition operand.
  • contains - Searches the second operator within the first operator. You can use the % wildcard as a placeholder for zero, one, or multiple characters if you wish to define a text pattern.
  • notcontains - Checks that the second operator is not part of the first operator. You can use the % wildcard as a placeholder for zero, one, or multiple characters if you wish to define a text pattern.
  • equals - The first operator is equal to the second operator.
  • notequals - The first operator is different from the second operator.
  • gt - The first operator is greater than the second operator.
  • gte - The first operator is greater than or equal to the second operator.
  • lt - The first operator is less than the second operator.
  • lte - The first operator is less than or equal to the second operator.
  • isnotnull - The operator is not null.
  • lastxdays - The first operator (date) is within a number of days (specified by the second operator) prior to the current date.
  • lastxweeks - The first operator (date) is within a number of weeks (specified by the second operator) prior to the current date.
  • lastxmonths - The first operator (date) is within a number of months (specified by the second operator) prior the current date.
  • lastxyears - The first operator (date) is within a number of years (specified by the second operator) prior to the current date.
  • nextxdays - The first operator (date) is within a number of days (specified by the second operator) after the current date.
  • nextxweeks - The first operator (date) is within a number of weeks (specified by the second operator) after the current date.
  • nextxmonths - The first operator (date) is within a number of months (specified by the second operator) after the current date.
  • nextxyears - The first operator (date) is within a number of years (specified by the second operator) after the current date.

Pairs attribute values to be matched in an entity join.

Copy 
{
    from : string;
    to : string;
}
 
Property Type Description
from string Attribute on the left side of the join.
to string Attribute on the right side of the join.

Return Value

Returns a JSON object that contains the fetch result set.

Examples

In this example:

  • We query the Account entity for each account name.
  • We assign the account cache key to the cached result set.
  • We save the result for the initial run (retrieved from the database) in the result1 variable.
  • We store the result for the second run (retrieved from the cache memory) in the result 2 variable.
Copy 
//will be retrieved from db
var result1 = ftos.data.getByQueryAndCache({
    "entity": {
        "alias": "base",
        "name": "Account",
        "attributelist": [{
            "name": "Name",
            "alias": "name"
        }]
    }
}, 'account');

//will be retrieved from MemoryCache
var result2 = ftos.data.getByQueryAndCache({
    "entity": {
        "alias": "base",
        "name": "Account",
        "attributelist": [{
            "name": "Name",
            "alias": "name"
        }]
    }
}, 'account');