soql-lib-query-builder

name	soql-lib-query-builder
description	Builds Salesforce SOQL queries using the SOQL Lib fluent builder API (SOQL.cls). Covers SELECT fields, toLabel, format, WHERE filters, USING SCOPE, FOR clauses, WITH DATA CATEGORY, ORDER BY, LIMIT, OFFSET, aggregate functions, GROUP BY / HAVING, all result methods (toList, toObject, toMap, toIds, toAggregated, toCursor, etc.) and security modes. Use when writing any SOQL query with SOQL Lib.

SOQL Lib — Query Builder

SOQL Lib is a fluent Apex builder for SOQL queries. Every method returns Queryable, enabling chaining. All filter values are automatically bound — never string-concatenated.

When to Use

• Use this skill when writing a new SOQL query
• Use this skill when choosing the right result method (toList, toObject, toMap, etc.)
• Use this skill when building WHERE conditions with SOQL.Filter or SOQL.FilterGroup
• Use this skill when working with aggregate queries, sub-queries, or parent field traversal

Instructions

Entry point

Always use SOQL.of(SObjectType) when no selector exists. Prefer SObjectType over String for compile-time safety:

// If a selector exists — prefer it
SOQL_Account.query().with(Account.Id, Account.Name).toList();

// No selector — use inline
SOQL.of(Account.SObjectType).with(Account.Id, Account.Name).toList();
SOQL.of('Account').toList(); // dynamic only

SELECT fields

.with(Account.Id, Account.Name, Account.Industry)               // up to 5 fields
.with(Account.Id)                                               // single field, can be chained
.with(new List<SObjectField>{ Account.Id, Account.Name, ... })  // 6+ fields
.with('CreatedBy', User.Name)                                   // parent field (up to 5)
.with('CreatedBy', User.Id, User.Name, User.Email)              // multiple parent fields
.with('CreatedBy', new List<SObjectField>{ User.Id, User.Name, User.Phone, User.FirstName, User.LastName, User.Email }) // 6+ parent fields
.with(SOQL.SubQuery.of('Contacts').with(Contact.Id, Contact.Name)) // child sub-query
.withFieldSet('AccountFieldSet')                                // Field Set
.with('Id, Name, Industry')                                     // dynamic String — avoid when possible

toLabel — translate field value to user language

.toLabel(Lead.Status)                         // SELECT toLabel(Status)
.toLabel(Lead.Status, 'leadStatus')           // SELECT toLabel(Status) leadStatus
.toLabel('Recordtype.Name')                   // SELECT toLabel(Recordtype.Name)
.toLabel('Recordtype.Name', 'recordTypeName') // SELECT toLabel(Recordtype.Name) recordTypeName

format — apply locale formatting to a field

.format(Opportunity.Amount)           // SELECT FORMAT(Amount)
.format(Opportunity.Amount, 'amt')    // SELECT FORMAT(Amount) amt

WHERE — single condition

.whereAre(SOQL.Filter.id().equal(recordId))
.whereAre(SOQL.Filter.recordType().equal('Partner'))
.whereAre(SOQL.Filter.name().contains('Acme'))
.whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
.whereAre(SOQL.Filter.with('CreatedBy', User.Email).equal('[e-mail]'))

WHERE — compound conditions

// AND (default)
.whereAre(SOQL.FilterGroup
    .add(SOQL.Filter.with(Account.Industry).equal('Technology'))
    .add(SOQL.Filter.with(Account.BillingCity).equal('Krakow'))
)

// OR
.whereAre(SOQL.FilterGroup
    .add(SOQL.Filter.with(Account.Id).equal(id))
    .add(SOQL.Filter.with(Account.Name).contains('Test'))
    .anyConditionMatching()
)

// Custom logic — e.g. 1 AND (2 OR 3)
.whereAre(SOQL.Filter.with(Account.Name).equal('A'))
.whereAre(SOQL.Filter.with(Account.BillingCity).equal('Krakow'))
.whereAre(SOQL.Filter.with(Account.Industry).equal('Tech'))
.conditionLogic('1 AND (2 OR 3)')

// Top-level OR connector
.anyConditionMatching()

WHERE — filter comparators

Method	SQL equivalent
.equal(val)	= :v
.notEqual(val)	!= :v
.isNull() / .isNotNull()	= null / != null
.isTrue() / .isFalse()	= true / = false
.lessThan(val) / .greaterThan(val)	< :v / > :v
.lessOrEqual(val) / .greaterOrEqual(val)	<= :v / >= :v
.contains('txt')	LIKE '%txt%'
.startsWith('txt')	LIKE 'txt%'
.endsWith('txt')	LIKE '%txt'
.notContains('txt')	(NOT ... LIKE '%txt%')
.isIn(new List<Object>{...})	IN (:v)
.notIn(new List<Object>{...})	NOT IN (:v)
.isIn(SOQL.InnerJoin...)	IN (SELECT ...)
.isIn(records, Account.OwnerId)	IN from SObject list
.includesAll(vals) / .includesSome(vals)	INCLUDES (multi-picklist)
.asDateLiteral()	unbound — for THIS_MONTH, LAST_N_DAYS:30, etc.

WHERE — modifiers

// Skip condition when expression is true
.whereAre(SOQL.Filter.with(Account.Industry).equal(industry).ignoreWhen(industry == null))

// Skip entire group
SOQL.FilterGroup.add(...).ignoreWhen(filters.isEmpty())

// Date literals (cannot be bound)
.whereAre(SOQL.Filter.with(Opportunity.CloseDate).equal('THIS_MONTH').asDateLiteral())
.whereAre(SOQL.Filter.with(Account.CreatedDate).greaterThan('LAST_N_DAYS:30').asDateLiteral())

WHERE — predefined shortcuts

.byId(recordId)                       // WHERE Id = :id
.byId(record)                         // WHERE Id = :record.Id
.byIds(new Set<Id>{ id1, id2 })       // WHERE Id IN (...)
.byIds(new List<Id>{ id1, id2 })      // WHERE Id IN (...)
.byIds(records)                       // WHERE Id IN (ids from list)
.byRecordType('Partner')              // WHERE RecordType.DeveloperName = 'Partner'

Semi-join (InnerJoin)

.whereAre(SOQL.Filter.with(Contact.AccountId).isIn(
    SOQL.InnerJoin.of(Account.SObjectType)
        .with(Account.Id)
        .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
))

USING SCOPE

Scope the query to a subset of records based on the running user's context:

.mineScope()                // USING SCOPE MINE — records owned by current user
.delegatedScope()           // USING SCOPE DELEGATED — delegated records
.mineAndMyGroupsScope()     // USING SCOPE MINE_AND_MY_GROUPS — ProcessInstanceWorkItem only
.myTerritoryScope()         // USING SCOPE MY_TERRITORY — requires territory management
.myTeamTerritoryScope()     // USING SCOPE MY_TEAM_TERRITORY — requires territory management
.teamScope()                // USING SCOPE TEAM — account team records

FOR clauses

.forReference()   // FOR REFERENCE — notify when record is referenced from custom UI
.forView()        // FOR VIEW — update LastViewedDate
.forUpdate()      // FOR UPDATE — lock records during update
.allRows()        // ALL ROWS — include deleted records and archived activities

WITH DATA CATEGORY

For Knowledge articles and objects with data categories:

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

ORDER BY / LIMIT / OFFSET

.orderBy(Account.Name)                    // ASC NULLS FIRST (default)
.orderBy(Account.Name).sortDesc()         // DESC
.orderBy(Account.Name).nullsLast()        // NULLS LAST
.orderBy('Account', Account.Name)         // parent field
.orderBy('Name', 'DESC')                  // dynamic direction — use only for dynamic
.sort('DESC')                             // dynamic direction on last orderBy
.nullsOrder('LAST')                       // dynamic nulls order
.orderByCount(Account.Industry).sortDesc().nullsLast() // ORDER BY COUNT(field)
.setLimit(100)
.offset(20)

Aggregate functions

.count()                                    // SELECT COUNT() — use with toInteger()
.count(Opportunity.Id, 'cnt')              // COUNT(Id) cnt
.count('Account', Account.Name, 'names')   // COUNT(Account.Name) names
.avg(Opportunity.Amount, 'avgAmt')         // AVG(Amount) avgAmt
.avg('Opportunity', Opportunity.Amount)     // AVG(Opportunity.Amount)
.sum(Opportunity.Amount, 'total')          // SUM(Amount) total
.sum('Opportunity', Opportunity.Amount)     // SUM(Opportunity.Amount)
.min(Contact.CreatedDate)                  // MIN(CreatedDate)
.min('Account', Account.CreatedDate, 'createdDate') // MIN(Account.CreatedDate) createdDate
.max(Campaign.BudgetedCost)               // MAX(BudgetedCost)
.max('Campaign', Campaign.BudgetedCost)   // MAX(Campaign.BudgetedCost)
.countDistinct(Lead.Company)              // COUNT_DISTINCT(Company)
.countDistinct('Lead', Lead.Company, 'company') // COUNT_DISTINCT(Lead.Company) company
.grouping(Lead.LeadSource, 'grpLS')       // GROUPING — for ROLLUP/CUBE

Note: COUNT() must be the only element in the SELECT list — other fields are removed automatically. Other aggregate functions auto-remove non-grouped default fields to avoid Field must be grouped or aggregated.

GROUP BY / HAVING

SOQL.of(Lead.SObjectType)
    .with(Lead.LeadSource)
    .count(Lead.Name, 'cnt')
    .groupBy(Lead.LeadSource)
    .have(SOQL.HavingFilter.count(Lead.Name).greaterThan(100))
    .toAggregated();

// Multiple GROUP BY
.groupBy(Contact.FirstName)
.groupBy(Contact.LastName)

// Related field GROUP BY
.groupBy('OpportunityLineItem.Opportunity.Account', Account.Id)

// ROLLUP / CUBE
.groupByRollup(Lead.LeadSource)           // GROUP BY ROLLUP(...)
.groupByRollup('ConvertedOpportunity', Opportunity.StageName)
.groupByCube(Account.Type)               // GROUP BY CUBE(...)
.groupByCube('ConvertedOpportunity', Opportunity.StageName)

// HAVING logic
.have(SOQL.HavingFilter.count(Lead.Name).greaterThan(100))
.have(SOQL.HavingFilter.with(Lead.City).startsWith('San'))
.anyHavingConditionMatching()            // default AND → OR between HAVING conditions
.havingConditionLogic('1 OR 2')          // custom HAVING logic
.have('COUNT(Name) > 100 AND COUNT(Name) < 200') // dynamic HAVING string

Security modes

.userMode()         // FLS + sharing enforced (default for inline queries)
.systemMode()       // ignores FLS; sharing controlled by class keyword
.withSharing()      // explicit with sharing (requires systemMode)
.withoutSharing()   // explicit without sharing (requires systemMode)
.stripInaccessible() // strip inaccessible fields (use with systemMode + withoutSharing)
.stripInaccessible(AccessType.READABLE) // with specific AccessType

Selectors default to .systemMode().withoutSharing(). Override ad-hoc: .userMode().

Result methods

Goal	Method	Return type
Multiple records	.toList()	List<SObject>
Single record (null-safe — returns null for 0 rows)	.toObject()	SObject
Record existence	.doExist()	Boolean
First record Id	.toId()	Id
All record Ids	.toIds()	Set<Id>
Ids from a field	.toIdsOf(Account.OwnerId)	Set<Id>
Ids from a parent field	.toIdsOf('Parent', Account.Id)	Set<Id>
Single field value	.toValueOf(Account.Name)	Object (cast required)
All values of one field	.toValuesOf(Account.Name)	Set<String> (not for Custom Metadata)
All values of parent field	.toValuesOf('Parent', Account.Name)	Set<String>
Map keyed by record Id	.toMap()	Map<Id, SObject>
Map keyed by String field	.toMap(Account.Name)	Map<String, SObject>
Map keyed by relationship field	.toMap('Parent.CreatedBy', User.Email)	Map<String, SObject>
Map key → String value	.toMap(Account.Name, Account.Industry)	Map<String, String>
Map keyed by Id field	.toIdMapBy(Account.OwnerId)	Map<Id, SObject>
Map keyed by related Id field	.toIdMapBy('Parent', Account.Id)	Map<Id, SObject>
Grouped by String key	.toAggregatedMap(Account.Industry)	Map<String, List<SObject>>
Grouped by String key + value list	.toAggregatedMap(Account.Industry, Account.Name)	Map<String, List<String>>
Grouped by relationship key	.toAggregatedMap('Parent.CreatedBy', User.Email)	Map<String, List<SObject>>
Grouped by Id key	.toAggregatedIdMapBy(Account.OwnerId)	Map<Id, List<SObject>>
Grouped by related Id key	.toAggregatedIdMapBy('Parent', Account.Id)	Map<Id, List<SObject>>
COUNT	.toInteger()	Integer
Aggregate	.toAggregated()	List<AggregateResult>
Aggregate (mockable)	.toAggregatedProxy()	List<SOQL.AggregateResultProxy>
Batch	.toQueryLocator()	Database.QueryLocator
Large sets (streaming)	.toCursor()	Database.Cursor
Large sets (pagination)	.toPaginationCursor()	Database.PaginationCursor
Debug SOQL string	.toString()	String

Important behaviors:

• toObject(): throws when >1 row, returns null (no exception) for 0 rows.
• toMap(), toIdMapBy(), toAggregatedMap(), toAggregatedIdMapBy() automatically add WHERE <keyField> != null.
• toValuesOf() does not work with Custom Metadata.
• Map result types require explicit casting: (Map<Id, Account>).

Debug

SOQL.of(Account.SObjectType).preview().toList(); // logs SOQL + bind vars at ERROR level

Examples

// List with filters
List<Account> accounts = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name, Account.Industry)
    .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
    .orderBy(Account.Name)
    .setLimit(50)
    .toList();

// Single record (null-safe)
Account acc = (Account) SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .byId(accountId)
    .toObject();

// Parent fields
List<Contact> contacts = SOQL.of(Contact.SObjectType)
    .with(Contact.Id, Contact.LastName)
    .with('Account', Account.Name, Account.Industry)
    .toList();

// Child sub-query
List<Account> accs = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .with(SOQL.SubQuery.of('Contacts')
        .with(Contact.Id, Contact.LastName)
        .orderBy(Contact.LastName)
    ).toList();

// Dynamic / optional filter
List<Account> result = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .whereAre(SOQL.Filter.with(Account.Industry).equal(industry).ignoreWhen(industry == null))
    .toList();

// Aggregate with GROUP BY + HAVING
List<AggregateResult> results = SOQL.of(Opportunity.SObjectType)
    .with(Opportunity.StageName)
    .sum(Opportunity.Amount, 'total')
    .count(Opportunity.Id, 'cnt')
    .groupBy(Opportunity.StageName)
    .have(SOQL.HavingFilter.sum(Opportunity.Amount).greaterThan(10000))
    .toAggregated();

// ROLLUP with GROUPING
List<AggregateResult> rollup = 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();

// Map keyed by Id
Map<Id, Account> byId = (Map<Id, Account>) SOQL.of(Account.SObjectType).toMap();

// Map grouped by field
Map<String, List<Account>> byIndustry =
    (Map<String, List<Account>>) SOQL.of(Account.SObjectType).toAggregatedMap(Account.Industry);

// Map keyed by owner Id
Map<Id, List<Account>> byOwner =
    (Map<Id, List<Account>>) SOQL.of(Account.SObjectType).toAggregatedIdMapBy(Account.OwnerId);

// Extract single field value
String adminProfileId = (String) SOQL.of(Profile.SObjectType)
    .whereAre(SOQL.Filter.name().equal('System Administrator'))
    .toValueOf(Profile.Name);

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

// Extract set of string values
Set<String> industries = SOQL.of(Account.SObjectType).toValuesOf(Account.Industry);

// toLabel — translated field
List<Lead> leads = SOQL.of(Lead.SObjectType)
    .with(Lead.Company)
    .toLabel(Lead.Status)
    .toList();

// USING SCOPE — current user's records only
List<Task> myTasks = SOQL.of(Task.SObjectType)
    .with(Task.Subject, Task.Status)
    .mineScope()
    .toList();

// FOR UPDATE — lock records
List<Contact> locked = SOQL.of(Contact.SObjectType)
    .byIds(contactIds)
    .forUpdate()
    .toList();

// ALL ROWS — include deleted
Integer total = SOQL.of(Contact.SObjectType).count().allRows().toInteger();

// Batch QueryLocator
Database.QueryLocator ql = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology'))
    .toQueryLocator();

// Cursor for large sets
Database.Cursor cursor = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .toCursor();

// Pagination cursor
Database.PaginationCursor pCursor = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .orderBy(Account.Name)
    .setLimit(100)
    .toPaginationCursor();