v3.3.0

06-October-2024

New

• HAVING support

HAVING support

HAVING is an optional clause that can be used in a SOQL query to filter results that aggregate functions return.

You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, such as SUM(). The HAVING clause is similar to a WHERE clause. The difference is that you can include aggregate functions in a HAVING clause, but not in a WHERE clause.

Signature

Queryable have(HavingFilterGroup filterGroup);
Queryable have(HavingFilter filter);
Queryable have(String dynamicCondition);
Queryable havingConditionLogic(String order);
Queryable anyHavingConditionMatching();

Example

SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .count(Lead.Name)
    .groupBy(Lead.City)
    .groupBy(Lead.LeadSource)
    .have(SOQL.HavingFilterGroup
        .add(SOQL.HavingFilter.count(Lead.Name).greaterThan(100))
        .add(SOQL.HavingFilter.with(Lead.City).startsWith('San'))
    )
    .toAggregated();

SOQL.of(Lead.SObjectType)
    .sum(Lead.AnnualRevenue)
    .groupBy(Lead.LeadSource)
    .have(SOQL.HavingFilter.sum(Lead.AnnualRevenue).greaterThan(1000000))
    .toAggregated();

More details:

• SOQL HAVING
• HavingFilterGroup
• HavingFilter

release/v3.2.4

01-July-2024

New

• userMode()
• API Update 61.0

userMode

Execution mode in which the object permissions, field-level security, and sharing rules of the current user are enforced.

By default, all queries are executed WITH USER_MODE. However, developers can override this. For more details, check Field-Level Security and Sharing Rules.

The userMode() method can be useful to override the systemMode() provided by the selector as in the example below.

Signature

Queryable userMode()

Example

public inherited sharing class SOQL_Account extends SOQL implements SOQL.Selector {
    public static final String MOCK_ID = 'SOQL_Account';

    public static SOQL_Account query() {
        return new SOQL_Account();
    }

    private SOQL_Account() {
        super(Account.SObjectType);
        // default settings
        with(Account.Id, Account.Name, Account.Type);
        systemMode();
        withoutSharing();
        mockId(MOCK_ID);
    }
}

SOQL_Account.query()
    .userMode() // systemMode() from SOQL_Account.query will be overridden by userMode() method
    .toList();

v3.2.3

25-April-2024

New

• groupBy(String relationshipName, SObjectField field)
• groupByRollup(String relationshipName, SObjectField field)
• groupByCube(String relationshipName, SObjectField field)

Refactoring

• Iterable for SubQuery with(Iterable<SObjectField> fields)
• Iterable for Filter containsSome(Iterable<String> values)
• Code refactoring

groupBy related

Thanks to @jessewheeler 's help, we have a groupBy related.

Signature

Queryable groupBy(String relationshipName, SObjectField field)

Example

SELECT COUNT(Name) count
FROM OpportunityLineItem
GROUP BY OpportunityLineItem.Opportunity.Account.Id

SOQL.of(OpportunityLineItem.SObjectType)
    .count(OpportunityLineItem.Name, 'count')
    .groupBy('OpportunityLineItem.Opportunity.Account', Account.Id)
    .toAggregated();

groupByRollup related

Signature

Queryable groupByRollup(String relationshipName, SObjectField field)

Example

SELECT COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(ConvertedOpportunity.StageName)

SOQL.of(Lead.SObjectType)
    .count(Lead.Name, 'cnt')
    .groupByRollup('ConvertedOpportunity', Opportunity.StageName)
    .toAggregated();

groupByCube related

Signature

Queryable groupByCube(String relationshipName, SObjectField field)

Example

SELECT COUNT(Name) cnt
FROM Lead
GROUP BY CUBE(ConvertedOpportunity.StageName)

SOQL.of(Lead.SObjectType)
    .count(Lead.Name, 'cnt')
    .groupByCube('ConvertedOpportunity', Opportunity.StageName)
    .toAggregated();

Code refactoring

Applied the Iterable interface where possible for a small code refactoring to simplify the code.

v3.2.2

27-March-2024

New

• withFieldSet(String fieldSetName)
• byRecordType(String recordTypeDeveloperName)

Refactoring

• API 60 Update
• Iterable for with(String relationshipName, Iterable<SObjectField> fields)
• Code refactoring

withFieldSet

Pass FieldSet name to get dynamic fields. It can be very useful to have a dynamic UI.

Signature

Queryable withFieldSet(String fieldSetName)

Example

SELECT
    Id,
    Name,
    Industry
FROM Account

SOQL.of(Account.SObjectType)
    .withFieldSet('AccountFieldSet')
    .toList();

byRecordType

Query record by RecordType.DeveloperName. To do that, you can use the byRecordType method.

🚨 If you already have a method called byRecordType in your selector, you may encounter the following issues:
Non-virtual, non-abstract methods cannot be overridden: YourSelector YourSelector.byRecordType(String)

You have two options:

1. You can remove the byRecordType from your selector and use the predefined method from the SOQL library.
2. You can skip that update and still use byRecordType from your selector.

Signature

Queryable byRecordType(String recordTypeDeveloperName)

Example

SELECT Id
FROM Account
WHERE RecordType.DeveloperName = 'Partner'

SOQL.of(Account.SObjectType)
    .byRecordType('Partner')
    .toList();

API 60 Update

🚨Do not forget to update ALL selectors to API 60.

Selectors with API < 60 that are using the isIn method will encounter the following error:
Method does not exist or incorrect signature: void isIn(List<String>) from the type SOQL.Filter

Salesforce has made some changes, and this update is necessary!

Iterable for with(String relationshipName, Iterable<SObjectField> fields)

Now you can pass List<SObjectField> as well as Set<SObjectField> to with(String relationshipName, Iterable<SObjectField> fields).

Code refactoring

Unused lines of code were removed. Null Coalescing Operator was applied.

v3.2.1

16-February-2024

Update of SOQL_Test

We replaced the Standard User Profile with Minimum Access - Salesforce, ensuring that the stripInaccessibleToObject and stripInaccessibleToList tests will execute successfully regardless of the CRUD settings in the Salesforce org.

From:

 static User standardUser() {
        return new User(
            Alias = 'newUser',
            Email = '[email protected]',
            EmailEncodingKey = 'UTF-8',
            LastName = 'Testing',
            LanguageLocaleKey = 'en_US',
            LocaleSidKey = 'en_US',
            ProfileId = [SELECT Id FROM Profile WHERE Name = 'Standard User'].Id,
            TimeZoneSidKey = 'America/Los_Angeles',
            UserName = '[email protected]'
        );
}

To:

 static User minimumAccessUser() {
        return new User(
            Alias = 'newUser',
            Email = '[email protected]',
            EmailEncodingKey = 'UTF-8',
            LastName = 'Testing',
            LanguageLocaleKey = 'en_US',
            LocaleSidKey = 'en_US',
            ProfileId = [SELECT Id FROM Profile WHERE Name = 'Minimum Access - Salesforce'].Id,
            TimeZoneSidKey = 'America/Los_Angeles',
            UserName = '[email protected]'
        );
 }

v3.2.0

19-December-2023

• Changes
  • COUNT related
  • AVG related
  • COUNT_DISTINCT related
  • MIN related
  • MAX related
  • SUM related
  • toLabel
  • format
  • Code refactoring

COUNT

Introducing the count method for related fields.

Queryable count(String relationshipName, SObjectField field);
Queryable count(String relationshipName, SObjectField field, String alias);

// SELECT COUNT(Account.Name) 
// FROM Contact

SOQL.of(Contact.SObjectType)
    .count('Account', Account.Name)
    .toAggregated();

// SELECT COUNT(Account.Name) names 
// FROM Contact

SOQL.of(Contact.SObjectType)
    .count('Account', Account.Name, 'names')
    .toAggregated();

AVG

Introducing the avg method for related fields.

Queryable avg(String relationshipName, SObjectField field);
Queryable avg(String relationshipName, SObjectField field, String alias);

// SELECT AVG(Opportunity.Amount) 
// FROM OpportunityLineItem

SOQL.of(OpportunityLineItem.SObjectType)
    .avg('Opportunity', Opportunity.Amount)
    .toAggregate();

// SELECT AVG(Opportunity.Amount) amount 
// FROM OpportunityLineItem

SOQL.of(OpportunityLineItem.SObjectType)
    .avg('Opportunity', Opportunity.Amount, 'amount')
    .toAggregate();

COUNT_DISTINCT

Introducing the countDistinct method for related fields.

Queryable countDistinct(String relationshipName, SObjectField field);
Queryable countDistinct(String relationshipName, SObjectField field, String alias);

// SELECT COUNT_DISTINCT(Lead.Company) 
// FROM CampaignMember

SOQL.of(CampaignMember.SObjectType)
    .countDistinct('Lead', Lead.Company)
    .toAggregate();

// SELECT COUNT_DISTINCT(Lead.Company) company 
// FROM CampaignMember

SOQL.of(CampaignMember.SObjectType)
    .countDistinct('Lead', Lead.Company, 'company')
    .toAggregate();

MIN

Introducing the min method for related fields.

Queryable min(String relationshipName, SObjectField field);
Queryable min(String relationshipName, SObjectField field, String alias);

// SELECT MIN(Account.CreatedDate) 
// FROM Contact

SOQL.of(Contact.SObjectType)
    .min('Account', Account.CreatedDate)
    .toAggregate();

// SELECT MIN(Account.CreatedDate) createdDate 
// FROM Contact

SOQL.of(Contact.SObjectType)
    .min('Account', Account.CreatedDate, 'createdDate')
    .toAggregate();

MAX

Introducing the max method for related fields.

Queryable max(String relationshipName, SObjectField field);
Queryable max(String relationshipName, SObjectField field, String alias);

// SELECT MAX(Campaign.BudgetedCost) 
// FROM CampaignMember

SOQL.of(CampaignMember.SObjectType)
    .max('Campaign', Campaign.BudgetedCost)
    .toAggregate();

// SELECT MAX(Campaign.BudgetedCost) budgetedCost 
// FROM CampaignMember

SOQL.of(CampaignMember.SObjectType)
    .max('Campaign', Campaign.BudgetedCost, 'budgetedCost')
    .toAggregate();

SUM

Introducing the sum method for related fields.

Queryable sum(String relationshipName, SObjectField field);
Queryable sum(String relationshipName, SObjectField field, String alias);

// SELECT SUM(Opportunity.Amount)
// FROM OpportunityLineItem

SOQL.of(OpportunityLineItem.SObjectType)
    .sum('Opportunity', Opportunity.Amount)
    .toAggregate();

// SELECT SUM(Opportunity.Amount) amount 
// FROM OpportunityLineItem

SOQL.of(OpportunityLineItem.SObjectType)
    .sum('Opportunity', Opportunity.Amount, 'amount')
    .toAggregate();

toLabel

toLabel()

To translate SOQL query results into the language of the user who submits the query, use the toLabel method.

Queryable toLabel(SObjectField field);
Queryable toLabel(String field);

// SELECT Company, toLabel(Status) 
// FROM Lead

SOQL.of(Lead.SObjectType)
    .with(Lead.Company)
    .toLabel(Lead.Status)
    .toList();

// SELECT Company, toLabel(Recordtype.Name) 
// FROM Lead

SOQL.of(Lead.SObjectType)
    .with(Lead.Company)
    .toLabel('Recordtype.Name')
    .toList();

format

FORMAT()

Use FORMAT with the SELECT clause to apply localized formatting to standard and custom number, date, time, and currency fields.

Queryable format(SObjectField field);
Queryable format(SObjectField field, String alias);

// SELECT FORMAT(Amount)
// FROM Opportunity

SOQL.of(Opportunity.SObjectType)
    .format(Opportunity.Amount)
    .toList();

// SELECT FORMAT(Amount) amt 
// FROM Opportunity

SOQL.of(Opportunity.SObjectType)
    .format(Opportunity.Amount, 'amt')
    .toList();

v3.1.0

08-November-2023

• Changes
  • AVG
  • COUNT_DISTINCT
  • MIN
  • MAX
  • SUM
  • ignoreWhen for FilterGroup
  • Code refactoring

AVG

Introducing the avg method.

Queryable avg(SObjectField field);
Queryable avg(SObjectField field, String alias);

// SELECT CampaignId, AVG(Amount) amount 
// FROM Opportunity 
// GROUP BY CampaignId

SOQL.of(Opportunity.SObjectType)
     .with(Opportunity.CampaignId)
     .avg(Opportunity.Amount)
     .groupBy(Opportunity.CampaignId)
     .toAggregated();

// SELECT CampaignId, AVG(Amount) amount 
// FROM Opportunity 
// GROUP BY CampaignId

SOQL.of(Opportunity.SObjectType)
    .with(Opportunity.CampaignId)
    .avg(Opportunity.Amount, 'amount')
    .groupBy(Opportunity.CampaignId)
    .toAggregated();

COUNT_DISTINCT

Introducing the countDistinct method.

Queryable countDistinct(SObjectField field);
Queryable countDistinct(SObjectField field, String alias);

// SELECT COUNT_DISTINCT(Company) FROM Lead

SOQL.of(Lead.SObjectType)
    .countDistinct(Lead.Company)
    .toAggregated();

// SELECT COUNT_DISTINCT(Company) company FROM Lead

SOQL.of(Lead.SObjectType)
    .countDistinct(Lead.Company, 'company')
    .toAggregated();

MIN

Introducing the min method.

Queryable min(SObjectField field);
Queryable min(SObjectField field, String alias);

// SELECT FirstName, LastName, MIN(CreatedDate) 
// FROM Contact 
// GROUP BY FirstName, LastName

SOQL.of(Contact.SObjectType)
    .with(Contact.FirstName, Contact.LastName)
    .min(Contact.CreatedDate)
    .groupBy(Contact.FirstName)
    .groupBy(Contact.LastName)
    .toAggregated();

// SELECT FirstName, LastName, MIN(CreatedDate) createdDate
// FROM Contact 
// GROUP BY FirstName, LastName

SOQL.of(Contact.SObjectType)
    .with(Contact.FirstName, Contact.LastName)
    .min(Contact.CreatedDate, 'createdDate')
    .groupBy(Contact.FirstName)
     groupBy(Contact.LastName)
    .toAggregated();

MAX

Introducing the max method.

Queryable max(SObjectField field);
Queryable max(SObjectField field, String alias);

// SELECT Name, MAX(BudgetedCost) 
// FROM Campaign 
// GROUP BY Name

SOQL.of(Campaign.SObjectType)
    .with(Campaign.Name)
    .max(Campaign.BudgetedCost)
    .groupBy(Campaign.Name)
    .toAggregated();

// SELECT Name, MAX(BudgetedCost) budgetedCost
// FROM Campaign 
// GROUP BY Name

SOQL.of(Campaign.SObjectType)
    .with(Campaign.Name)
    .max(Campaign.BudgetedCost, 'budgetedCost')
    .groupBy(Campaign.Name)
    .toAggregated();

SUM

Introducing the sum method.

Queryable sum(SObjectField field);
Queryable sum(SObjectField field, String alias);

// SELECT SUM(Amount) FROM Opportunity

SOQL.of(Opportunity.SObjectType)
    .sum(Opportunity.Amount)
    .toAggregated()

// SELECT SUM(Amount) amount FROM Opportunity

SOQL.of(Opportunity.SObjectType)
    .sum(Opportunity.Amount, 'amount')
    .toAggregated()

ignoreWhen for FilterGroup

The ignoreWhen method was previously available only for Filter, but now you can use it for FilterGroup.

Boolean isPartnerUser = false;

SOQL.of(Account.SObjectType)
    .whereAre(SOQL.FilterGroup
        .add(SOQL.FilterGroup
            .add(SOQL.Filter.with(Account.BillingCity).equal('Krakow'))
            .add(SOQL.Filter.with(Account.BillingCity).equal('Warsaw'))
            .anyConditionMatching()
            .ignoreWhen(!isPartnerUser) // <===
        )
        .add(SOQL.FilterGroup
            .add(SOQL.Filter.with(Account.Industry).equal('IT'))
            .add(SOQL.Filter.name().contains('MyAcccount'))
        )
    )
    .toList();

v3.0.0

10-October-2023

• Changes
  • GROUP BY CUBE
  • GROUPING
  • GROUP BY Exception
  • toMap
  • toAggregatedMap
• Fixes
  • Code refactoring
  • GROUP BY Fix
  • toMap Casting Fix

Changes

GROUP BY CUBE

Introducing the groupByCube method.

Queryable groupByCube(SObjectField field);

// SELECT Type FROM Account GROUP BY CUBE(Type)
SOQL.of(Account.SObjectType)
    .with(Account.Type)
    .groupByCube(Account.Type)
    .toAggregated();

GROUPING

Introducing the grouping method.

Queryable grouping(SObjectField field, String alias);

// SELECT LeadSource, Rating, GROUPING(LeadSource) grpLS, GROUPING(Rating) grpRating, COUNT(Name) cnt 
// FROM Lead 
// GROUP BY ROLLUP(LeadSource, Rating)

SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource, Lead.Rating)
    .grouping(Lead.LeadSource, 'grpLS')
    .grouping(Lead.Rating, 'grpRating')
    .count(Lead.Name, 'cnt')
    .groupByRollup(Lead.LeadSource)
    .groupByRollup(Lead.Rating)
    .toAggregated();

GROUP BY Exception

As stated in the Salesforce Documentation:

You can't combine GROUP BY and GROUP BY CUBE syntax in the same statement. For example, GROUP BY CUBE(field1), field2 is not valid as all grouped fields must be within the parentheses.

You can't combine GROUP BY and GROUP BY ROLLUP syntax in the same statement. For example, GROUP BY ROLLUP(field1), field2 is not valid as all grouped fields must be within the parentheses.

The following code will throw an error.

SOQL.of(Account.SObjectType)
    .with(Account.Type)
    .groupBy(Account.Type)
    .groupByCube(Account.Type)
    .toObject();

You can't use GROUP BY, GROUP BY ROLLUP and GROUP BY CUBE in the same query.

toMap

toMap with Custom Key

Map<String, SObject> toMap(SObjectField keyField);

You can use any String field as a Map key.

Instead of:

Map<String, Account> nameToAccount = new Map<String, Account>();

for (Account acc : [SELECT Id, Name FROM Account]) {
   nameToAccount.put(acc.Name, acc);
}

You can now do:

Map<String, Account> nameToAccount = (Map<String, Account>) SOQL.of(Account.SObjectType).toMap(Account.Name);

toMap with Custom Key an Custom Value

Map<String, String> toMap(SObjectField keyField, SObjectField valueField);

You can use any String field as a Map key and another field as a source of value.

Instead of:

Map<String, String> accountNameToIndustry = new Map<String, String>();

for (Account acc : [SELECT Id, Name, Industry FROM Account]) {
   accountNameToIndustry.put(acc.Name, acc.Industry);
}

You can now do:

Map<String, String> accountNameToIndustry = SOQL.of(Account.SObjectType).toMap(Account.Name, Account.Industry);
Map<String, String> accountIdToIndustry = SOQL.of(Account.SObjectType).toMap(Account.Id, Account.Industry);

toAggregatedMap

toAggregatedMap with Custom Key

Map<String, List<SObject>> toAggregatedMap(SObjectField keyField);

You can use any String field as a Map key.

Instead of:

Map<String, List<Account>> accountsPerIndustry = new Map<String, List<Account>>();

for (Account acc : [SELECT Id, Name, Industry FROM Account]) {
 
   if (!accountsPerIndustry.containsKey(acc.Industry)) {
      accountsPerIndustry.put(acc.Industry, new List<Account>());
   }

   accountsPerIndustry.get(acc.Industry).add(acc);
}

You can now do:

Map<String, List<Account>> accountsPerIndustry = (Map<String, List<Account>>) SOQL.of(Account.SObjectType).toAggregatedMap(Account.Industry);

toAggregatedMap with Custom Key an Custom Value

Map<String, List<String>> toAggregatedMap(SObjectField keyField, SObjectField valueField);

You can use any String field as a Map key and another field as a source of aggregated values.

Instead of:

Map<String, List<String>> accountNamesPerIndustry = new Map<String, List<String>>();

for (Account acc : [SELECT Id, Name, Industry FROM Account]) {
 
   if (!accountNamesPerIndustry.containsKey(acc.Industry)) {
      accountNamesPerIndustry.put(acc.Industry, new List<String>());
   }

   accountNamesPerIndustry.get(acc.Industry).add(acc.Name);
}

You can now do:

Map<String, List<String>> accountNamesPerIndustry = SOQL.of(Account.SObjectType).toAggregatedMap(Account.Industry, Account.Name);

Fixes

Code refactoring

GROUP BY Fix

Default fields were causing aField must be grouped or aggregated error.

Fields that are not grouped or aggregated will be automatically removed from the query.

// SELECT LeadSource FROM Lead GROUP BY LeadSource
SOQL.of(Lead.SObjectType)
    .with(Lead.FirstName, Lead.LastName, Lead.Email) // default fields
    .with(Lead.LeadSource)
    .groupBy(Lead.LeadSource)
    .toAggregated();

toMap Casting Fix

You can cast the result of toMap().

Map<Id, Account> result = (Map<Id, Account>) SOQL.of(Account.SObjectType).toMap();

v2.2.1

08-September-2023

Changes

New with method

Introducing the with method:

with(Iterable<String> fields)

Now you can pass a List or Set of fields for your query. This feature can be incredibly helpful for dynamic queries.

Note! Whenever possible, please use with(List<SObjectField> fields). SObjectField refers to the field in the code. If someone tries to remove it, an error message will appear: "This custom field definition is referenced elsewhere in salesforce.com."

Example usage with a List:

SOQL.of(Account.SObjectType)
    .with(new List<String>{'Id', 'Name', 'Industry'})
    .toList();

Example usage with a Set:

SOQL.of(Account.SObjectType)
    .with(new Set<String>{'Id', 'Name', 'Industry'})
    .toList();

Add COUNT to toInteger()

When the COUNT statement is not specified with toInteger(), COUNT will be automatically added. This simplifies the query.

Previously, code like the following would throw an error:

Integer accountsAmount = SOQL.of(Account.SObjectType).toInteger(); 

The valid option was:

// SELECT COUNT() FROM Account
Integer accountsAmount = SOQL.of(Account.SObjectType).count().toInteger();

Now, you can simply write:

// SELECT COUNT() FROM Account
Integer accountsAmount = SOQL.of(Account.SObjectType).toInteger();

v2.2.0

08-August-2023

Changes

Control condition order for main filters group

You can now specify the order for conditions used in separated whereAre clauses.
Previously, this was only possible using the new FilterGroup.

Use anyConditionMatching() or conditionLogic(String order).

SOQL.of(Account.SObjectType)
     .whereAre(SOQL.Filter.with(Account.Name).equal('Test'))
     .whereAre(SOQL.Filter.with(Account.Industry).equal('IT'))
     .conditionLogic('1 OR 2')
     .toList();

Documentation Update

Check out the updated documentation at:

https://soql-lib.vercel.app/api/soql

The list of all methods is now placed at the top of the concrete API page.

Date Literals

Date Literals cannot be bound. To skip binding, we have created the asDateLiteral() method, which notifies the library that the filter contains a Date Literal.

After an in-depth analysis of possible implementations, we decided to introduce this new method to handle Date Literals.

SOQL.of(Account.SObjectType) 
     .whereAre(SOQL.Filter.with(Account.CreatedDate).greaterThan('LAST_N_QUARTERS:2').asDateLiteral())
     .toList();

Filter Group Refactoring

FilterGroup now uses String.format to build conditions, offering a simpler and more efficient solution.

New Filter methods

Author: @salberski

Several new methods have been created:

notContains

SELECT Id
FROM Account
WHERE NOT Name LIKE '%My%'

SOQL.of(Contact.SObjectType)
    .whereAre(SOQL.Filter.name().notContains('My'))
    .toList();

SOQL.of(Contact.SObjectType)
    .whereAre(SOQL.Filter.name().notContains('_', 'My', '%'))
    .toList();

notEndsWith

SELECT Id
FROM Account
WHERE NOT Name LIKE '%My'

SOQL.of(Contact.SObjectType)
    .whereAre(SOQL.Filter.name().notEndsWith('My'))
    .toList();

notStartsWith

SELECT Id
FROM Account
WHERE NOT Name LIKE 'My%'

SOQL.of(Contact.SObjectType)
    .whereAre(SOQL.Filter.name().notStartsWith('My'))
    .toList();