Repository files navigation

Using a query builder to build dynamic SOQL gives many advantages:

1. More efficient: No need to deal with string concatenation, and handsfree from handling binding variable names.
2. Less error-prone: APIs are carefully designed with strong types, cannot pass wrong values.

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

• Advantages of Using SOQL Builder in Salesforce (medium link)

Small changes but they are breaking v1.x. However v1.x will be maintained in a separate branch for bug fixes, in case some projects don't want to upgrade.

1. Renamed the following types and methods, so the they are more consistent to their keyword counterparts and easy to remember.

   • Query.Selector ->Query.SelectBy

   • Query.selector() ->Query.selectBy()

   • Query.Orderer ->Query.OrderBy

   • Query.orderer() ->Query.orderBy()

   • Query.Grouper ->Query.GroupBy

   • Query.grouper() ->Query.groupBy()

2. Deprecated two functionsDISTANCE_IN_KM() andDISTANCE_IN_MI(), but introducedDISTANCE(), which is more flexible since units are passed as parameters instead.

• 1. Design Principles
• 2. Naming Conventions
  • 2.1 Naming Readability
  • 2.2 Naming Confliction
• 3. Overview
  • 3.1 Query Class
  • 3.2 Query Execution
• 4. Keywords
  • 4.1 From Statement
  • 4.2 Select Statement
  • 4.3 Where Statement
  • 4.4 Order By Statement
  • 4.5 Group By Statement
• 5. Operators
  • 5.1 Logical Operators
  • 5.2 Comparison Operators
• 6. Functions
  • 6.1 Aggregate Functions
  • 6.2 Date/Time Functions
  • 6.3 Other Functions
• 7. Literals
  • 7.1 Date Literals
  • 7.2 Currency Literals
• 8. License

1. Highly Compatible: Support all syntaxes and functions of SOQL, except the following syntaxes as of current state:

   • USING SCOPE statement.
   • WITH [DATA CATEGORY] statement.

2. Highly Composable: Clauses can be created standalone, then passed around, modified and composed into queries in a later stage.

   Query.SelectByselectBy =selectBy().add(Account.Id,Account.Name);Query.Filterfilter =andx(gt(Account.AnnualRevenue,2000),lt(Account.AnnualRevenue,2000));Query.OrderByorderBy =orderBy().add(Account.CreatedDate).descending().nullsLast();List<Account>accounts = (List<Account>)Query.of(Account.SObjecType)    .selectBy(selectBy).filterBy(filter).orderBy(orderBy)    .run();

3. Value Objects: Queries and all clauses are value objects, which means different query instances are considered equal when built with same parameters in the same order.

   Assert.areEqual(Query.of(Account.SObjectType).selectBy(Account.Id,Account.Name)),Query.of(Account.SObjectType).selectBy(Account.Id,Account.Name)));

4. Strong Types: Strong types are enforced when possible, so developers can make less mistakes when construct queries.

   // Example 1: date function can only be compared with an Integer.qt(CALENDAR_MONTH(Contact.Birthdate),1);// passqt(CALENDAR_MONTH(Contact.Birthdate),'A');// fail

Here are the naming conventions used to increase query readability:

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

1. Use<keyword>By() format for SOQL keywords, such asselectBy,filterBy,groupBy,havingBy,orderBy.
2. Use<operator>x() format for conflicted operators only, such asorx(),andx(),inx(),likex(). No need to memorize when to follow this pattern, the IDE will highlight there is a confliction, then you will know its time to add the x suffix.

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, where all static methods can be referenced directly. All examples in this README are written in such context.

publicwithsharingclassAccountQueryextendsQuery {publicList<Account>listAccount() {return (List<Account>)Query.of(Account.SObjectType)            .selectBy(Account.Name,Account.BillingCountry,Account.BillingState)            .selectBy(FORMAT(CONVERT_CURRENCY(Account.AnnualRevenue)))            .selectBy('Contacts',Query.of(Contact.SObjectType).selectBy(Contact.Name))            .filterBy(orx()                .add(andx()                    .add(gt(Account.AnnualRevenue,1000))                    .add(eq(Account.BillingCountry,'China'))                    .add(eq(Account.BillingState,'Beijing'))                )                .add(andx()                    .add(lt(Account.AnnualRevenue,1000))                    .add(eq(Account.BillingCountry,'China'))                    .add(eq(Account.BillingState,'Shanghai'))                )            )            .orderBy(Account.AnnualRevenue).descending().nullsLast()            .run();    }}

There are three ways to invoke aQuery. And by default they are running in system mode,AccessLevel can be supplied to change their running mode, i.e.run(AccessLevel.USER_MODE).

List<Account>accounts = (List<Account>)Query.of(Account.SObjectType)    .run();// #1Database.QueryLocatorlocator =Query.of(Account.SObjectType)    .selectBy(Account.Name,Account.AnnualRevenue)    .getLocator();// #2Integercount =Query.of(Account.SObjectType).selectBy(COUNT())    .getCount();// #3

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

// SELECT Id FROM AccountQueryaccountQuery =Query.of(Account.SOBjectType);

There are five types ofselectBy() statements, each accept different input types. They can chain from one after another, so developers can select as many fields as they want.

QueryaccountQuery =Query.of(Account.SObjectType)// #1. all params are sobject fields    .selectBy(Account.Name,Account.BillingCountry,Account.BillingState)// #2. all params are functions    .selectBy(FORMAT(CONVERT_CURRENCY(Account.AnnualRevenue)),TO_LABEL('Owner.LocaleSidKey'))// #3. all params are strings    .selectBy('Owner.Profile.Id','TOLABEL(Owner.EmailEncodingKey)')// #4. one subquery for child relationship "Contacts"    .selectBy('Contacts',Query.of(Contact.SObjectType).selectBy(Contact.Name))// #5. a list of objects mixing with sobject fields, funcitons and strings    .selectBy(newList<Object> {Account.Description,FORMAT(Account.CreatedDate),'Owner.Name' });

Use aselectBy() to compose the field selection outside of a query. And oneSelectBy can be added to another one for reuse.

Query.SelectByselectBy =Query.selectBy()    .add(Account.Name,Account.BillingCountry,Account.BillingState)    .add(FORMAT(CONVERT_CURRENCY(Account.AnnualRevenue)))    .add('Contacts',Query.of(Contact.SObjectType).selectBy(Contact.Name));Query.SelectByanotherSelectBy =Query.selectBy()    .add(Account.Description,Account.NumberOfEmployees)    .add(selectBy);// selectBy can be consumed by another selectByQueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(anotherSelectBy);// selectBy can be comsumed by a query

Note: If variableselectBy is modified later, it won't impact theSelectBy or queries composed earlier.

selectBy.add(Account.CreatedDate);// both anotherSelectBy and accountQuery are not impacted.

Usetypeof() to construct a SOQL TYPEOF statement.

1. Multiplethen() methods can be chained to add more fields.
2. Multiplewhen() methods can be used for the sameSObjectType.
3. Multipleelsex() methods can be chained to add more fields.
4. Thetypeof() can be create standalone outside of a query.

QueryaccountQuery =Query.of(Task.SObjecType)    .selectBy(typeof('What')        .when(Account.SObjectType)              .then(Account.Phone,Account.NumberOfEmployees)        .when(Opportunity.SObjectType)// #1 multiple then methods can be chained              .then(Opportunity.Amount,Opportunity.CloseDate)              .then('ExpectedRevenue','Description')        .when(Account.SObjectType)// #2 previously used SObjectType can be used again              .then(Account.BillingCountry,Account.BillingState)        .elsex(Task.Id,Task.Status)        .elsex('Email','Phone')// #3 multiple elsex methods can be chained    );Query.TypeOftypeOfWhat =typeof(Task.SObjecType)    .when().then().elsex() ... ;// #4 TypeOf can be created standalone

The where statement method is calledfilterBy() but notwhereBy. Both comparison expression and logical statement areQuery.Filter types, which can be supplied to thefilterBy(Filter filter) API.

QueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(Account.Name)    .filterBy(gt(Account.AnnualRevenue,2000));// #1. a single comparison expressionQueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(Account.Name)    .filterBy(andx()// #2. a single logical statement        .add(gt(Account.AnnualRevenue,2000))        .add(lt(Account.AnnualRevenue,6000))    );

EachQuery only supports a single method call tofilterBy(). If there are multiple calls tofilterBy() are made, the latter will override the former. This is because the filters used by where statement is a tree structure with a single root. Filters can be created and composed outside of theQuery natively, and the following sections are going introduce two styles to compose them.

Many existing libraries use this kind of filter composition. One thing should take a note, if you prefer to use this style. Theorx,andx only support adding 2 to 10 filters out of the box. When more filters need to be added, please useorx(List<Filter> filters) andandx(List<Filter> filters) APIs instead.

Query.Filterfilter =orx(andx(// only support up to 10 filtersgt(Account.AnnualRevenue,1000),eq(Account.BillingCountry,'China'),eq(Account.BillingState,'Beijing')    ),andx(newList<Filter> {lt(Account.AnnualRevenue,1000),eq(Account.BillingCountry,'China'),eq(Account.BillingState,'Shanghai')    }));

The above traditional composition style is more compact, while the following style gives one advantage: no trailing commas. So developers don't need to worry about when to add/remove them.

Query.Filterfilter =orx()    .add(andx()        .add(gt(Account.AnnualRevenue,1000))        .add(eq(Account.BillingCountry,'China'))        .add(eq(Account.BillingState,'Beijing'))    )    .add(andx()        .add(lt(Account.AnnualRevenue,1000))        .add(eq(Account.BillingCountry,'China'))        .add(eq(Account.BillingState,'Shanghai'))    );

We can compare a field against null witheqNull() andneNull() operators. They are invented due toeq() andne() only support strongly typed parameters, and cannot pass null values. And theinx() andnin() operators also support null checking.

Query.Filterfilter =andx()    .add(eqNull(Account.BillingCountry))    .add(neNull(Account.AnnualRevenue))    .add(inx(Account.BillingState,newList<String> {'Beijing',null }))    .add(nin(Account.BillingState,newList<String> {'Shanghai',null })));

In Apex Query, onlyinx(),nin() operators can be used to compare Id field againstList<SObject>, but noteq() andne().

List<Account>accounts = ... ;// some accounts queried elsewhereList<Contact>contacts =List<Contact>Query.of(Contact.SObjectType)    .selectBy(Contact.Id,Contact.Name)    .filterBy(inx(Contact.AccountId,accounts))    .run();

There are four types oforderBy() statements, each accepts different input types:

Note: TheseorderBy() methods can chain from one to another, so developers can order by as many fields as they want.

QueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(Account.Name)// #1. all params are fields    .orderBy(Account.BillingCountry,Account.BillingState)// #2. all params are strings    .orderBy('Owner.Profile.Name')// #3. all params are functions    .orderBy(DISTANCE(Account.ShippingAddress,Location.newInstance(37.775000, -122.41800),'km'))// #4. a list of objects mixing of fields, strings and funcitons    .orderBy(newList<Object>{Account.BillingCountry,'Owner.Profile.Name' });

EveryorderBy() supports an optional trailing call todescending() andnullsLast(). Ordering fields are default toascending() andnullsFirst() behaviors, you can but not necessarily to declare them explicitly. The ascending and nulls logic will be applied to all the fields or functions used by the previousorderBy() next to them. If different sorting logics need to be applied to each field, just separate them into differentorderBy() methods.

QueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(Account.Name)// fields are in the same ordering behavior    .orderBy(Account.BillingCountry,Account.BillingState).descending().nullsLast();QueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(Account.Name)// fields are in different ordering behaviors    .orderBy(Account.BillingCountry).descending().nullsLast()    .orderBy(Account.BillingState).ascending().nullsFirst();

Use aorderBy() to compose the field ordering logic outside of a query. And oneOrderBy can be added to another one for reuse.

Query.OrderByorderBy =Query.orderBy()    .add(DISTANCE(Account.ShippingAddress,Location.newInstance(37.775000, -122.41800),'km'));Query.OrderByanotherOrderBy =Query.orderBy()    .add(Account.BillingCountry,Account.BillingState).descending().nullsLast()    .add(orderBy);// orderBy can be consumed by another orderByQueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(Account.Name)    .orderBy(anotherOrderBy);// orderBy can be comsumed by a query

Note: If variableorderBy is modified later, it won't impact theOrderBy or queries composed earlier.

orderBy.add(Account.CreatedDate);// both anotherOrderBy and accountQuery are not impacted.

There are four types ofgroupBy() statements, each accepts different input types:

Note: ThesegroupBy() methods can chain from one to another, so developers can group by as many fields as they want.

QueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(AVG(Account.AnnualRevenue))    .selectBy(SUM(Account.AnnualRevenue,'summary'))// optional alias// #1. group by fields    .groupBy(Account.BillingCountry,Account.BillingState)// #2. group by strings    .groupBy('Owner.Profile.Name')// #3. group by date functions    .groupBy(CALENDAR_YEAR(Account.CreatedDate))// #3. a list of objects mixing of fields, strings and functions    .groupBy(newList<Object>{Account.BillingCountry,'Owner.Profile.Name' });

The aggregate results can be filtered and ordered withhavingBy(). ThehavingBy(Filter filter) can be used in the same way asfilterBy(), just supply a comparison expression or logical statement inside it.

QueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(AVG(Account.AnnualRevenue),SUM(Account.AnnualRevenue))    .groupBy(Account.BillingCountry,Account.BillingState).rollup()// aggerate result can be filtered    .havingBy(gt(SUM(Account.AnnualRevenue),2000))// aggerate result can be ordered    .orderBy(AVG(Account.AnnualRevenue),SUM(Account.AnnualRevenue));

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

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

Use agroupBy() to compose the the field grouping outside of a query. And oneGroupBy can be added to another one for reuse.

Query.GroupBygroupBy =Query.groupBy()    .add(CALENDAR_YEAR(Account.CreatedDate));Query.GroupByanotherGroupBy =Query.groupBy().add(Account.BillingCountry,Account.BillingState)    .add(groupBy);// groupBy can be consumed by another groupByQueryaccountQuery =Query.of(Account.SObjectType)    .selectBy(AVG(Account.AnnualRevenue),SUM(Account.AnnualRevenue))    .groupBy(anotherGroupBy);// groupBy can be comsumed by a query

Note: If variablegroupBy is modified later, it won't impact theGroupBy or queries composed earlier.

groupBy.add(DAY_ONLY(CONVERT_TIMEZONE(Account.CreatedDate)));// both anotherGroupBy and accountQuery are not impacted.

There are three logical operators, each function the same as their SOQL counterparts.

// traditional composition styleandx(filter1,filter2,filter3,filter4);andx(newList<Filter> {filter1,filter2,filter3,filter4 });// comma-free composition styleandx().add(filter1,filter2,filter3,filter4);andx().add(filter1,filter2).add(filter3,filter4);

As a rule of thumb, there are three different types can be used forparam:

1. AnSObjectField such asAccount.AnnualRevenue.
2. An function for picklist label, date, distance and aggregation, i.e.TO_LABEL(Account.AccountSource),CALENDAR_MONTH(CreatedDate).
3. A string such as'Owner.Profile.Name'. This is mainly used for parent field referencing.

The following functions operating on Date, Time and Datetime fields.Note: Date functions can only be used in where conditions, and group by statements. When used in group by, of course it can appear in select and having as well. Date functions cannot be used inside any other functions, as well as the above aggregate functions.

QueryaccountQuery =Query.of(Opportunity.SObjectType)    .selectBy(CALENDAR_YEAR(Opportunity.CreatedDate),SUM(Opportunity.Amount))    .groupBy(CALENDAR_YEAR(Opportunity.CreatedDate));

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

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

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 =orx()    .add(eq(Account.CreatedDate,YESTERDAY()))    .add(eq(Account.AnnualRevenual,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)

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

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

NOTE: TRY is an Apex keyword, so it can not have a corresponding method, instead TRY currency can be generated with a generalCURRENCY method. In case Salesforce is introducing new currencies, which are not ported into the library,CURRENCY method can be used temporarily as well.

AED, AFN, ALL, AMD, ANG, AOA, ARS, AUD, AWG, AZN, BAM, BBD, BDT, BGN, BHD, BIF, BMD, BND, BOB, BRL, BSD, BTN, BWP, BYN, BZD, CAD, CDF, CHF, CLP, CNY, COP, CRC, CSD, CUP, CVE, CZK, DJF, DKK, DOP, DZD, EGP, ERN, ETB, EUR, FJD, FKP, GBP, GEL, GHS, GIP, GMD, GNF, GTQ, GYD, HKD, HNL, HRK, HTG, HUF, IDR, ILS, INR, IQD, IRR, ISK, JMD, JOD, JPY, KES, KGS, KHR, KMF, KPW, KRW, KWD, KYD, KZT, LAK, LBP, LKR, LRD, LYD, MAD, MDL, MGA, MKD, MMK, MOP, MRU, MUR, MWK, MXN, MYR, MZN, NAD, NGN, NIO, NOK, NPR, NZD, OMR, PAB, PEN, PGK, PHP, PKR, PLN, PYG, QAR, RON, RSD, RUB, RWF, SAR, SBD, SCR, SDG, SEK, SGD, SHP, SLE, SLL, SOS, SRD, STN, SYP, SZL, THB, TJS, TND, TOP,TRY, TTD, TWD, TZS, UAH, UGX, USD, UYU, UZS, VES, VND, VUV, WST, XAF, XCD, XOF, XPF, YER, ZAR

Apache 2.0