Skip to content

Latest commit

 

History

History
571 lines (426 loc) · 16.6 KB

DynamicSOQL.md

File metadata and controls

571 lines (426 loc) · 16.6 KB

Table of content

Intro

Important

The DynamicSOQL module depends on the DatabaseUtils module.

Enter DynamicSOQL, a powerful and flexible library that enables you to retrieve data from Salesforce using Apex Code and JavaScript. This library provides a unique, object-oriented approach to building SOQL queries, making it easier to build and manage complex queries.

DynamicSOQL is built using Apex code and a JavaScript mirror, and it provides a number of features that make it a powerful and versatile solution for working with SOQL. Some of the key features of DynamicSOQL include:

  • Apec API: DynamicSOQL provides an easy-to-use API that is fully object-oriented. This makes it easy to build and manage complex queries, as you can use objects to represent the various parts of your query.

  • JavaScript API: DynamicSOQL includes a JavaScript API that allows you to build the JSON representation of your query and then send it to the backend to process. This means you can build your query on the frontend and then process it on the backend, providing a seamless and efficient data retrieval solution.

  • Granular Control: DynamicSOQL enables you to build your query granularly, meaning you can prepare different parts of the query separately. For example, you can use the DynamicSOQLConditionBlock and DynamicSOQLCondition objects to prepare the JSON representation of your filters, and then build the WHERE clause on the backend.

  • Security: DynamicSOQL includes security measures to prevent SOQL injection on the backend. This ensures that your queries are secure and protects your data from malicious attacks.

  • Functionality: DynamicSOQL provides a wide range of functionality, including the ability to build simple queries, aggregate queries, subqueries, and more.

The library is built in Object Oriented style and contains the next classes:

  • DynamicSOQL - the main class that represents SOQL.
  • DynamicSOQLCondition - represents the part of 'WHERE' clause in a base format like <FieldName> <operator> <value>'.
  • DynamicSOQLConditionBlock - used to combine DynamicSOQLCondition or nested DynamicSOQLConditionBlock and allows to build logical bloks with OR | AND keywords.
  • DynamicSOQLFunction - represent SOQL Functions, for example: COUNT(Id) alias.
  • DynamicSOQLGroupBy - represent GROUP BY clause and allows to build Aggregated Query.
  • DynamicSOQLOrderBy - represent ORDER BY clause.

Quick Start

Build a regular query

DynamicSOQL soql = new DynamicSOQL('Account')
.withField('Id')
.withField('Name')
.withConditions(
    new DynamicSOQLConditionBlock('AND')
    .addCondition(new DynamicSOQLCondition('Name', '=', 'Some Account Name'))
)
.withOrderBy(new DynamicSOQLOrderBy(new List<String>{'Name', 'Id'}))
.withSubQuery(
    'Contacts',
    new DynamicSOQL('Contact')
    .withField('FirstName')
    .withField('Email')
    .withConditions(
        new DynamicSOQLConditionBlock('AND')
        .addCondition(new DynamicSOQLCondition('Email', '!=', (String)null))
    )
);

System.debug(soql.stringify());
/* The output (line breaks was added manually):
    SELECT Id,Name,(
        SELECT FirstName,Email
        FROM Contacts
        WHERE (Email != null)
    )
    FROM Account
    WHERE (Name = 'Some Account Name')
    ORDER BY Name,Id ASC NULLS LAST
*/

Build an aggregated query

DynamicSOQL soql = new DynamicSOQL('Opportunity')
.withField('StageName')
.withFunction(new DynamicSOQLFunction('SUM', 'Amount', 'amount'))
.withFunction(new DynamicSOQLFunction('COUNT', 'Id', 'oppCount'))
.withFunction(new DynamicSOQLFunction('AVG', 'Amount', 'avgAmount'))
.withGroupBy(
    new DynamicSOQLGroupBy(new List<String>{'StageName'})
    .withHaving(
        new DynamicSOQLConditionBlock('AND')
        .addCondition(new DynamicSOQLCondition(
            new DynamicSOQLFunction('SUM','Amount'), '>', 190)
        )
    )
);

System.debug(soql.stringify());

/* The output (line breaks was added manually):
    SELECT StageName,
        SUM(Amount) amount,
        COUNT(Id) oppCount,
        AVG(Amount) avgAmount
    FROM Opportunity
    GROUP BY StageName
        HAVING (SUM(Amount) > 190)
*/

Reference Guide

DynamicSOQL

Constructors

The following are constructors for DynamicSOQL.

  • DynamicSOQL(String sObjectName)
DynamicSOQL soql = new DynamicSOQL('Account');

Methods

The following are methods for DynamicSOQL. All are instance methods.

  • withField
    withField(String fieldName): DynamicSOQL
    Adds a field to SELECT statement
System.debug(
    new DynamicSOQL('Account')
    .withField('Id')
    .withField('Name')
    .withField('OwnerId')
    .stringify()
); // SELECT Id,Name,OwnerId FROM Account
  • withFunction
    withFunction(DynamicSOQLFunction function): DynamicSOQL
    Adds a function to SELECT statement like "COUNT(Id) recordsCount"
System.debug(
    new DynamicSOQL('Account')
    .withFunction(new DynamicSOQLFunction('COUNT', ''))
    .stringify()
); // SELECT COUNT() FROM Account
  • withSubQuery
    withSubQuery(String relationshipName, DynamicSOQL subQuery): DynamicSOQL
    Adds a subquery
System.debug(
    new DynamicSOQL('Account')
    .withField('Name')
    .withSubQuery(
        'Contacts',
        new DynamicSOQL('Contact')
        .withField('Id')
    ).stringify()
); // SELECT Name,(SELECT Id FROM Contacts) FROM Account
  • withConditions
    withConditions(DynamicSOQLConditionBlock conditionBlock): DynamicSOQL
    Adds a condition block to the query
System.debug(
    new DynamicSOQL('Account')
    .withField('Id')
    .withConditions(
        new DynamicSOQLConditionBlock('AND')
        .addCondition(new DynamicSOQLCondition('Name', '=', 'Test'))
        .addCondition(new DynamicSOQLCondition('CreatedDate', '>', Date.newInstance(2022, 01, 01)))
    ).stringify()
); // SELECT Id FROM Account WHERE (Name = 'Test' AND CreatedDate > 2022-01-01)
  • withGroupBy
    withGroupBy(DynamicSOQLGroupBy groupBy): DynamicSOQL
    Adds a GROUP BY statement to the query
System.debug(
    new DynamicSOQL('Opportunity')
    .withField('StageName')
    .withFunction(new DynamicSOQLFunction('SUM', 'Amount', 'amount'))
    .withGroupBy(
        new DynamicSOQLGroupBy(new List<String>{'StageName'})
        .withHaving(
            new DynamicSOQLConditionBlock('AND')
            .addCondition(new DynamicSOQLCondition(
                new DynamicSOQLFunction('SUM','Amount'), '>', 190)
            )
        )
    ).stringify()
); // SELECT StageName,SUM(Amount) amount FROM Opportunity GROUP BY StageName HAVING (SUM(Amount) > 190)
  • withOrderBy
    withOrderBy(DynamicSOQLOrderBy orderBy): DynamicSOQL
    Adds a ORDER BY statement to the query
System.debug(
    new DynamicSOQL('Account')
    .withField('Name')
    .withOrderBy(new DynamicSOQLOrderBy(new List<String>{'Name'}))
    .stringify()
); // SELECT Name FROM Account ORDER BY Name ASC NULLS LAST
  • withOffset
    withOffset(Integer offsetNumber): DynamicSOQL
    Adds OFFSET statement to SOQL
System.debug(
    new DynamicSOQL('Account')
    .withField('Id')
    .withOffset(0)
    .withLimit(10)
    .stringify()
); // SELECT Id FROM Account LIMIT 10 OFFSET 0
  • withLimit
    withLimit(Integer limitNumber): DynamicSOQL
    Adds LIMIT statement to SOQL
System.debug(
    new DynamicSOQL('Account')
    .withField('Id')
    .withOffset(0)
    .withLimit(10)
    .stringify()
); // SELECT Id FROM Account LIMIT 10 OFFSET 0
  • infoToFLSCheck
    infoToFLSCheck(): Map<String, Set<String>>
    Returns the Map in format: sObjectApiName => Set<String>{fieldApiName}
System.debug(
    new DynamicSOQL('Account')
    .withFunction(new DynamicSOQLFunction('COUNT', 'Id'))
    .withField('Name')
    .withSubQuery(
        'Contact',
        new DynamicSOQL('Contact')
        .withField('Id')
        .withField('FirstName')
    )
    .withConditions(
        new DynamicSOQLConditionBlock('AND')
        .addCondition(new DynamicSOQLCondition('Phone', '=', '12345'))
    )
    .withOrderBy(new DynamicSOQLOrderBy(new List<String>{'Industry'}))
    .infoToFLSCheck()
);

/*
{
    Account={Id, Industry, Name, Phone},
    Contact={FirstName, Id}
}
*/
  • stringify
    stringify(): String
    Builds a SOQL string
new DynamicSOQL('Account')
.withField('Id')
.withSubQuery('Contacts', new DynamicSOQL('Contact').withField('Id'))
.stringify(); // SELECT Id,(SELECT Id FROM Contacts) FROM Account

DynamicSOQLFunction

Constructors

The following are constructors for DynamicSOQLFunction.

  • DynamicSOQLFunction(String functionName)
DynamicSOQLFunction function = new DynamicSOQLFunction('COUNT');
System.debug(function.stringify()); // COUNT()
  • DynamicSOQLFunction(String functionName, String fieldName)
DynamicSOQLFunction function = new DynamicSOQLFunction('COUNT', 'Id');
System.debug(function.stringify()); // COUNT(Id)
  • DynamicSOQLFunction(String functionName, String fieldName, String alias)
DynamicSOQLFunction function = new DynamicSOQLFunction('COUNT', 'Id', 'recordsCount');
System.debug(function.stringify()); // COUNT(Id) recordsCount

Methods

The following are methods for DynamicSOQLFunction. All are instance methods.

  • fieldApiName
    fieldApiName(): String
    Returns the field api name that is used in a formula.
new DynamicSOQLFunction('COUNT', 'Id', 'alias')
.fieldApiname() // Id
  • stringify
    stringify(): String
    Builds a SOQL function string like COUNT(Id) recordsCount
new DynamicSOQLFunction('COUNT', 'Id', 'alias')
.stringify() // COUNT(Id) alias

DynamicSOQLConditionBlock

Constructors

The following are constructors for DynamicSOQLConditionBlock.

  • DynamicSOQLConditionBlock(String operator)
DynamicSOQLConditionBlock conditionBlock = new DynamicSOQLConditionBlock('AND')

Methods

The following are methods for DynamicSOQLConditionBlock. All are instance methods.

  • addCondition
    addCondition(DynamicSOQLCondition condition): DynamicSOQLConditionBlock
    Adds a condition to the current block
DynamicSOQLConditionBlock conditionBlock = new DynamicSOQLConditionBlock('OR')
.addCondition(
    new DynamicSOQLCondition('Name', '=', 'Test_1')
);

System.debug(conditionBlock.stringify('Account')); // (Name = 'Test_1')

conditionBlock
.addCondition(new DynamicSOQLCondition('Name', '=', 'Test_2'));
System.debug(conditionBlock.stringify('Account')); // (Name = 'Test_1' OR Name = 'Test_2')
  • addConditionBlock
    addConditionBlock(DynamicSOQLConditionBlock conditionBlock): DynamicSOQLConditionBlock
    Adds new condition block that will be added to the current one. It allow to build complex conditions like (condition OR condition) AND condition
DynamicSOQLConditionBlock conditionBlock = new DynamicSOQLConditionBlock('AND')
.addCondition(new DynamicSOQLCondition('Name', '=', 'Test1'))
.addConditionBlock(
    new DynamicSOQLConditionBlock('OR')
    .addCondition(new DynamicSOQLCondition('Phone', '=', '12345'))
    .addCondition(new DynamicSOQLCondition('Phone', '=', '123456'))
);

System.debug(conditionBlock.stringify('Account')); // ((Phone = '12345' OR Phone = '123456') AND Name = 'Test1')
  • switchOperator
    switchOperator(String operator): DynamicSOQLConditionBlock Changes the operator. Could be either OR | AND
DynamicSOQLConditionBlock conditionBlock = new DynamicSOQLConditionBlock('AND')
.addCondition(new DynamicSOQLCondition('Phone', '=', '12345'))
.addCondition(new DynamicSOQLCondition('Phone', '=', '123456'));

System.debug(conditionBlock.stringify('Account')); // (Phone = '12345' AND Phone = '123456')
conditionBlock.switchOperator('OR');
System.debug(conditionBlock.stringify('Account')); // (Phone = '12345' OR Phone = '123456')
  • fieldsApiNames
    fieldsApiNames(): Set<String>
    Returns all field api names from DynamicSOQLCondition and DynamicSOQLConditionBlock
DynamicSOQLConditionBlock conditionBlock = new DynamicSOQLConditionBlock('AND')
.addCondition(new DynamicSOQLCondition('Phone', '=', '121345'))
.addCondition(new DynamicSOQLCondition('FirstName', '=', 'Test'))
.addCondition(new DynamicSOQLCondition('LastName', '=', 'Test'));

conditionBlock.fieldsApiNames(); // {'Phone', 'FirstName', 'LastName'}
  • stringify
    stringify(String sobjectApiName): String
    Builds a Dynamic SOQL Condition Block string for WHERE statement

DynamicSOQLCondition

Constructors

The following are constructors for DynamicSOQLCondition.

  • DynamicSOQLCondition(String fieldName, String operator, Object value)
DynamicSOQLCondition booleanCondition = new DynamicSOQLCondition('isActive', '=', true);
DynamicSOQLCondition numberCondition = new DynamicSOQLCondition('Amount', '>=', 100);
  • DynamicSOQLCondition(DynamicSOQLFunction function, String operator, Object value)
DynamicSOQLCondition condition = new DynamicSOQLCondition(
    new DynamicSOQLFunction('COUNT', 'Amount'),
    '>',
    100
);
  • DynamicSOQLCondition(String fieldName, String operator, Datetime value)
DynamicSOQLCondition condition = new DynamicSOQLCondition('CreatedDate', '>', Datetime.newInstance(2022, 01, 01, 01, 01, 01));
  • DynamicSOQLCondition(String fieldName, String operator, Date value)
DynamicSOQLCondition condition = new DynamicSOQLCondition('CreatedDate', '>', Date.newInstance(2022, 01, 01));
  • DynamicSOQLCondition(String fieldName, String operator, String value)
DynamicSOQLCondition stringCondition = new DynamicSOQLCondition('Name', '=', 'It\'s a string');

Set<Id> someVariable = new Set<Id>{'id_1','id_2','id_3'}
DynamicSOQLCondition variableCondition = new DynamicSOQLCondition('Id', 'IN:', 'someVariable');

Methods

The following are methods for DynamicSOQLCondition. All are instance methods.

  • fieldApiName
    fieldApiName(): String
    Returns the field api name that is used in a condition.

  • stringify
    stringify(String sobjectApiName): String
    Builds a SOQL condition string like Name = 'Andrew'

DynamicSOQLOrderBy

Constructors

The following are constructors for DynamicSOQLOrderBy.

  • DynamicSOQLOrderBy(List<String> orderByFields)
DynamicSOQLOrderBy orderBy = new DynamicSOQLOrderBy(new List<String>{'Name', 'Id'});
  • DynamicSOQLOrderBy(List<String> orderByFields, Boolean isDESC)
DynamicSOQLOrderBy orderBy = new DynamicSOQLOrderBy(new List<String>{'Name', 'Id'}, true);
  • DynamicSOQLOrderBy(List<String> orderByFields, Boolean isDESC, Boolean isNullsFirst)
DynamicSOQLOrderBy orderBy = new DynamicSOQLOrderBy(new List<String>{'Name', 'Id'}, true, true);

Methods

The following are methods for DynamicSOQLOrderBy. All are instance methods.

  • fieldsApiNames
    fieldsApiNames(): Set<String>
    Returns list of fields that are used in ORDER BY Statement

  • stringify
    stringify(): String
    Returns ORDER BY statement like: "ORDER BY Name ASC NULLS LAST"

DynamicSOQLGroupBy

Constructors

The following are constructors for DynamicSOQLGroupBy.

  • DynamicSOQLGroupBy(List<String> fieldGroupByList)
DynamicSOQLGroupBy groupBy = new DynamicSOQLGroupBy(new List<String>{'StageName'});

Methods

The following are methods for DynamicSOQLGroupBy. All are instance methods.

  • fieldsApiNames
    fieldsApiNames(): Set<String>
    Returns list of fields that are used in GROUP BY Statement

  • withHaving
    withHaving(DynamicSOQLConditionBlock conditionBlock): DynamicSOQLGroupBy
    Adds HAVING clause to the GROUP BY clause

  • stringify
    stringify(String sobjectApiName): String
    Builds a GROUP BY part of SOQL string