A query builder for dynamic SOQL construction.
Support: If you find this library helpful, please consider sharing it on Twitter or recommending it to your friends or colleagues.
| Environment | Installation Link | Version |
|---|---|---|
| Production, Developer |  |
ver 3.0.5 |
| Sandbox | |
ver 3.0.5 |
Version 2.0 was too complex to maintain and use. Version 3.0 aims for simplicity, though there is limited room for improvement. During the redesign, I also considered whether simple string concatenation would suffice.
- Key Updates
- Performance improved by 30%. This is a modest gain, roughly a 7 vs 10 CPU time difference.
- Strings are now first-class citizens, and strong type checking has been removed.
- Rarely used features have been removed.
- New Features:
The following naming conventions are used to improve query readability:
| Description | Naming Convention | Reasoning | Example | |
|---|---|---|---|---|
| Keywords | Core structures of SOQL. | camelCase | Keywords should clearly correspond to their SOQL equivalents. | selectBy, whereBy, groupBy, havingBy, orderBy |
| Operators | Logical and comparison operators. | lowercase | Operators should be concise and operator-like, using abbreviations where appropriate. | eq, ne, gt, gte, lt, lte, inx, nin |
| Functions | Used for aggregation, formatting, date access, etc. | camelCase | Camel case aligns with Apex method names and is easy to type. | count, max, toLabel, format, calendarMonth, fiscalYear |
| Literals | Only date and currency literals. | UPPER_CASE | These are constant-like values, so static constant variable naming is preferred. | LAST_90_DAYS(), LAST_N_DAYS(30), CURRENCY('USD', 100) |
To avoid conflicts with existing keywords or operators, follow these conventions:
- Use the
<keyword>By()format for SOQL keywords, such asselectBy,whereBy,groupBy,havingBy,orderBy. - Use the
<operator>x()format for conflicting operators only, such asorx(),andx(),inx(),likex().
All operators and functions are implemented as static methods of the Query class. Referencing them with a Query. prefix each time can be tedious. When possible, extend the Query class so all static methods can be referenced directly.
public with sharing class AccountQuery extends Query {
public List<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 Account
WHERE ((AnnualRevenue > 1000 AND BillingState = 'Beijing')
OR (AnnualRevenue < 1000 AND BillingState = 'Shanghai'))
ORDER BY AnnualRevenue DESC NULLS LASTThe main advantage of this library is its flexibility: you can split a complete query into multiple segments, freely combine or reorder them as needed, and assemble the final query conditionally. For example, the SOQL above can be broken down into several dynamic components and then composed together as required:
public with sharing class AccountQuery extends Query {
public List<Account> runQuery(List<Object> additionalFields,
Decimal beijingRevenue,
Decimal shanghaiRevenue) {
Query q = baseQuery();
q.selectBy(additionalFields);
/**
* Don't worry if `andx()` or `orx()` in the where condition
* have zero or only one filter; SOQL will always be built correctly.
*/
q.whereBy(orx());
q.whereBy().add(beijingRevenueGreaterThan(beijingRevenue));
q.whereBy().add(shanghaiRevenueLessThan(shanghaiRevenue));
return q.run();
}
public Query baseQuery() {
Query q = Query.of('Account');
q.selectBy('Name');
q.selectBy(toLabel('Industry'));
return q.orderBy(orderField('AnnualRevenue').descending().nullsLast());
}
public Filter beijingRevenueGreaterThan(Decimal revenue) {
return andx()
.add(gt('AnnualRevenue', revenue))
.add(eq('BillingState', 'Beijing'));
}
public Filter shanghaiRevenueLessThan(Decimal revenue) {
return andx()
.add(lt('AnnualRevenue', revenue))
.add(eq('BillingState', 'Shanghai'));
}
}Parent and child relationships can be assembled using query chaining. Multiple levels of parent and child chaining are supported, except for queries with a group by clause.
public with sharing class AccountQuery extends Query {
public List<Account> listAccount() {
Query parentQuery = Query.of('Account')
.selectBy('Name', format(convertCurrency('AnnualRevenue')));
Query childQuery = 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, Email FROM Contacts) -- Child Chaining
FROM AccountWithout query chaining, the following code achieves the same result:
public with sharing class AccountQuery extends Query {
public List<Account> listAccount() {
return (List<Account>) Query.of('Account')
.selectBy('Name', toLabel('Industry'),
'Parent.Name', format(convertCurrency('Parent.AnnualRevenue')),
'(SELECT Name, Email FROM Contacts)')
.run();
}
}When you want to run the same Query with different binding variables, use the following pattern. Note: Query templates should be built with var(binding variable name).
public with sharing class AccountQuery extends Query {
public static Query accQuery {
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
);
}
return accQuery;
}
set;
}
public List<Account> listAccount(String state, Decimal revenue) {
return (List<Account>) accQuery.run(new Map<String, Object> {
'revenue' => revenue,
'state' => state,
'emailSuffix' => '%gmail.com'
});
}
}Equivalent to the following SOQL:
SELECT Name, toLabel(Industry)
(SELECT Name, Email FROM Contacts WHERE Email LIKE :emailSuffix)
FROM Account
WHERE (AnnualRevenue > :revenue AND BillingState = :state)Execute with the default AccessLevel.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 selectBy(count()). |
Execute with any AccessLevel, such as AccessLevel.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 selectBy(count()). |
All queries are created with a simple call to Query.of(String objectName). If no other fields are selected, a default Id field is used.
Query accountQuery = Query.of('Account');Equivalent to the following SOQL:
SELECT Id FROM Account| 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. |
Query accountQuery = Query.of('Account')
.selectBy('Name', toLabel('Industry'))
.selectBy(new List<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))
(SELECT Name, Email FROM Contacts)
FROM AccountThe whereBy(Filter filter) API accepts either a comparison expression or a logical statement.
Query accountQuery = Query.of('Account')
.selectBy('Name')
.whereBy(gt('AnnualRevenue', 2000)); // #1. comparison filter
Query accountQuery = Query.of('Account')
.selectBy('Name')
.whereBy(andx() // #2. logical filter
.add(gt('AnnualRevenue', 2000))
.add(lt('AnnualRevenue', 6000))
);Use whereBy() to access the root filter, so branch filters can be appended later.
// TYPE #1: a default AND logical filter will be applied
Query accountQuery = Query.of('Account').selectBy('Name')
.whereBy(gt('AnnualRevenue', 2000));
accountQuery.whereBy().add(lt('AnnualRevenue', 6000));
// TYPE #2: an existing logical filter will be reused
Query accountQuery = 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 applied
Query accountQuery = Query.of('Account').selectBy('Name');
accountQuery.whereBy().add(gt('AnnualRevenue', 2000));
accountQuery.whereBy().add(lt('AnnualRevenue', 6000));All equivalent to the following SOQL:
SELECT Name FROM Account Where AnnualRevenue > 2000 AND AnnualRevenue < 6000| API | Description | |
|---|---|---|
| 1 | orderBy(Object...) |
Order by up to 10 fields. |
| 2 | orderBy(List<Object>) |
Order by List<Object> of fields. |
Parameters can be either string representations or functions.
Query accountQuery = Query.of('Account')
.selectBy('Name', toLabel('Industry'))
.orderBy(
'BillingCountry DESC NULLS LAST',
distance('ShippingAddress', Location.newInstance(37.775000, -122.41800), 'km')
)
.orderBy(new List<Object>{ 'Owner.Profile.Name' });Parameters can also be created by orderField(). Equivalent to the above SOQL:
Query accountQuery = Query.of('Account')
.selectBy('Name', toLabel('Industry'))
.orderBy(
orderField('BillingCountry').descending().nullsLast(),
orderField(distance('ShippingAddress', Location.newInstance(37.775000, -122.41800), 'km'))
)
.orderBy(new List<Object>{ orderField('Owner.Profile.Name') });Equivalent to the following SOQL:
SELECT Name, toLabel(Industry)
FROM Account
ORDER BY BillingCountry DESC NULLS LAST,
DISTANCE(ShippingAddress, GEOLOCATION(37.775001, -122.41801), 'km'),
Owner.Profile.Name| API | Description | |
|---|---|---|
| 1 | groupBy(String ...) |
Group by up to 10 field names. |
| 2 | groupBy(List<String>) |
Group by a List of field names. |
Query accountQuery = Query.of('Account')
.selectBy(avg('AnnualRevenue'))
.selectBy(sum('AnnualRevenue', 'RevenueSUM')) // optional alias
.groupBy('BillingCountry', calendarYear('CreatedDate'))
.groupBy(new List<String>{ calendarMonth('CreatedDate') });Equivalent to the following SOQL:
SELECT AVG(AnnualRevenue), SUM(AnnualRevenue) RevenueSUM
FROM Account
GROUP BY BillingCountry, CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate)Aggregate results can be filtered and ordered with havingBy() and orderBy(). The havingBy(Filter filter) method is used in the same way as whereBy().
Query accountQuery = 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:
SELECT AVG(AnnualRevenue), SUM(AnnualRevenue)
FROM Account
GROUP BY ROLLUP(BillingCountry, BillingState)
HAVING SUM(AnnualRevenue) > 2000
ORDER BY AVG(AnnualRevenue), SUM(AnnualRevenue)Optional rollup() or cube() methods can be called on the query to generate subtotals or grand totals.
Query accountQuery = Query.of('Account')
.selectBy(AVG('AnnualRevenue'), SUM('AnnualRevenue'))
.groupBy('BillingCountry', 'BillingState')
.rollup();| 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 |
| 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 parameter can be:
- Field names such as
AnnualRevenue,'Owner.Profile.Name'. - Functions such as:
toLabel()function- date function
calendarMonth('CreatedDate') - distance function
distance('ShippingAddress', Location.newInstance(37.775001, -122.41801), 'km') - aggregate function
sum('AnnualRevenue')in a having statement
The inx() and nin() operators can also be used to compare an Id field against a List<SObject>.
List<Account> accounts = ... ; // some accounts queried elsewhere
List<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 Contact
WHERE AccountId IN :accounts| 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 examples show various ways to compose a filter:
Query.Filter revenueGreaterThan = gt('AnnualRevenue', 1000);
Query.LogicalFilter shanghaiRevenueLessThan = andx().addAll(new List<Filter> {
lt('AnnualRevenue', 1000),
eq('BillingState', 'Shanghai')
});
Query.LogicalFilter orFilter = orx()
.add(andx()
.add(revenueGreaterThan)
.add(eq('BillingState', 'Beijing'))
)
.add(shanghaiRevenueLessThan);Equivalent to the following SOQL:
(AnnualRevenue > 1000 AND BillingState = 'Beijing')
OR (AnnualRevenue < 1000 AND BillingState = 'Shanghai')| 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 |
The following functions operate 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 Opportunity
WHERE CALENDAR_YEAR(CreatedDate) > 2000
GROUP BY CALENDAR_YEAR(CreatedDate)| Static Methods | Description |
|---|---|
convertTimezone(field) |
Converts 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. |
Here is an example of how to generate a location-based comparison expression:
Query.Filter filter = 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') |
Below are all available date literals, referenced from Salesforce (link). They can be created with the corresponding methods and passed into comparison operators.
Query.Filter filter = 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)
You can find the list of supported currency ISO codes in the Salesforce documentation (see here).
Query.Filter filter = orx()
.add(eq('AnnualRevenual', CURRENCY('USD', 2000)))
.add(eq('AnnualRevenual', CURRENCY('CNY', 2000)))
.add(eq('AnnualRevenual', CURRENCY('TRY', 2000)))
);Apache 2.0