apexfarm/ApexQueryPublic

NotificationsYou must be signed in to change notification settings
Fork1
Star15

A better Apex SOQL query builder.

License

Apache-2.0 license

15 stars 1 fork Branches Tags Activity

Star

Notifications

You must be signed in to change notification settings

Branches Tags

Folders and files

Name		Name	Last commit message	Last commit date
Latest commit History 63 Commits
apex-query/main/default/classes		apex-query/main/default/classes
config		config
docs/images		docs/images
scripts		scripts
.forceignore		.forceignore
.gitignore		.gitignore
.prettierignore		.prettierignore
.prettierrc		.prettierrc
LICENSE		LICENSE
README.md		README.md
package.json		package.json
sfdx-project.json		sfdx-project.json

Repository files navigation

Apex Query

A query builder to build SOQL dynamically.

Environment	Installation Link	Version
Production, Developer		ver 3.0.4
Sandbox		ver 3.0.4

Release v3.0.0

v2.0 was too complex to maintain and use. v3.0 was trying to be simple, although not much can be improved. During the remake, I also had a question in mind, would it be simple enough to just use string concatenation?

Key Updates
- Performance is improved by 30%. This isn't much compared a 7 vs 10 CPU time difference.
- Strings become first class citizens, and strong type checking are removed.
- Rarely used features are removed, such as value object concepts.
New Features:
Removed Features
- RemovedTYPEOF statement, use a manually built long string instead.
- Removed input parameter types ofSObjectType andSObjectField, useString names instead.
- Removed filter composition style:andx(filter1, filter2), useandx().add(filter1).add(filter2) instead.
- Removed currency literals such asUSD(100), useCURRENCY('USD', 100) instead.
API Changes:
- Normalized functions (i.e.max()) to return strings, instead of strong types.
- Normalized filters (i.e.eq()) to accept objects, instead of strong types.
- eqNull andneNull operators are removed, useeq('Field', null) andne('Field', null) directly.
- Query.of(Account.SobjectType) =>Query.of('Account')
- Query.filterBy() =>Query.whereBy()
- Rename functions withcamel case instead of uppercase. for example:
  - CONVERT_CURRENCY() =>convertCurrency()
  - MAX() =>max()

1. Naming Conventions

1.1 Naming Readability

Here are the naming conventions used to increase query readability:

	Description	Naming Convention	Reasoning	Example
Keywords	These are backbone structures of a SOQL.	camelCase	Keywords should easily remind users to their SOQL counterparts.	`selectBy`,`whereBy`,`groupBy`,`havingBy`,`orderBy`
Operators	These are mainly logical and comparison operators.	lowercase	Operators should be small and short to be operator-like, abbreviation is used when appropriate.	`eq`,`ne`,`gt`,`gte`,`lt`,`lte`,`inx`,`nin`
Functions	These are used to perform aggregation, formatting, and date accessing etc.	camelCase	Camel cases are align with Apex method names, and easy to type.	`count`,`max`,`toLabel`,`format`,`calendarMonth`,`fiscalYear`
Literals	There are only date and currency literals.	UPPER_CASE	Those are constant-like values, so static constant variable naming convention is preferred.	`LAST_90_DAYS()`,`LAST_N_DAYS(30)`,`CURRENCY('USD', 100)`

1.2 Naming Confliction

Here are the naming conventions to avoid conflictions with existing keywords or operators.

Use<keyword>By() format for SOQL keywords, such asselectBy,whereBy,groupBy,havingBy,orderBy.
Use<operator>x() format for conflicted operators only, such asorx(),andx(),inx(),likex().

2. Overview

2.1 Query Class

All operators and functions are built as static methods of the Query class, to reference them with aQuery dot every time is tedious. When possible, please extend theQuery class, then all static methods can be referenced directly.

publicwithsharingclassAccountQueryextendsQuery {publicList<Account>listAccount() {return (List<Account>)Query.of('Account')            .selectBy('Name',toLabel('Industry'))            .whereBy(orx()                .add(andx()                    .add(gt('AnnualRevenue',1000))                    .add(eq('BillingState','Beijing')))                .add(andx()                    .add(lt('AnnualRevenue',1000))                    .add(eq('BillingState','Shanghai')))            )            .orderBy(orderField('AnnualRevenue').descending().nullsLast())            .run();    }}

Equivalent to the following SOQL:

SELECT Name, toLabel(Industry)FROM AccountWHERE ((AnnualRevenue>1000AND BillingState='Beijing')OR (AnnualRevenue<1000AND BillingState='Shanghai'))ORDER BY AnnualRevenueDESC NULLS LAST

2.2 Query Composition

The key benefit of this library is to decompose the full query into various parts, and compose them together conditionally. Let's decompose the above SOQL into the following dynamic structure.

publicwithsharingclassAccountQueryextendsQuery {publicList<Account>runQuery(List<Object>additionalFields,DecimalbeijingRevenue,DecimalshanghaiRevenue) {Queryq =baseQuery();q.selectBy(additionalFields);/**         *  Please don't worry about `andx()` or `orx()` set to where condition         *  having zero or only one filter, SOQL can always be built correctly.         */q.whereBy(orx());q.whereBy().add(beijingRevenueGreaterThan(beijingRevenue));q.whereBy().add(shanghaiRevenueLessThan(shanghaiRevenue));returnq.run();    }publicQuerybaseQuery() {Queryq =Query.of('Account');q.selectBy('Name');q.selectBy(toLabel('Industry'));returnq.orderBy(orderField('AnnualRevenue').descending().nullsLast());    }publicFilterbeijingRevenueGreaterThan(Decimalrevenue) {returnandx()            .add(gt('AnnualRevenue',revenue))            .add(eq('BillingState','Beijing'));    }publicFiltershanghaiRevenueLessThan(Decimalrevenue) {returnandx()            .add(lt('AnnualRevenue',revenue))            .add(eq('BillingState','Shanghai'));    }}

2.3 Query Chaining

Parent and child relationship can be assembled via query chaining. Multiple levels of parent and child chaining are supported, but not for queries with group by clause.

publicwithsharingclassAccountQueryextendsQuery {publicList<Account>listAccount() {QueryparentQuery =Query.of('Account')            .selectBy('Name',format(convertCurrency('AnnualRevenue')));QuerychildQuery =Query.of('Contact').selectBy('Name','Email');return (List<Account>)Query.of('Account')            .selectBy('Name',toLabel('Industry'))            .selectParent('Parent',parentQuery)// Parent Chaining            .selectChild('Contacts',childQuery)// Child Chaining            .run();    }}

Equivalent to the following SOQL:

SELECT Name, toLabel(Industry),Parent.Name, FORMAT(convertCurrency(Parent.AnnualRevenue))-- Parent Chaining    (SELECT Name, EmailFROM Contacts)-- Child ChainingFROM Account

Without query chaining, the following code can achieve the same result.

publicwithsharingclassAccountQueryextendsQuery {publicList<Account>listAccount() {return (List<Account>)Query.of('Account')            .selectBy('Name',toLabel('Industry'),'Parent.Name',format(convertCurrency('Parent.AnnualRevenue')),'(SELECT Name, Email FROM Contacts)')            .run();    }}

2.4 Query Template

When the sameQuery is intended to be run with different binding variables, the following pattern can be used.Note: Query template supposed be built withvar() with binding var names.

publicwithsharingclassAccountQueryextendsQuery {publicstaticQueryaccQuery {get {if (accQuery ==null) {accQuery =Query.of('Account')                    .selectBy('Name',toLabel('Industry'))                    .selectChild('Contacts',Query.of('Contact')                        .selectBy('Name','Email')                        .whereBy(likex('Email',var('emailSuffix')))// var 1                    )                    .whereBy(andx()                        .add(gt('AnnualRevenue',var('revenue')))// var 2                        .add(eq('BillingState',var('state')))// var 3                    );            }returnaccQuery;    }set;    }publicList<Account>listAccount(Stringstate,Decimalrevenue) {return (List<Account>)accQuery.run(newMap<String,Object> {'revenue' =>revenue,'state' =>state,'emailSuffix' =>'%gmail.com'        });    }}

Equivalent to the following SOQL:

SELECT Name, toLabel(Industry)    (SELECT Name, EmailFROM ContactsWHERE EmailLIKE :emailSuffix)FROM AccountWHERE (AnnualRevenue> :revenueAND BillingState= :state)

2.5 Query Execution

Execute with defaultAccessLevel.SYSTEM_MODE:

	API	API with Binding Variables	Return Types
1	`run()`	`run(bindingVars)`	`List<SObject>`
2	`getLocator()`	`getLocator(bindingVars)`	`Database.QueryLocator`
3	`getCount()`	`getCount(bindingVars)`	`Integer`, must be used together with`SELECT COUNT()`.

Execute with anyAccessLevel, such asAccessLevel.USER_MODE:

	API	API with Access Level	Return Types
1	`run(AccessLevel)`	`run(bindingVars, AccessLevel)`	`List<SObject>`
2	`getLocator(AccessLevel)`	`getLocator(bindingVars, AccessLevel)`	`Database.QueryLocator`
3	`getCount(AccessLevel)`	`getCount(bindingVars, AccessLevel)`	`Integer`, must be used together with`SELECT COUNT()`.

3. Keywords

3.1 From Statement

All queries are created with a simple call toQuery.of(String objectName) API. A defaultId field is used if no other fields are selected.

QueryaccountQuery =Query.of('Account');

Equivalent to the following SOQL:

SELECT IdFROM Account

3.2 Select Statement

	API	Description
1	`selectBy(Object ... )`	Select up to 10 field names or functions.
2	`selectBy(List<Object>)`	Select a`List<Object>` of any field names or functions.
3	`selectParent(String relationshipName, Query subQuery)`	Parent chaining.
4	`selectChild(String relationshipName, Query subQuery)`	Child chaining.

QueryaccountQuery =Query.of('Account')    .selectBy('Name',toLabel('Industry'))    .selectBy(newList<Object> {'Owner.Name',FORMAT('CreatedDate') })    .selectParent('Parent',Query.of('Account')        .selectBy('Name',format(convertCurrency('AnnualRevenue'))))    .selectChild('Contacts',Query.of('Contact').selectBy('Name','Email'));

Equivalent to the following SOQL:

SELECT Name, toLabel(Industry),Owner.Name, FORMAT(CreatedDate)Parent.Name, FORMAT(convertCurrency(Parent.AnnualRevenue))-- Parent Chaining    (SELECT Name, EmailFROM Contacts)-- Child ChainingFROM Account

3.3 Where Statement

Set Root Filter

whereBy(Filter filter) API accepts either a comparison expression or a logical statement.

QueryaccountQuery =Query.of('Account')    .selectBy('Name')    .whereBy(gt('AnnualRevenue',2000));// #1. comparison filterQueryaccountQuery =Query.of('Account')    .selectBy('Name')    .whereBy(andx()// #2. logical filter        .add(gt('AnnualRevenue',2000))        .add(lt('AnnualRevenue',6000))    );

Get Root Filter

UsewhereBy() to access the root filter, so branch filters can be appended to the stem later.

// TYPE #1: a default AND logicical filter will appliedQueryaccountQuery =Query.of('Account').selectBy('Name')    .whereBy(gt('AnnualRevenue',2000));accountQuery.whereBy().add(lt('AnnualRevenue',6000));// TYPE #2: an existing logical filter will be reusedQueryaccountQuery =Query.of('Account').selectBy('Name')    .whereBy(andx().add(gt('AnnualRevenue',2000)));accountQuery.whereBy().add(lt('AnnualRevenue',6000));// TYPE #3: a default AND logical filter will be appliedQueryaccountQuery =Query.of('Account').selectBy('Name');accountQuery.whereBy().add(gt('AnnualRevenue',2000));accountQuery.whereBy().add(lt('AnnualRevenue',6000));

All Equivalent to the following SOQL:

SELECT NameFROM AccountWhere AnnualRevenue>2000AND AnnualRevenue<6000

3.4 Order By Statement

	API	Description
1	`orderBy(Object...)`	Order by up to 10 fields.
2	`orderBy(List<Object>)`	Order by`List<Object>` of fields.

Parameter can either be string representation or functions.

QueryaccountQuery =Query.of('Account')    .selectBy('Name',toLabel('Industry'))    .orderBy('BillingCountry DESC NULLS LAST',DISTANCE('ShippingAddress',Location.newInstance(37.775000, -122.41800),'km')    )    .orderBy(newList<Object>{'Owner.Profile.Name' });

Parameter can also be created byorderField(). Equivalent to the above SOQL:

QueryaccountQuery =Query.of('Account')    .selectBy('Name',toLabel('Industry'))    .orderBy(orderField('BillingCountry').descending().nullsLast(),orderField(DISTANCE('ShippingAddress',Location.newInstance(37.775000, -122.41800),'km'))    )    .orderBy(newList<Object>{orderField('Owner.Profile.Name') });

Equivalent to the following SOQL:

SELECT Name, toLabel(Industry)FROM AccountORDER BY BillingCountryDESC NULLS LAST,    (DISTANCE(ShippingAddress, GEOLOCATION(37.775001,-122.41801),'km'),Owner.Profile.Name

3.5 Group By Statement

	API	Description
1	`groupBy(String ...)`	Group by up to 10 field names.
2	`groupBy(List<String>)`	Group by`List` of field names.

QueryaccountQuery =Query.of('Account')    .selectBy(avg('AnnualRevenue'))    .selectBy(sum('AnnualRevenue','RevenueSUM'))// optional alias    .groupBy('BillingCountry',calendarYear('CreatedDate'))    .groupBy(newList<String>{calendarMonth('CreatedDate') });

Equivalent to the following SOQL:

SELECTAVG(AnnualRevenue),SUM(AnnualRevenue) RevenueSUMFROM AccountGROUP BY BillingCountry, CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate)

Having Clause

The aggregate results can be filtered and ordered withhavingBy() andorderBy().havingBy(Filter filter) can be used in the same way aswhereBy().

QueryaccountQuery =Query.of('Account')    .selectBy(avg('AnnualRevenue'),sum('AnnualRevenue'))    .groupBy('BillingCountry','BillingState')    .rollup()    .havingBy(gt(sum('AnnualRevenue'),2000))    .orderBy(avg('AnnualRevenue'),sum('AnnualRevenue'));

Equivalent to the following SOQL:

SELECTAVG(AnnualRevenue),SUM(AnnualRevenue)FROM AccountGROUP BY ROLLUP(BillingCountry, BillingState)HAVINGSUM(AnnualRevenue)>2000ORDER BYAVG(AnnualRevenue),SUM(AnnualRevenue)

Rollup Summary

Optionalrollup() orcube() methods can be invoked on the query to generate sub totals or grand totals.

QueryaccountQuery =Query.of('Account')    .selectBy(AVG('AnnualRevenue'),SUM('AnnualRevenue'))    .groupBy('BillingCountry','BillingState')    .rollup();

3.6 Other Keywords

API	Generated Format
`limitx(Integer n)`	`LIMIT n`
`offset(Integer n)`	`OFFSET n`
`forView()`	`FOR VIEW`
`forReference()`	`FOR REFERENCE`
`forUpdate()`	`FOR UPDATE`
`updateTracking()`	`UPDATE TRACKING`
`updateViewstat()`	`UPDATE VIEWSTAT`

4. Filters

4.1 Comparison Filter

SOQL Operators	Apex Query Operators	Generated Format
=	`eq(param, value)`	`param = value`
!=	`ne(param, value)`	`param != value`
<	`lt(param, value)`	`param < value`
<=	`lte(param, value)`	`param <= value`
>	`gt(param, value)`	`param > value`
>=	`gte(param, value)`	`param >= value`
BETWEEN	`between(param, minValue, maxValue)`	`param >= minValue AND param <= maxValue`
LIKE	`likex(param, String value)`	`param LIKE value`
NOT LIKE	`nlike(param, String value)`	`(NOT param LIKE value)`
IN	`inx(param, List<Object> values)`	`param IN :values`
NOT IN	`nin(param, List<Object> values)`	`param NOT IN :values`
INCLUDES	`includes(param, List<String> values)`	`param INCLUDES (:value1, :value2)`
EXCLUDES	`excludes(param, List<String> values)`	`param EXCLUDES (:value1, :value2)`

As a rule of thumb, the first param can be:

Field names such asAnnualRevenue,'Owner.Profile.Name'.
Functions such as
- toLabel() function
- date functioncalendarMonth('CreatedDate')
- distance functiondistance('ShippingAddress', Location.newInstance(37.775001, -122.41801), 'km')
- aggregate functionsum('AnnualRevenue') in having statement

Compare with sObject List

inx(),nin() operators can also be used to compare an Id field againstList<SObject>.

List<Account>accounts = ... ;// some accounts queried elsewhereList<Contact>contacts =List<Contact>Query.of('Contact')    .selectBy('Name',toLabel('Account.Industry'))    .whereBy(inx('AccountId',accounts))    .run();

Equivalent to the following SOQL:

SELECT Name, toLabel(Account.Industry)FROM ContactWHERE AccountIdIN :accounts

4.2 Logical Filter

AND	Generated Format
`andx().add(Filter filter1).add(Filter filter2) ...`	`(filter1 AND filter2 ...)`
`andx().addAll(List<Filter> filters)`	`(filter1 AND filter2 ...)`
OR
`orx().add(Filter filter1).add(Filter filter2) ...`	`(filter1 OR filter2 ...)`
`orx().addAll(List<Filter> filters)`	`(filter1 OR filter2 ...)`
NOT
`notx(Filter filter)`	`NOT(filter)`

The following codes demonstrate various ways to compose a filter.

Query.FilterrevenueGreaterThan =gt('AnnualRevenue',1000);Query.LogicalFiltershanghaiRevenueLessThan =andx().addAll(newList<Filter> {lt('AnnualRevenue',1000),eq('BillingState','Shanghai')    });Query.LogicalFilterorFilter =orx()    .add(andx()        .add(revenueGreaterThan)        .add(eq('BillingState','Beijing'))    )    .add(shanghaiRevenueLessThan));

Equivalent to the following SOQL:

(AnnualRevenue>1000AND BillingState='Beijing')OR (AnnualRevenue<1000AND BillingState='Shanghai')

5. Functions

5.1 Aggregate Functions

Static Methods	Generated Format
`count(field)`	`COUNT(field)`
`count(field, alias)`	`COUNT(field) alias`
`countDistinct(field)`	`COUNT_DISTINCT(field)`
`countDistinct(field, alias)`	`COUNT_DISTINCT(field) alias`
`grouping(field)`	`GROUPING(field)`
`grouping(field, alias)`	`GROUPING(field) alias`
`sum(field)`	`SUM(field)`
`sum(field, alias)`	`SUM(field) alias`
`avg(field)`	`AVG(field)`
`avg(field, alias)`	`AVG(field) alias`
`max(field)`	`MAX(field)`
`max(field, alias)`	`MAX(field) alias`
`min(field)`	`MIN(field)`
`min(field, alias)`	`MIN(field) alias`

5.2 Date/Time Functions

The following functions operating on Date, Time and Datetime fields.

Query.of('Opportunity')    .selectBy(calendarYear('CreatedDate'),sum('Amount'))    .whereBy(gt(calendarYear('CreatedDate'),2000))    .groupBy(calendarYear('CreatedDate'));

Equivalent to the following SOQL:

SELECT CALENDAR_YEAR(CreatedDate),SUM(Amount)FROM OpportunityWHERE CALENDAR_YEAR(CreatedDate)>2000GROUP BY CALENDAR_YEAR(CreatedDate)

Static Methods	Description
`convertTimezone(field)`	Convert datetime fields to the user’s time zone.Note: You can only use`convertTimezone()` inside the following date functions.
`calendarMonth(field)`	Returns a number representing the calendar month of a date field.
`calendarQuarter(field)`	Returns a number representing the calendar quarter of a date field.
`calendarYear(field)`	Returns a number representing the calendar year of a date field.
`dayInMonth(field)`	Returns a number representing the day in the month of a date field.
`dayInWeek(field)`	Returns a number representing the day of the week for a date field.
`dayInYear(field)`	Returns a number representing the day in the year for a date field.
`dayOnly(field)`	Returns a date representing the day portion of a datetime field.
`fiscalMonth(field)`	Returns a number representing the fiscal month of a date field.
`fiscalQuarter(field)`	Returns a number representing the fiscal quarter of a date field.
`fiscalYear(field)`	Returns a number representing the fiscal year of a date field.
`hourInDay(field)`	Returns a number representing the hour in the day for a datetime field.
`weekInMonth(field)`	Returns a number representing the week in the month for a date field.
`weekInYear(field)`	Returns a number representing the week in the year for a date field.

5.3 Other Functions

Here is an example how to generate a location-based comparison expression.

Query.Filterfilter =lt(distance('ShippingAddreess',Location.newInstance(37.775000, -122.41800)),20,'km');

Static Methods	Generated Format
`toLabel(field)`	`toLabel(field)`
`format(field)`	`FORMAT(field)`
`convertCurrency(field)`	`convertCurrency(field)`.Note: It can also be used inside`format()`.
`distance(field, Location geo, string unit)`	`DISTANCE(ShippingAddress, GEOLOCATION(37.775,-122.418), 'km')`

6. Literals

6.1 Date Literals

Here are all the available date literals referenced from Salesforce (link). They can be created with corresponding methods, and passed into comparison operators working with them.

Query.Filterfilter =andx()    .add(eq('LastModifiedDate',YESTERDAY()))    .add(gt('CreatedDate',LAST_N_DAYS(5))));

YESTERDAY(),TODAY(),TOMORROW(),LAST_WEEK(),THIS_WEEK(),NEXT_WEEK(),LAST_MONTH(),THIS_MONTH(),NEXT_MONTH(),LAST_90_DAYS(),NEXT_90_DAYS(),THIS_QUARTER(),LAST_QUARTER(),NEXT_QUARTER(),THIS_YEAR(),LAST_YEAR(),NEXT_YEAR(),THIS_FISCAL_QUARTER(),LAST_FISCAL_QUARTER(),NEXT_FISCAL_QUARTER(),THIS_FISCAL_YEAR(),LAST_FISCAL_YEAR(),NEXT_FISCAL_YEAR()
LAST_N_DAYS(Integer n),NEXT_N_DAYS(Integer n),N_DAYS_AGO(Integer n),NEXT_N_WEEKS(Integer n),LAST_N_WEEKS(Integer n),N_WEEKS_AGO(Integer n),NEXT_N_MONTHS(Integer n),LAST_N_MONTHS(Integer n),N_MONTHS_AGO(Integer n),NEXT_N_QUARTERS(Integer n),LAST_N_QUARTERS(Integer n),N_QUARTERS_AGO(Integer n),NEXT_N_YEARS(Integer n),LAST_N_YEARS(Integer n),N_YEARS_AGO(Integer n),NEXT_N_FISCAL_QUARTERS(Integer n),N_FISCAL_QUARTERS_AGO(Integer n),NEXT_N_FISCAL_YEARS(Integer n),LAST_N_FISCAL_YEARS(Integer n),N_FISCAL_YEARS_AGO(Integer n)

6.2 Currency Literals

Here are all the available currency ISO codes referenced from Salesforce (link).

Query.Filterfilter =orx()    .add(eq('AnnualRevenual',CURRENCY('USD',2000)))    .add(eq('AnnualRevenual',CURRENCY('CNY',2000)))    .add(eq('AnnualRevenual',CURRENCY('TRY',2000))));

7.License

Apache 2.0

About

A better Apex SOQL query builder.

Releases4

v3.0.0 Latest

May 7, 2025

+ 3 releases

Packages

No packages published

Movatterモバイル変換

License

apexfarm/ApexQuery

Folders and files

Latest commit

History

Repository files navigation

Apex Query

Release v3.0.0

Table of Contents

1. Naming Conventions

1.1 Naming Readability

1.2 Naming Confliction

2. Overview

2.1 Query Class

2.2 Query Composition

2.3 Query Chaining

2.4 Query Template

2.5 Query Execution

3. Keywords

3.1 From Statement

3.2 Select Statement

3.3 Where Statement

Set Root Filter

Get Root Filter

3.4 Order By Statement

3.5 Group By Statement

Having Clause

Rollup Summary

3.6 Other Keywords

4. Filters

4.1 Comparison Filter

Compare with sObject List

4.2 Logical Filter

5. Functions

5.1 Aggregate Functions

5.2 Date/Time Functions

5.3 Other Functions

6. Literals

6.1 Date Literals

6.2 Currency Literals

7.License

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases4

Packages0

Uh oh!

Contributors2

Uh oh!

Languages

Packages