Dynamic Queries

The term dynamic queries refer to the database queries that are constructed dynamically at the run time. In CL Products, dynamic queries are used for computations, like debt to income ratio, insurance premium, credit exposure, and so on that need records to be fetched from various objects at the run time. This gives users the flexibility to select any set of objects to query on and to construct the query at the run time.

To implement a dynamic query in CL Product, do the following:

Define a query
Generate SOQL string
Call dynamic query
Execute query

Define a dynamic query

This section describes the following:

Concept: the concept of configuring a dynamic query in CL Products, taking Q2 Origination as an example.
Steps: the steps to define a dynamic query taking an object from Q2 Origination as an example.

Concept of configuring dynamic queries

An SOQL query is constructed in the following format. It consists of parameters, like object name, field names, and WHERE clauses. To define a dynamic query the values of these parameters are not provided in the query but need to be derived.

SELECT <field name1>, <field name2>,... FROM <object name> WHERE <condition1> OR/AND <condition1>

The following table explains how the values of SOQL query parameters are derived.

Query parameter	Derived from	Example
Field name	The value of the Field Name or the Alias column for which the Filter Type is Select_field.	Highlighted in the Multiple Select Fields image.
	There can be multiple Select fields.	IncomeName, IncomeType, IncomeDate as highlighted in the Multiple Select Fields image.
	The select field can also be an Aggregate function.	Sum of the Monthly Income field, as highlighted in the Select Field as an aggregate function image.
Object name	The value of the From Object field on the query details page.	Income object as highlighted in the From Object Name image.
WHERE clause	The value of the Where Clause field. The Where clauses can be nested. It means that the main clause consists of sub Where clauses and the evaluation of the main clause depends on the evaluation of the sub or the dependent clauses.	Clause-32060 is the main Where clause as highlighted in the From Object Name image. Note: To understand the structure of the Clause-32060, refer to Where clause structure. Note: To understand the evaluation of the Clause-32060, refer to Where clause evaluation.

Select fields examples

From object and Where clause

Where clause structure

The following chart displays the structure of the WHERE Clause-32060 used in QUERY-6131.

Where clause evaluation

The following image describes the parameters and their details that make Clause-32060.

The following table describes how each parameter is evaluated.

Parameter id	Parameter name	Parameter type	Evaluation
Parameter-15033	MonthlyIncomeAppReference	Context_Value	If the parameter is of the type Context_Value, the values are fetched at the run time. In this example, the value of the field ID is fetched from the records provided at the run time.
Parameter-15034	IncomeFrequency	Constant	If the parameter is of the type Constant, the value is constant. In this example, it is Monthly.
Parameter-15036	Date	DT(constant)	If the parameter is of the type DT, the value is a constant. The permissible format is YYYY-MM-DD. In this example, it is 2018-08-21.
Parameter-15037	Function	FN	If the parameter name is of the type FN, you can implement a custom class in it using the interface class named IAction. In this example, the custom class genesis.DynamicQueryFNImpl is implemented.
Parameter-17139	MonthlyIncomeContactRef	Context_Value	If the parameter is of the type Context_Value, the values are fetched at the run time. In this example, the value of the field Contact is fetched from the records provided at the run time.

Clause-32060 is evaluated as:

((Evaluation of Parameter-17139) AND (evaluation of Parameter-15033) AND (evaluation of Parameter-15034)) OR ((evaluation of Parameter-15036) AND (evaluation of Parameter-15037))

Steps to define a dynamic query

Prerequisites

The WHERE clauses and the parameters needed to construct a query must be defined.

Steps

To define a dynamic query, do the following:

Log in to your Salesforce account.
On the Dynamic Queries tab, select New.
The New Dynamic Query page appears.
Specify the query details as described in the Configuring dynamic query section of the Fields reference table.
Select in the Where Clause box, then select New Dynamic Query Data.
On the Dynamic Query Data page, specify the Where clause details as described in the Dynamic query data section of the Fields reference table.
Select Save.
In the Dynamic Query Parameter Edit section, specify the parameter details as described in the Dynamic query parameter edit fields section of the Fields reference table.
Select Save.
After a query is defined, select Validate Query to run it.

Fields reference table

The following table describes the fields needed to be specified while constructing a dynamic query.

Field	Description
Configuring dynamic query
Dynamic Query Id	An auto-generated identifier of a query.
Query Name	The name identifier of a query provided while defining it.
From Object	The Salesforce object API name, from where the records are to be fetched.
Where Clause	The where clause for the query. Records satisfying this condition are fetched. Select in the Where Clause text box to define the parameters.
Group By	Is used to group or summarize the query results.
Order By	Is used to sort the query results.
Record Limit	The number of records to be queried.
Is Query JSON provided?	If this checkbox is selected, the query JSON is provided. If so, then it is directly used while executing a query. Otherwise, query JSON is constructed first.
Start With	It specifies the rows that are the roots and appear at the top of the data tree in hierarchical queries. Note: Hierarchical queries are used to traverse data organized in a parent-child relationship.
Connect By	Used in hierarchical queries. It specifies the relationship between parent rows and child rows of the hierarchy.
Query JSON	JSON format of the query. It is populated by selecting the Validate Query button.
Dynamic query data edit
Parent Query	It is the parent dynamic query. Note: Prior to the Winter'22 release, the Parent Query field was not displaying the value irrespective of whether it has a value. But with the Winter'22 release, Q2 Origination is enhanced to display the value as highlighted in the following image:
Alias	An alias for the selected field or aggregate function.
Filter Type	Is the type of record. It can be either of the following: Select Field: the field that needs to be selected in the query. Expression: fields or records that are used as a part of the here Clause/Start With/Connect By.
Aggregate Function	The aggregate function to be used. For example, Sum.
Field Name	API name of the selected field.
Value	Is the parameter record reference.
Expression Left	Is the left side of an expression when joining two dynamic query data records.
Operator	The operator used in the expression.
Expression Right	Is the right side of an expression when joining two dynamic query data records.
Field Data type	The data type of the selected field or the aggregate function.
Dynamic query parameter edit fields
Dynamic Query Parameter Id	The name identifier for a parameter.
Parent Query	The parent dynamic query record.
Type and Value	The following list describes different types of parameters and the values that they can have. Constant: represents a constant value. Like, Borrower is a constant value in the following query: Select Account_c from Party_C where Party_Type_C= Borrower FN: represents a custom class name. DT: represents date, time, or datetime values. Permissible format is Context_Value: represents fields for which the values are fetched from the input map of the dynamic query at the run time. Sub_Query: is a query defined inside another query. When a query is run, the subquery defined inside it is run first. In Salesforce, subqueries can be used on the id fields only. In the following example, the subquery is highlighted in yellow. So, when the main query is run, first the contacts with, Id = partyId and party type = Borrower or Co-Borrower or Cosigner are fetched (as defined in the subquery).
Parameter Query Data	Is the selected field from an existing query.

Generate SOQL string

String query = results.get(‘query name’);

In this example, the following SOQL query is created.

SELECT Name, genesis__Income_Type__c, genesis__Credit_Date__c FROM genesis__Income__c WHERE ( ( genesis__Contact__c IN (SELECT clcommon__Contact__c FROM clcommon__Party__c WHERE genesis__Application__c = :ID AND clcommon__Type__r.Name IN ('BORROWER' , 'CO-BORROWER', 'COSIGNER') ) AND ( genesis__Application__c = :ID AND genesis__Frequency__c = 'Monthly' ) ) OR ( genesis__Credit_Date__c= != '2018-08-21' AND genesis__Taxable__c IN :Function ) )

Call dynamic query

Set<String> queryNames = new Set<String>();

queryNames.add(‘ApplicationMonthlyIncomeQueryExample’);

QueryProcessor queryProcessor = new QueryProcessor();

Map<String,String> results = queryProcessor.getQuery(queryNames, application);

Execute a query

List<genesis__Income__c> resultRecords = (List<genesis__Income__c>)Database.query(query);