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