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

v2.1.0

28-July-2023

Changes

Ignore conditions when logic evaluates to true

Instead of doing this:

public SOQL.FilterGroup getFilters(String accountName, String accountIndustry) {
      SOQL.FilterGroup group = SOQL.FilterGroup;

      if (String.isNotBlank(accountName)) {
         group.add(SOQL.Filter.name().equal(accountName));
      }
      if (String.isNotBlank(accountIndustry)) {
         group.add(SOQL.Filter.with(Account.Industry).equal(accountIndustry));
      }
      
      return group;
}

Developer can do:

SOQL.FilterGroup
   .add(SOQL.Filter.name().equal(accountName).ignoreWhen(String.isBlank(accountName)))
   .add(SOQL.Filter.with(Account.Industry).equal(accountName).ignoreWhen(String.isBlank(accountIndustry)))

Check if record exists

Instead of:

Boolean isRecordExists = SOQL.of(Account.SObjectType).byId('someId').toList() > 0;

Developer can do:

Boolean isRecordExists = SOQL.of(Account.SObjectType).byId('someId').doExist();

Delete removeWhenNull method

Method .removeWhenNull() is not longer supported and was deleted.
Developers can use .ignoreWhen(value == null) instead.

v2.0.2

03-July-2023

Changes

Work with multi-select picklists

New methods were added:

• includesAll
• includesSome
• excludesAll
• excludesSome

Query Exception

Query Exception List has no rows for assignment to SObject will be catch and null will be returned.

toValuesOf

Using https://salesforce.stackexchange.com/questions/393308/get-a-list-of-one-column-from-a-soql-result approach all values from one field can be gather.

v2.0.1

27-June-2023

Changes

Use Iterable Set

Based on Summer '23 the Set class now implements the Iterable interface.

https://help.salesforce.com/s/articleView?language=en_US&id=release-notes.rn_apex_set_implements_iterable.htm&release=244&type=5

Iterable was used as a supertype for List and Set.

custom LIKE

https://soql-lib.vercel.app/api/soql-filter#contains

You can specify custom contains with - and %.

SOQL.Filter.with(Account.Name).contains('_', 'My', '%')

of(String)

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

You can create instance of SOQL dynamically.

SOQL of(String ofObject)

String ofObject = 'Account';
SOQL.of(ofObject).toList();

toField - null pointer check

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

For null record .toField method will return null value.

Code Refactoring

Unused lines of code were removed.

v2.0.0

05-June-2023

Changes

Code Against Interfaces, Not Implementations

Static variables return Interface instead of concrete class.

// Filter is interface
// QFilter is a class
public static Filter Filter {
   get {
      return new QFilter(binder);
   }
}

String condition

String condition and FilterGroup/Filter are combined with AND.

SOQL.of(Account.SObjectType)
    .whereAre('EmployeeNumber > 5')
    .whereAre(SOQL.Filter.with(Account.Name).equal(accountName).removeWhenNull())
    .toList();

Skip Binding for DataTime

System.QueryException: Invalid bind expression type of String for column of type Datetime

Data Literals are supported.

SOQL.of(Account.SObjectType)
    .whereAre(SOQL.Filter.with(Account.CreatedDate).equal('YEASTERDAY'))
    .toList();

Code Refactoring

Code is simpler, a lot of lines was removed.

v1.0.2

31-May-2023

Changes

with(field1 - field5)

Make code easier to read by new with methods.

Documentation

SOQL with(SObjectField field)
SOQL with(SObjectField field1, SObjectField field2);
SOQL with(SObjectField field1, SObjectField field2, SObjectField field3);
SOQL with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4);
SOQL with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5);

removeWhenNull

Documentation

Condition will be removed when filter's value is null.

String accountName = null;

SOQL.of(Account.SObjectType)
    .whereAre(SOQL.Filter.with(Account.Name).equal(accountName).removeWhenNull())
    .toList();

anyConditionMatching

You can utilize the anyConditionMatching method, which joins conditions using the OR operator.

SOQL.of(Account.SObjectType)
    .whereAre(SOQL.FilterGroup
        .add(SOQL.Filter.with(Account.Name).equal('My Account'))
        .add(SOQL.Filter.with(Account.NumberOfEmployees).greaterThanOrEqual(10))
        .anyConditionMatching()
    ).toList();

Fixed Issues

Count Fix

COUNT() must be the only element in the SELECT list, any other fields will be automatically removed.

Code Refactoring

Code is simpler, a lot of lines was removed.

SOQL 1.0.1

1. Dynamic orderBy - orderBy(String field) and orderBy(String field, String direction);
2. Documentation updates
3. Empty FilterGroup fix

SOQL 1.0.0

First stable version.