Skip to main content

SOQL

Apex Classes: SOQL.cls and SOQL_Test.cls.

The lib main class for query construction.

Basic Usage Example
SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .toList();

Methods​

The following are methods for using SOQL:

INIT

SELECT

AGGREGATION FUNCTIONS

GROUPING

toLabel

format

SUBQUERY

USING SCOPE

WHERE

GROUP BY

WITH DATA_CATEGORY

HAVING

ORDER BY

LIMIT

OFFSET

FOR

FIELD-LEVEL SECURITY

SHARING MODE

MOCKING

DEBUGGING

PREDEFINIED

RESULT

INIT​

of​

Constructs an SOQL.

Signature

Method Signatures
Queryable of(SObjectType ofObject)
Queryable of(String ofObject)

Example

SOQL Query
SELECT Id FROM Account
SOQL Lib Implementation
SOQL.of(Account.SObjectType).toList();
SOQL.of('Account').toList();

SELECT​

with field1 - field5​

Signature

Single Field Method
Queryable with(SObjectField field)
Two Fields Method
Queryable with(SObjectField field1, SObjectField field2);
Three Fields Method
Queryable with(SObjectField field1, SObjectField field2, SObjectField field3);
Four Fields Method
Queryable with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4);
Five Fields Method
Queryable with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5);

Example

SOQL Query
SELECT Id, Name
FROM Account
SOQL Lib Field Selection
SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .toList();

// or

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

Method Signatures
Queryable with(List<SObjectField> fields)
Queryable with(List<String> fields)

Example

SOQL Query
SELECT Id, Name, Industry, AccountNumber, AnnualRevenue, BillingCity
FROM Account
SOQL Lib with List of SObjectFields
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

Method Signature
Queryable with(String fields)

Example

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

with related field1 - field5​

Allows to add parent field to a query.

Signature

Single Related Field Method
Queryable with(String relationshipName, SObjectField field)
Two Related Fields Method
Queryable with(String relationshipName, SObjectField field1, SObjectField field2);
Three Related Fields Method
Queryable with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3);
Four Related Fields Method
Queryable with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4);
Five Related Fields Method
Queryable with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5);

Example

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

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

Allows to add parent fields to a query.

Use for more than 5 parent fields.

Signature

Method Signature
Queryable with(String relationshipName, Iterable<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();

with field set​

Pass FieldSet name to get dynamic fields.

Signature

Queryable withFieldSet(String fieldSetName)

Example

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

SUB-QUERY​

with subquery​

Using Relationship Queries

Use SOQL to query several relationship types.

For more details check SOQL.SubQuery class.

Signature

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

AGGREGATE FUNCTIONS​

Note! To avoid the Field must be grouped or aggregated error, any default fields that are neither in Aggregation Functions nor included in the GROUP BY clause will be automatically removed.

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

Queryable count()

Example

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

// or

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

Queryable count(SObjectField field, String alias)

Example

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

Signature

count(String relationshipName, SObjectField field)

Example

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

Signature

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

Example

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

avg​

Signature

Queryable avg(SObjectField field)

Example

SELECT CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId
SOQL.of(Opportunity.SObjectType)
    .with(Opportunity.CampaignId)
    .avg(Opportunity.Amount)
    .groupBy(Opportunity.CampaignId)
    .toAggregate();

avg with alias​

Signature

Queryable avg(SObjectField field, String alias)

Example

SELECT CampaignId, AVG(Amount) amount FROM Opportunity GROUP BY CampaignId
SOQL.of(Opportunity.SObjectType)
    .with(Opportunity.CampaignId)
    .avg(Opportunity.Amount, 'amount')
    .groupBy(Opportunity.CampaignId)
    .toAggregate();

Signature

avg(String relationshipName, SObjectField field)

Example

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

Signature

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

Example

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

count_distinct​

Signature

Queryable countDistinct(SObjectField field

Example

SELECT COUNT_DISTINCT(Company) FROM Lead
SOQL.of(Lead.SObjectType)
    .countDistinct(Lead.Company)
    .toAggregate();

count_distinct with alias​

Signature

Queryable countDistinct(SObjectField field, String alias)

Example

SELECT COUNT_DISTINCT(Company) company FROM Lead
SOQL.of(Lead.SObjectType)
    .countDistinct(Lead.Company, 'company')
    .toAggregate();

Signature

Queryable countDistinct(String relationshipName, SObjectField field)

Example

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

Signature

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

Example

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

min​

Signature

Queryable min(SObjectField field)

Example

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

min with alias​

Signature

Queryable min(SObjectField field, String alias)

Example

SELECT FirstName, LastName, MIN(CreatedDate) createDate
FROM Contact
GROUP BY FirstName, LastName
SOQL.of(Contact.SObjectType)
    .with(Contact.FirstName, Contact.LastName)
    .min(Contact.CreatedDate, 'createDate')
    .groupBy(Contact.FirstName)
    .groupBy(Contact.LastName)
    .toAggregate();

Signature

Queryable min(String relationshipName, SObjectField field)

Example

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

Signature

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

Example

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

max​

Signature

Queryable max(SObjectField field)

Example

SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name
 SOQL.of(Campaign.SObjectType)
    .with(Campaign.Name)
    .max(Campaign.BudgetedCost)
    .groupBy(Campaign.Name)
    .toAggregate();

max with alias​

Signature

Queryable max(SObjectField field, String alias)

Example

SELECT Name, MAX(BudgetedCost) budgetedCost
FROM Campaign
GROUP BY Name
 SOQL.of(Campaign.SObjectType)
    .with(Campaign.Name)
    .max(Campaign.BudgetedCost, 'budgetedCost')
    .groupBy(Campaign.Name)
    .toAggregate();

Signature

Queryable max(String relationshipName, SObjectField field)

Example

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

Signature

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

Example

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

sum​

Signature

Queryable sum(SObjectField field)

Example

SELECT SUM(Amount) FROM Opportunity
SOQL.of(Opportunity.SObjectType)
    .sum(Opportunity.Amount)
    .toAggregate();

sum with alias​

Signature

Queryable sum(SObjectField field, String alias)

Example

SELECT SUM(Amount) amount FROM Opportunity
SOQL.of(Opportunity.SObjectType)
    .sum(Opportunity.Amount, 'amount')
    .toAggregate();

GROUPING​

grouping​

Signature

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

Signature

sum(String relationshipName, SObjectField field)

Example

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

Signature

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

Example

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

toLabel​

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

Signature

Queryable toLabel(SObjectField field)
Queryable toLabel(SObjectField field, String alias)
Queryable toLabel(String field)
Queryable toLabel(String field, String alias)

Example

SELECT Company, toLabel(Status) FROM Lead
SOQL.of(Lead.SObjectType)
    .with(Lead.Company)
    .toLabel(Lead.Status)
    .toList();
SELECT Company, toLabel(Status) leadStatus FROM Lead
SOQL.of(Lead.SObjectType)
    .with(Lead.Company)
    .toLabel(Lead.Status, 'leadStatus')
    .toList();
SELECT Company, toLabel(Recordtype.Name) FROM Lead
SOQL.of(Lead.SObjectType)
    .with(Lead.Company)
    .toLabel('Recordtype.Name')
    .toList();
SELECT Company, toLabel(Recordtype.Name) recordTypeName FROM Lead
SOQL.of(Lead.SObjectType)
    .with(Lead.Company)
    .toLabel('Recordtype.Name', 'recordTypeName')
    .toList();

format​

Signature

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

Example

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

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

Queryable 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

Queryable mineScope()

Example

SELECT Id
FROM Account
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

Queryable mineAndMyGroupsScope()

Example

SELECT Id
FROM ProcessInstanceWorkItem
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

Queryable 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

Queryable 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

Queryable 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

Queryable 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'))
        .anyConditionMatching()
    ).toList();

whereAre string​

Execute conditions passed as String.

Signature

Queryable 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

Queryable 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

Queryable 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

Queryable groupBy(SObjectField field)

Example

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

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​

Signature

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

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​

Signature

Queryable groupByCube(SObjectField field)

Example

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

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

WITH DATA_CATEGORY​

For more details check SOQL.DataCategoryFilter.

Signature

 Queryable withDataCategory(DataCategoryFilter dataCategoryFilter);

Example

SELECT Id, Title
FROM Knowledge__kav
WITH DATA CATEGORY Geography__c AT (Europe__c, North_America__c)
SOQL.of(Knowledge__kav.SObjectType)
    .with(Knowledge__kav.Id, Knowledge__kav.Title)
    .withDataCategory(SOQL.DataCategoryFilter.with('Geography__c').at(new List<String>{ 'Europe__c', 'North_America__c' }))
    .toList();

HAVING​

have​

HAVING

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

For more details check SOQL.HavingFilterGroup and SOQL.HavingFilter

Signature

Queryable have(HavingFilterGroup havingFilterGroup)
Queryable have(HavingFilter havingFilter)

Example

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource, City
HAVING COUNT(Name) > 100 AND City LIKE 'San%'
SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .count(Lead.Name)
    .groupBy(Lead.LeadSource)
    .groupBy(Lead.City)
    .have(SOQL.HavingFilter.count(Lead.Name).greaterThan(100))
    .have(SOQL.HavingFilter.with(Lead.City).startsWith('San'))
    .toAggregated();

having string​

Execute having conditions passed as String.

Signature

Queryable have(String conditions)

Example

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100 AND COUNT(Name) < 200
SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .count(Lead.Name)
    .groupBy(Lead.LeadSource)
    .have('COUNT(Name) > 100 AND COUNT(Name) < 200')
    .toAggregated();

havingConditionLogic​

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

Signature

Queryable havingConditionLogic(String order)

Example

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource, City
HAVING COUNT(Name) > 100 OR City LIKE 'San%'
 SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .count(Lead.Name)
    .groupBy(Lead.LeadSource)
    .groupBy(Lead.City)
    .have(SOQL.HavingFilter.count(Lead.Name).greaterThan(100))
    .have(SOQL.HavingFilter.with(Lead.City).startsWith('San'))
    .havingConditionLogic('1 OR 2')
    .toAggregated();

anyHavingConditionMatching​

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

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

Signature

Queryable anyHavingConditionMatching()

Example

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource, City
HAVING COUNT(Name) > 100 OR City LIKE 'San%'
SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .count(Lead.Name)
    .groupBy(Lead.LeadSource)
    .groupBy(Lead.City)
    .have(SOQL.HavingFilter.count(Lead.Name).greaterThan(100))
    .have(SOQL.HavingFilter.with(Lead.City).startsWith('San'))
    .anyHavingConditionMatching()
    .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

Queryable orderBy(SObjectField field)
Queryable orderBy(String field)
Queryable 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

Queryable orderBy(String relationshipName, SObjectField field)

Example

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

orderBy COUNT​

Signature

Queryable orderByCount(SObjectField field)

Example

SELECT Industry
FROM Account
GROUP BY Industry
ORDER BY COUNT(Industry) DESC NULLS LAST
SOQL.of(Account.SObjectType)
    .with(Account.Industry)
    .groupBy(Account.Industry)
    .orderByCount(Account.Industry).sortDesc().nullsLast()
    .toAggregated();

sortDesc​

Default order is ascending (ASC).

Signature

Queryable sortDesc()

Example

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

sort​

Use ONLY for dynamic order.

Signature

Queryable sort(String direction)

Example

SELECT Id
FROM Account
ORDER BY Industry ASC NULLS FIRST
SOQL.of(Account.SObjectType)
    .orderBy('Industry')
    .sort('DESC')
    .toList();

nullsLast​

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

Signature

Queryable nullsLast()

Example

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

nulls order​

To build dynamic order - use nullsOrder method.

Signature

Queryable nullsOrder(String order)

Example

SELECT Id
FROM Account
ORDER BY Name NULLS LAST
String order = 'LAST';

SOQL.of(Account.SObjectType)
    .orderBy(Account.Industry)
    .nullsOrder(order)
    .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

Queryable 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

Queryable 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

Queryable 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

Queryable 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

Queryable 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

Queryable 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

userMode​

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.

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

Signature

Queryable userMode()

Example

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

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

Queryable 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

Queryable stripInaccessible()
Queryable 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

Queryable withSharing()

Example

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

withoutSharing​

Execute query without sharing.

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

Signature

Queryable withoutSharing()

Example

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

MOCKING​

mockId​

Query needs unique id that allows for mocking.

Signature

Queryable mockId(String queryIdentifier)

Example

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

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

record mock​

Signature

SOQL.Mockable mock(String mockId).thenReturn(SObject record)

Example

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

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

list mock​

Signature

SOQL.Mockable mock(String mockId).thenReturn(List<SObject> records)

Example

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

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

count mock​

Signature

SOQL.Mockable mock(String mockId).thenReturn(Integer amount)

Example

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

// In Unit Test
SOQL.mock('MyQuery).thenReturn(5);

aggregateResult mock​

Signature

SOQL.Mockable mock(String mockId).thenReturn(List<Map<String, Object>> mock);
SOQL.Mockable mock(String mockId).thenReturn(Map<String, Object> mock);

Example

List<Map<String, Object>> aggregateResults = new List<Map<String, Object>>{
    new Map<String, Object>{ 'LeadSource' => 'Web',  'total' => 10},
    new Map<String, Object>{ 'LeadSource' => 'Phone', 'total' => 5},
    new Map<String, Object>{ 'LeadSource' => 'Email', 'total' => 3}
};

SOQL.mock('mockingQuery').thenReturn(aggregateResults);

List<SOQL.AggregateResultProxy> result = SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .COUNT(Lead.Id, 'total')
    .groupBy(Lead.LeadSource)
    .mockId('mockingQuery')
    .toAggregatedProxy();

SObjectType mock​

Create a mock using SObjectType instead of a string identifier. This method automatically generates a hash-based mock ID from the SObjectType.

Signature

SOQL.Mockable mock(SObjectType ofObject)

Example

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

// In Unit Test - Mock records using SObjectType
SOQL.mock(Account.SObjectType).thenReturn(new List<Account>{
    new Account(Name = 'Test Account 1'),
    new Account(Name = 'Test Account 2')
});

// Mock single record using SObjectType  
SOQL.mock(Contact.SObjectType).thenReturn(
    new Contact(FirstName = 'John', LastName = 'Doe')
);

// Mock count using SObjectType
SOQL.mock(Lead.SObjectType).thenReturn(10);

Note! When using mock(SObjectType), the library automatically generates a mock identifier based on the SObjectType's hash code. This is convenient when you don't need to specify custom mock IDs and want to mock all queries for a specific SObject type.

exception mock​

Mock a query exception with default message.

Signature

SOQL.Mockable mock(String mockId).throwException()

Example

// In Unit Test
SOQL.mock('MyQuery').throwException();

Test.startTest();
Exception error;
try {
    Account result = SOQL.of(Account.SObjectType)
        .mockId('MyQuery')
        .toObject();
} catch (Exception e) {
    error = e;
}
Test.stopTest();

Assert.isNotNull(error, 'The query exception should be thrown.');

exception mock with message​

Mock a query exception with custom error message.

Signature

SOQL.Mockable mock(String mockId).throwException(String message)

Example

// In Unit Test
String errorMessage = 'No such column \'InvalidField__c\' on entity \'Account\'.';
SOQL.mock('MyQuery').throwException(errorMessage);

Test.startTest();
Exception error;
try {
    Account result = SOQL.of(Account.SObjectType)
        .mockId('MyQuery')
        .toObject();
} catch (Exception e) {
    error = e;
}
Test.stopTest();

Assert.isNotNull(error, 'The query exception should be thrown.');
Assert.isTrue(error.getMessage().contains('InvalidField__c'));

DEBUGGING​

preview​

Signature

Queryable 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

Queryable byId(Id recordId)
Queryable 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)
    .toObject();

byIds​

Signature

Queryable byIds(Iterable<Id> recordIds)
Queryable 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();

byRecordType​

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

Signature

Queryable byRecordType(String recordTypeDeveloperName)

Example

SELECT Id
FROM Account
WHERE RecordType.DeveloperName = 'Partner'
SOQL.of(Account.SObjectType)
    .byRecordType('Partner')
    .toList();

RESULT​

toId​

Id toId()

Example

Id adminProfileId = SOQL.of(Profile.SObjectType)
    .whereAre(SOQL.Filter.name().equal('System Administrator'))
    .toId();

new User (
    // ...
    ProfileId = adminProfileId
);

toIds​

Extract all record IDs from query result.

Signature

Set<Id> toIds()

Example

Set<Id> accountIds = SOQL.of(Account.SObjectType)
    .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
    .toIds();

toIdsOf​

Extract field values as Set of IDs from query result. Field will be automatically added to the query fields.

Signature

Set<Id> toIdsOf(SObjectField field)

Example

Set<Id> ownerIds = SOQL.of(Account.SObjectType)
    .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
    .toIdsOf(Account.OwnerId);

Signature

Set<Id> toIdsOf(String relationshipName, SObjectField field)

Example

Set<Id> parentAccountIds = SOQL.of(Account.SObjectType)
    .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
    .toIdsOf('Parent', Account.Id);

doExist​

Signature

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)

toValuesOf Releated Field​

Signature

Set<String> toValuesOf(String relationshipName, SObjectField targetKeyField)

Example

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

toInteger​

Signature

Integer toInteger()

Example

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

toObject​

When the list of records contains more than one entry, the error List has more than 1 row for assignment to SObject will occur.

When there are no records to assign, the error List has no rows for assignment to SObject will NOT occur. This is automatically handled by the framework, and a null value will be returned instead.

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​

Note! To improve query performance, a condition checking if the keyField is not null (WHERE keyField != null) is automatically added to the query.

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 relationship key​

Note! To improve query performance, a condition checking if the targetKeyField is not null (WHERE relationshipName.targetKeyField != null) is automatically added to the query.

Signature

Map<String, SObject> toMap(String relationshipName, SObjectField targetKeyField)

Example

Map<String, Account> parentCreatedByEmailToAccount = (Map<String, Account>) SOQL.of(Account.SObjectType).toMap('Parent.CreatedBy', User.Email);

toMap with custom key and value​

Note! To improve query performance, a condition checking if the keyField is not null (WHERE keyField != null) is automatically added to the query.

Signature

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

Example

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

toAggregatedMap​

Note! To improve query performance, a condition checking if the keyField is not null (WHERE keyField != null) is automatically added to the query.

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​

Note! To improve query performance, a condition checking if the keyField is not null (WHERE keyField != null) is automatically added to the query.

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

toAggregatedMap with custom relationship key​

Note! To improve query performance, a condition checking if the targetKeyField is not null (WHERE relationshipName.targetKeyField != null) is automatically added to the query.

Signature

Map<String, List<SObject>> toAggregatedMap(String relationshipName, SObjectField targetKeyField)

Example

Map<String, List<Account>> parentCreatedByEmailToAccounts = (Map<String, List<Account>>) SOQL.of(Account.SObjectType).toAggregatedMap('Parent.CreatedBy', User.Email);

toIdMapBy​

Creates a map where the key is the Id extracted from the specified field and the value is the SObject record.

Note! To improve query performance, a condition checking if the field is not null (WHERE field != null) is automatically added to the query.

Signature

Map<Id, SObject> toIdMapBy(SObjectField field)

Example

Map<Id, Account> ownerIdToAccount = (Map<Id, Account>) SOQL.of(Account.SObjectType).toIdMapBy(Account.OwnerId);

toIdMapBy with relationship​

Creates a map where the key is the Id extracted from the specified relationship field and the value is the SObject record.

Note! To improve query performance, a condition checking if the targetKeyField is not null (WHERE relationshipName.targetKeyField != null) is automatically added to the query.

Signature

Map<Id, SObject> toIdMapBy(String relationshipName, SObjectField targetKeyField)

Example

Map<Id, Account> parentIdToAccount = (Map<Id, Account>) SOQL.of(Account.SObjectType).toIdMapBy('Parent', Account.Id);

toAggregatedIdMapBy​

Creates a map where the key is the Id extracted from the specified field and the value is a list of SObject records grouped by that Id.

Note! To improve query performance, a condition checking if the keyField is not null (WHERE keyField != null) is automatically added to the query.

Signature

Map<Id, List<SObject>> toAggregatedIdMapBy(SObjectField keyField)

Example

Map<Id, List<Account>> ownerIdToAccounts = (Map<Id, List<Account>>) SOQL.of(Account.SObjectType).toAggregatedIdMapBy(Account.OwnerId);

toAggregatedIdMapBy with relationship​

Creates a map where the key is the Id extracted from the specified relationship field and the value is a list of SObject records grouped by that Id.

Note! To improve query performance, a condition checking if the targetKeyField is not null (WHERE relationshipName.targetKeyField != null) is automatically added to the query.

Signature

Map<Id, List<SObject>> toAggregatedIdMapBy(String relationshipName, SObjectField targetKeyField)

Example

Map<Id, List<Account>> parentIdToAccounts = (Map<Id, List<Account>>) SOQL.of(Account.SObjectType).toAggregatedIdMapBy('Parent', Account.Id);

toQueryLocator​

Signature

Database.QueryLocator toQueryLocator()

Example

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

toCursor​

Returns a Database.Cursor for the query, which can be used for more efficient processing of large result sets with streaming capabilities.

Signature

Database.Cursor toCursor()

Example

Database.Cursor cursor = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
    .toCursor();

toPaginationCursor​

Returns a Database.PaginationCursor for the query, which enables efficient pagination through large result sets with built-in offset management.

Signature

Database.PaginationCursor toPaginationCursor()

Example

Database.PaginationCursor paginationCursor = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name, Account.Industry)
    .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
    .orderBy(Account.Name)
    .setLimit(100)
    .toPaginationCursor();