soql-lib-query-builder

name	soql-lib-query-builder
description	>- Use when this capability is needed.

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

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

---

Source: beyond-the-cloud-dev/soql-lib — distributed by TomeVault.