# Beyond The Cloud Dev Soql Lib Soql Lib > SOQL Lib — Query Builder - Skill: `tomevault-io/beyond-the-cloud-dev-soql-lib-soql-lib` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/beyond-the-cloud-dev-soql-lib-soql-lib` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/beyond-the-cloud-dev-soql-lib-soql-lib/raw - Safety review: pending (external: skill-scanner PASS, skillspector PASS) - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: DevOps & Infra - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/beyond-the-cloud-dev-soql-lib-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: ```apex // 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 ```apex .with(Account.Id, Account.Name, Account.Industry) // up to 5 fields .with(Account.Id) // single field, can be chained .with(new List{ 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{ 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 ```apex .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 ```apex .format(Opportunity.Amount) // SELECT FORMAT(Amount) .format(Opportunity.Amount, 'amt') // SELECT FORMAT(Amount) amt ``` ### WHERE — single condition ```apex .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('admin@example.com')) ``` ### WHERE — compound conditions ```apex // 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{...})` | `IN (:v)` | | `.notIn(new List{...})` | `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 ```apex // 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 ```apex .byId(recordId) // WHERE Id = :id .byId(record) // WHERE Id = :record.Id .byIds(new Set{ id1, id2 }) // WHERE Id IN (...) .byIds(new List{ id1, id2 }) // WHERE Id IN (...) .byIds(records) // WHERE Id IN (ids from list) .byRecordType('Partner') // WHERE RecordType.DeveloperName = 'Partner' ``` ### Semi-join (InnerJoin) ```apex .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: ```apex .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 ```apex .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: ```apex SOQL.of(Knowledge__kav.SObjectType) .with(Knowledge__kav.Id, Knowledge__kav.Title) .withDataCategory(SOQL.DataCategoryFilter.with('Geography__c').at(new List{ 'Europe__c', 'North_America__c' })) .toList(); ``` ### ORDER BY / LIMIT / OFFSET ```apex .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 ```apex .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 ```apex 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 ```apex .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` | | 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` | | Ids from a field | `.toIdsOf(Account.OwnerId)` | `Set` | | Ids from a parent field | `.toIdsOf('Parent', Account.Id)` | `Set` | | Single field value | `.toValueOf(Account.Name)` | `Object` (cast required) | | All values of one field | `.toValuesOf(Account.Name)` | `Set` (not for Custom Metadata) | | All values of parent field | `.toValuesOf('Parent', Account.Name)` | `Set` | | Map keyed by record Id | `.toMap()` | `Map` | | Map keyed by String field | `.toMap(Account.Name)` | `Map` | | Map keyed by relationship field | `.toMap('Parent.CreatedBy', User.Email)` | `Map` | | Map key → String value | `.toMap(Account.Name, Account.Industry)` | `Map` | | Map keyed by Id field | `.toIdMapBy(Account.OwnerId)` | `Map` | | Map keyed by related Id field | `.toIdMapBy('Parent', Account.Id)` | `Map` | | Grouped by String key | `.toAggregatedMap(Account.Industry)` | `Map>` | | Grouped by String key + value list | `.toAggregatedMap(Account.Industry, Account.Name)` | `Map>` | | Grouped by relationship key | `.toAggregatedMap('Parent.CreatedBy', User.Email)` | `Map>` | | Grouped by Id key | `.toAggregatedIdMapBy(Account.OwnerId)` | `Map>` | | Grouped by related Id key | `.toAggregatedIdMapBy('Parent', Account.Id)` | `Map>` | | COUNT | `.toInteger()` | `Integer` | | Aggregate | `.toAggregated()` | `List` | | Aggregate (mockable) | `.toAggregatedProxy()` | `List` | | 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 != null`. > - `toValuesOf()` does not work with Custom Metadata. > - Map result types require explicit casting: `(Map)`. ### Debug ```apex SOQL.of(Account.SObjectType).preview().toList(); // logs SOQL + bind vars at ERROR level ``` ## Examples ```apex // List with filters List 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 contacts = SOQL.of(Contact.SObjectType) .with(Contact.Id, Contact.LastName) .with('Account', Account.Name, Account.Industry) .toList(); // Child sub-query List 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 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 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 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 byId = (Map) SOQL.of(Account.SObjectType).toMap(); // Map grouped by field Map> byIndustry = (Map>) SOQL.of(Account.SObjectType).toAggregatedMap(Account.Industry); // Map keyed by owner Id Map> byOwner = (Map>) 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 ownerIds = SOQL.of(Account.SObjectType) .whereAre(SOQL.Filter.with(Account.Industry).equal('Technology')) .toIdsOf(Account.OwnerId); // Extract set of string values Set industries = SOQL.of(Account.SObjectType).toValuesOf(Account.Industry); // toLabel — translated field List leads = SOQL.of(Lead.SObjectType) .with(Lead.Company) .toLabel(Lead.Status) .toList(); // USING SCOPE — current user's records only List myTasks = SOQL.of(Task.SObjectType) .with(Task.Subject, Task.Status) .mineScope() .toList(); // FOR UPDATE — lock records List 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](https://github.com/beyond-the-cloud-dev/soql-lib) — distributed by [TomeVault](https://tomevault.io).