Skip to main content

SOQL

The lib main class for query construction.

Methods​

The following are methods for SOQL.

INIT

SELECT

COUNT

GROUPING

SUBQUERY

USING SCOPE

WHERE

GROUP BY

ORDER BY

LIMIT

OFFSET

FOR

FIELD-LEVEL SECURITY

SHARING MODE

MOCKING

DEBUGGING

PREDEFINIED

RESULT

INIT​

of​

Conctructs an SOQL.

Signature

SOQL of(SObjectType ofObject)
SOQL of(String ofObject)

Example

SELECT Id FROM Account
SOQL.of(Account.SObjectType).toList();

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

SELECT​

with field1 - field5​

Signature

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

Example

SELECT Id, Name
FROM Account
SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .toList();

SOQL.of(Account.SObjectType)
    .with(Account.Id)
    .with(Account.Name)
    .toList();

with fields​

SELECT

SELECT statement that specifies the fields to query. The fieldList in the SELECT statement specifies the list of one or more fields, separated by commas, that you want to retrieve.

Use for more than 5 fields.

Signature

SOQL with(List<SObjectField> fields)
SOQL with(List<String> fields)

Example

SELECT Id, Name, Industry, AccountNumber, AnnualRevenue, BillingCity
FROM Account
SOQL.of(Account.SObjectType)
    .with(new List<SObjectField>{
        Account.Id,
        Account.Name,
        Account.Industry,
        Account.AccountNumber,
        Account.AnnualRevenue,
        Account.BillingCity
    }).toList();

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

with string fields​

NOTE! With String Apex does not create reference to field. Use SObjectField whenever it possible. Method below should be only use for dynamic queries.

Signature

SOQL with(String fields)

Example

SELECT Id, Name, Industry
FROM Account
SOQL.of(Account.SObjectType)
    .with('Id, Name, Industry')
    .toList();

with related field1 - field5​

Allows to add parent field to a query.

Signature

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

Example

SELECT CreatedBy.Name
FROM Account
SOQL.of(Account.SObjectType)
    .with('CreatedBy', User.Name)
    .toList();

SOQL.of(Account.SObjectType)
    .with('CreatedBy', User.Id, User.Name, User.Phone)
    .toList();

SELECT

Allows to add parent fields to a query.

Use for more than 5 parent fields.

Signature

SOQL with(String relationshipName, List<SObjectField> fields)

Example

SELECT
    CreatedBy.Id,
    CreatedBy.Name,
    CreatedBy.Phone,
    CreatedBy.FirstName,
    CreatedBy.LastName,
    CreatedBy.Email
FROM Account
SOQL.of(Account.SObjectType)
    .with('CreatedBy', new List<SObjectField>{
        User.Id,
        User.Name,
        User.Phone,
        User.FirstName,
        User.LastName,
        User.Email
    }).toList();

SUB-QUERY​

with subquery​

Using Relationship Queries

Use SOQL to query several relationship types.

For more details check SOQL.SubQuery class.

Signature

SOQL with(SOQL.SubQuery subQuery)

Example

SELECT Id, (
    SELECT Id, Name
    FROM Contacts
) FROM Account
SOQL.of(Account.SObjectType)
    .with(SOQL.SubQuery.of('Contacts')
        .with(Contact.Id, Contact.Name)
    ).toList();

COUNT-QUERY​

count​

COUNT()

COUNT() returns the number of rows that match the filtering conditions.

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

Signature

SOQL count()

Example

SELECT COUNT()
FROM Account
SOQL.of(Account.SObjectType)
    .count()
    .toInteger();

count field​

Signature

count(SObjectField field)

Note! To avoid the Field must be grouped or aggregated error, any default fields will be automatically removed.

You can still specify additional fields, but they should be placed after the COUNT() function in the SELECT statement.

Example

SELECT COUNT(Id), COUNT(CampaignId)
FROM Opportunity
 SOQL.of(Opportunity.SObjectType)
    .count(Opportunity.Id)
    .count(Opportunity.CampaignId)
    .toAggregated();

count with alias​

Signature

count(SObjectField field, String alias)

Note! To avoid the Field must be grouped or aggregated error, any default fields will be automatically removed.

You can still specify additional fields, but they should be placed after the COUNT() function in the SELECT statement.

Example

SELECT COUNT(Name) names FROM Account
SOQL.of(Account.SObjectType)
    .count(Account.Name, 'names')
    .toAggregated();

GROUPING​

grouping​

Signature

grouping(SObjectField field, String alias)

Example

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

USING SCOPE​

USING SCOPE

delegatedScope​

Filter for records delegated to another user for action. For example, a query could filter for only delegated Task records.

Signature

SOQL delegatedScope()

Example

SELECT Id
FROM Task
USING SCOPE DELEGATED
SOQL.of(Task.SObjectType)
    .delegatedScope()
    .toList();

mineScope​

Filter for records owned by the user running the query.

Signature

SOQL mineScope()

Example

SELECT Id
FROM Task
USING SCOPE MINE
SOQL.of(Account.SObjectType)
    .mineScope()
    .toList();

mineAndMyGroupsScope​

Filter for records assigned to the user running the query and the user’s queues. If a user is assigned to a queue, the user can access records in the queue. This filter applies only to the ProcessInstanceWorkItem object.

Signature

SOQL mineAndMyGroupsScope()

Example

SELECT Id
FROM Task
USING SCOPE MINE_AND_MY_GROUPS
SOQL.of(ProcessInstanceWorkItem.SObjectType)
    .mineAndMyGroupsScope()
    .toList();

myTerritoryScope​

Filter for records in the territory of the user running the query. This option is available if territory management is enabled for your organization.

Signature

SOQL myTerritoryScope()

Example

SELECT Id
FROM Opportunity
USING SCOPE MY_TERRITORY
SOQL.of(Opportunity.SObjectType)
    .myTerritoryScope()
    .toList();

myTeamTerritoryScope​

Filter for records in the territory of the team of the user running the query. This option is available if territory management is enabled for your organization.

Signature

SOQL myTeamTerritoryScope()

Example

SELECT Id
FROM Opportunity
USING SCOPE MY_TEAM_TERRITORY
SOQL.of(Opportunity.SObjectType)
    .myTeamTerritoryScope()
    .toList();

teamScope​

Filter for records assigned to a team, such as an Account team.

Signature

SOQL teamScope()

Example

SELECT Id FROM Account USING SCOPE TEAM
SOQL.of(Account.SObjectType)
    .teamScope()
    .toList();

WHERE​

whereAre​

WHERE

The condition expression in a WHERE clause of a SOQL query includes one or more field expressions. You can specify multiple field expressions in a condition expression by using logical operators.

For more details check SOQL.FilterGroup and SOQL.Filter

Signature

SOQL whereAre(FilterClause conditions)

Example

SELECT Id
FROM Account
WHERE Id = :accountId OR Name = '%MyAccount%'
SOQL.of(Account.SObjectType)
    .whereAre(SOQL.FilterGroup
        .add(SOQL.Filter.with(Account.Id).equal(accountId))
        .add(SOQL.Filter.with(Account.Name).contains('MyAccount'))
        .conditionLogic('1 OR 2')
    ).toList();

whereAre string​

Execute conditions passed as String.

Signature

SOQL whereAre(String conditions)

Example

SELECT Id
FROM Account
WHERE NumberOfEmployees >=10 AND NumberOfEmployees <= 20
SOQL.of(Account.SObjectType)
    .whereAre('NumberOfEmployees >=10 AND NumberOfEmployees <= 20')
    .toList();

conditionLogic​

Set conditions order for SOQL query. When not specify all conditions will be with AND.

Signature

SOQL conditionLogic(String order)

Example

SELECT Id
FROM Account
WHERE Name = 'Test' AND BillingCity = 'Krakow'
SOQL.of(Account.SObjectType)
    .whereAre(SOQL.Filter.with(Account.Name).equal('Test'))
    .whereAre(SOQL.Filter.with(Account.BillingCity).equal('Krakow'))
    .conditionLogic('1 OR 2')
    .toList();

anyConditionMatching​

When the conditionLogic is not specified, all conditions are joined using the AND operator by default.

To change the default condition logic, you can utilize the anyConditionMatching method, which joins conditions using the OR operator.

Signature

SOQL anyConditionMatching()

Example

SELECT Id
FROM Account
WHERE Name = 'Test' AND BillingCity = 'Krakow'
SOQL.of(Account.SObjectType)
    .whereAre(SOQL.Filter.with(Account.Name).equal('Test'))
    .whereAre(SOQL.Filter.with(Account.BillingCity).equal('Krakow'))
    .anyConditionMatching()
    .toList();

GROUP BY​

GROUP BY

groupBy​

You can use the GROUP BY option in a SOQL query to avoid iterating through individual query results. That is, you specify a group of records instead of processing many individual records.

Signature

SOQL groupBy(SObjectField field)

Example

SELECT LeadSource
FROM Lead
GROUP BY LeadSource
SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .groupBy(Lead.LeadSource)
    .toAggregated();

groupByRollup​

Signature

SOQL groupByRollup(SObjectField field)

Example

SELECT LeadSource, COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(LeadSource)
SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .count(Lead.Name, 'cnt')
    .groupByRollup(Lead.LeadSource)
    .toAggregated();

groupByCube​

Signature

SOQL groupByCube(SObjectField field)

Example

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

ORDER BY​

ORDER BY

orderBy​

Use the optional ORDER BY in a SELECT statement of a SOQL query to control the order of the query results.

Signature

SOQL orderBy(SObjectField field)
SOQL orderBy(String field)
SOQL orderBy(String field, String direction)

Example

SELECT Id
FROM Account
ORDER BY Name DESC
SOQL.of(Account.SObjectType)
    .orderBy(Account.Name)
    .sortDesc()
    .toList();

SOQL.of(Account.SObjectType)
    .orderBy('Name')
    .sortDesc()
    .toList();

SOQL.of(Account.SObjectType)
    .orderBy('Name', 'DESC')
    .toList();

Order SOQL query by parent field.

Signature

SOQL orderBy(String relationshipName, SObjectField field)

Example

SELECT Id
FROM Contact
ORDER BY Account.Name
SOQL.of(Contact.SObjectType)
    .orderBy('Account', Account.Name)
    .toList();

sortDesc​

Default order is ascending (ASC).

Signature

SOQL sortDesc()

Example

SELECT Id
FROM Account
ORDER BY Name DESC
SOQL.of(Account.SObjectType)
    .orderBy(Account.Name)
    .sortDesc()
    .toList();

nullsLast​

By default, null values are sorted first (NULLS FIRST).

Signature

SOQL nullsLast()

Example

SELECT Id
FROM Account
ORDER BY Name NULLS LAST
SOQL.of(Account.SObjectType)
    .orderBy(Account.Industry)
    .nullsLast()
    .toList();

LIMIT​

setLimit​

LIMIT is an optional clause that can be added to a SELECT statement of a SOQL query to specify the maximum number of rows to return.

Signature

SOQL setLimit(Integer amount)

Example

SELECT Id
FROM Account
LIMIT 100
SOQL.of(Account.SObjectType)
    .setLimit(100)
    .toList();

OFFSET​

offset​

When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause on a SOQL query.

Signature

SOQL offset(Integer startingRow)

Example

SELECT Id
FROM Account
OFFSET 10
SOQL.of(Account.SObjectType)
    .setOffset(10)
    .toList();

FOR​

forReference​

Use to notify Salesforce when a record is referenced from a custom interface, such as in a mobile application or from a custom page.

Signature

SOQL forReference()

Example

SELECT Id
FROM Contact
FOR REFERENCE
SOQL.of(Contact.SObjectType)
    .forReference()
    .toList();

forView​

Use to update objects with information about when they were last viewed.

Signature

SOQL forView()

Example

SELECT Id
FROM Contact
FOR VIEW
SOQL.of(Contact.SObjectType)
    .forView()
    .toList();

forUpdate​

Use to lock sObject records while they’re being updated in order to prevent race conditions and other thread safety problems.

Signature

SOQL forUpdate()

Example

SELECT Id
FROM Contact
FOR UPDATE
SOQL.of(Contact.SObjectType)
    .forUpdate()
    .toList();

allRows​

SOQL statements can use the ALL ROWS keywords to query all records in an organization, including deleted records and archived activities.

Signature

SOQL allRows()

Example

SELECT COUNT()
FROM Contact
ALL ROWS
SOQL.of(Contact.SObjectType)
    .count()
    .allRows()
    .toList();

FIELD-LEVEL SECURITY​

AccessLevel Class

By default AccessLevel is set as USER_MODE.

More details you can find in here

systemMode​

Execution mode in which the the object and field-level permissions of the current user are ignored, and the record sharing rules are controlled by the class sharing keywords.

Signature

SOQL systemMode()

Example

SOQL.of(Account.SObjectType)
    .systemMode()
    .toList();

stripInaccessible​

USER_MODE enforces not only object and field-level security but also sharing rules (with sharing). You may encounter situations where you need object and field-level security but want to ignore sharing rules (without sharing). To achieve this, use .systemMode(), .withoutSharing() and .stripInaccessible().

Read more about stripInaccessible in advanced.

Signature

SOQL stripInaccessible()
SOQL stripInaccessible(AccessType accessType)
SOQL.of(Account.SObjectType)
    .systemMode()
    .withoutSharing()
    .stripInaccessible()
    .toList();

SHARING MODE​

Using the with sharing, without sharing, and inherited sharing Keywords

More details you can find in here.

withSharing​

Execute query with sharing.

Note! System mode needs to be enabled by .systemMode().

Signature

SOQL withSharing()

Example

SOQL.of(Account.SObjectType)
    .systemMode()
    .withSharing()
    .toList();

withoutSharing​

Execute query without sharing.

Note! System mode needs to be enabled by .systemMode().

Signature

SOQL withoutSharing()

Example

SOQL.of(Account.SObjectType)
    .systemMode()
    .withoutSharing()
    .toList();

MOCKING​

mockId​

Query needs unique id that allows for mocking.

Signature

SOQL mockId(String queryIdentifier)

Example

SOQL.of(Account.SObjectType)
    .mockId('MyQuery')
    .toList();

// In Unit Test
SOQL.setMock('MyQuery', new List<Account>{
    new Account(Name = 'MyAccount 1'),
    new Account(Name = 'MyAccount 2')
});

record mock​

Signature

SOQL setMock(String mockId, SObject record)

Example

SOQL.of(Account.sObjectType)
    .mockId('MyQuery')
    .toList();

// In Unit Test
SOQL.setMock('MyQuery', new Account(Name = 'MyAccount 1'));

list mock​

Signature

SOQL setMock(String mockId, List<SObject> records)

Example

SOQL.of(Account.sObjectType)
    .mockId('MyQuery')
    .toList();

// In Unit Test
SOQL.setMock('MyQuery', new List<Account>{
    new Account(Name = 'MyAccount 1'),
    new Account(Name = 'MyAccount 2')
});

count mock​

Signature

SOQL setCountMock(String mockId, Integer amount)

Example

SOQL.of(Account.sObjectType)
    .mockId('MyQuery')
    .count()
    .toInteger();

// In Unit Test
SOQL.setMock('MyQuery', 5);

DEBUGGING​

preview​

Signature

SOQL preview()

Example

SOQL.of(Account.SObjectType)
    .preview()
    .toList();

Query preview will be available in debug logs:

============ Query Preview ============
SELECT Name, AccountNumber, BillingCity, BillingCountry, BillingCountryCode
FROM Account
WHERE ((Id = :v1 OR Name LIKE :v2))
=======================================

============ Query Binding ============
{
  "v2" : "%Test%",
  "v1" : "0013V00000WNCw4QAH"
}
=======================================

PREDEFINIED​

For all predefined methods SOQL instance is returned so you can still adjust query before execution. Add additional fields with .with.

byId​

Signature

SOQL byId(Id recordId)
SOQL byId(SObject record)

Example

SELECT Id
FROM Account
WHERE Id = '1234'
SOQL.of(Account.SObjectType)
    .byId('1234')
    .toObject();
Account account = [SELECT Id FROM Account LIMIT 1];
SOQL.of(Account.SObjectType)
    .byId(account)
    .toList();

byIds​

Signature

SOQL byIds(Iterable<Id> recordIds)
SOQL byIds(List<SObject> records)

Example

SELECT Id
FROM Account
WHERE Id IN ('1234')
SOQL.of(Account.SObjectType)
    .byIds(new Set<Id>{ '1234' })
    .toList();
SOQL.of(Account.SObjectType)
    .byIds(new List<Id>{ '1234' })
    .toList();
List<Account> accounts = [SELECT Id FROM Account];
SOQL.of(Account.SObjectType)
    .byIds(accounts)
    .toList();

RESULT​

doExist​

Boolean doExist()

Example

Boolean isRecordExist = SOQL.of(Account.SObjectType).byId('1234').doExist();

toValueOf​

Extract field value from query result. Field will be automatically added to the query fields.

Signature

Object toValueOf(SObjectField fieldToExtract)

Example

String accountName = (String) SOQL.of(Account.SObjectType).byId('1234').toValueOf(Account.Name)

toValuesOf​

Extract field values from query result. Field will be automatically added to the query fields.

SOQL Lib is using Building a KeySet from any field approach to get only one field.

Note! It does not work with Custom Metadata.

Signature

Set<String> toValuesOf(SObjectField fieldToExtract)

Example

Set<String> accountNames = SOQL.of(Account.SObjectType).byId('1234').toValuesOf(Account.Name)

toInteger​

Signature

Integer toInteger()

Example

SELECT COUNT() FROM Account
SOQL.of(Account.SObjectType).count().toInteger();

toObject​

When list of records is greater than 1 the List has more than 1 row for assignment to SObject will occur.

When there is no record to assign the List has no rows for assignment to SObject will occur.

Signature

sObject toObject()

Example

SOQL.of(Account.SObjectType).toObject();

toList​

Signature

List<sObject> toList()

Example

SOQL.of(Account.SObjectType).toList();

toAggregated​

Signature

List<AggregateResult> toAggregated()

Example

SELECT LeadSource
FROM Lead
GROUP BY LeadSource
SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .groupBy(Lead.LeadSource)
    .toAggregated()

toMap​

Signature

Map<Id, SObject> toMap()

Example

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

toMap with custom key​

Signature

Map<String, SObject> toMap(SObjectField keyField)

Example

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

toMap with custom key and value​

Signature

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

Example

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

toAggregatedMap​

Signature

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

Example

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

toAggregatedMap with custom value​

Signature

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

Example

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

toQueryLocator​

Signature

Database.QueryLocator toQueryLocator()

Example

SOQL.of(Account.SObjectType).toQueryLocator();