• Install
  • Contribute
  • API Reference

SOQL Builder

Description

A developer friendly tool for creating SOQL queries as strings. Use whenever you use Database.query() as opposed to [select …].

Code that builds a SOQL query via string concatenation is error prone. Many times you’ll forget a comma, misplace an AND, mis-format a date string, or worst of all, forgot to escape user input. These types of mistakes are silly because they happen to everyone and they seem to happen over and over again. SOQL Builder’s value lay in mitigating these mistakes.

Main class is SoqlBuilder.

Apex Classes

AndCondition Condition ConditionGroup DateFormula Field FieldCondition NestableCondition NotCondition Operator OrCondition OrderBy SetCondition Soqlable SoqlBuilder SoqlOptions SoqlUtils UnitOfTime

Basic Example

Given this SOQL query:

SELECT name FROM account WHERE employees < 10

Here is the corresponding code to create the query string via a SoqlBuilder:

new SoqlBuilder()
 .selectx('name')
 .fromx('account')
 .wherex(new FieldCondition('employees').lessThan(10))
 .toSoql();

NOTE: You probably noticed the x’s at the end of some of the methods. Unfortunately, “select”, “from”, and “where” are all reserved words in apex and cannot be used as method identifiers. In order to get around this, x has been appended to the end of the method name (this convention is followed throughout other classes as well). Another thing you might have noticed is the methods are “chained”. That helps make this code less verbose and feel more like a SOQL statement.

Benefits

Here’s a list of benefits to building SOQL queries using SoqlBuilder:

  1. Reduces the risk of a silly SOQL grammar error
  2. More readable / less verbose code
  3. Automatic literal conversion
  4. String escaping by default
  5. Easy wild-carding

The first benefit is the reduced risk of misplacing an element. All String concatenation with SoqlBuilder takes place inside the super intelligent toSoql() method.

While it might not be true for simple cases (like the example above), using a SoqlBuilder will make your code less verbose and as a result, more readable. The construction of most real-world soql queries is an ugly, un-readable mess of String concatenation. Perhaps you’ve written or seen code like the following:

final Datetime aDatetime = DateTime.newInstance(2010,1,1,1,1,1);
final String aName = 'O\'Neal';
final List<String> aList = new List<String>{'Apparel','Auto'};
String soql = 'SELECT id,name,ispartner,industry';
soql += ' FROM account';
soql += ' WHERE CreatedDate < ';
soql += aDatetime.format('yyyy-MM-dd') + 'T' + aDatetime.format('hh:mm:ss') + 'Z';
soql += ' AND Name like \'%';
soql += String.escapeSingleQuotes(aName);
soql += '%\' AND industry INCLUDES (';
Boolean isFirst = true;
for(String anItem : aList){
 if(isFirst){
 isFirst = false;
 } else {
 soql += ',';
 }
 soql += '\'';
 soql += anItem;
 soql += '\'';
}
soql += ')';
System.debug(soql);

There’s no other way to put it: dude, that’s ugly code!

Here’s how the same query can be constructed using SoqlBuilder:

final Datetime aDatetime = DateTime.newInstance(2010,1,1,1,1,1);
final String aName = 'acme';
final List<String> aList = new List<String>{'Apparel','Auto'};
String soql = new SoqlBuilder()
  .selectx(new Set<Object>{'id','name','ispartner','industry'})
  .fromx('account')
  .wherex(new AndCondition()
    .add(new FieldCondition('CreatedDate').lessThan(aDatetime))
    .add(new FieldCondition('Name').likex(aName))
    .add(new SetCondition('industry').includes(aList))
  )
  .toSoql(new SoqlOptions().wildcardStringsInLikeOperators());
System.debug(soql);

The third benefit is automatic conversion of literals. From the example above (the SoqlBuilder portion), notice how the aDatetime variable is simply passed to the lessThan() method? The FieldCondition handles converting the date time to the appropriate format. Just in case you’re curious where that code is, see the toLiteral() method in the SoqlUtils class.

Another benefit which can be seen in the previous example is automatic escaping of single quotes. Notice that the aName variable is simply passed to the likex() method. When toSoql() is executed, all single quotes in all Strings will be automatically escaped! Imagine how much the AppExchange Security Review Team will like that!

Also, the previous example also shows how easily all Strings in LIKE operators can be wild-carded. By default, the “wildcardStringsInLikeOperators” property is set to FALSE. However, if you call the wildcardStringsInLikeOperators() method on a new SoqlOptions object, then it will do just that: all strings will be wild-carded on both sides.

Reference

For all examples below, the new SoqlBuilder() and .toSoql() portions are omitted. If you’d like to run one of the examples - using anonymous execute for example - then use the following snippet:

System.debug('\n\n'
+ new SoqlBuilder()
//insert example from below here
.toSoql()
+ '\n\n');

SELECT

Selecting Fields

.selectx('ID')
.selectx('Name')
.fromx('Account')
//-> SELECT Name,ID FROM Account

.selectx(new Set<String>{'ID','Name'})
 .fromx('Account')
 //-> SELECT Name,ID FROM Account

.selectx(new List<String>{'ID','Name'})
.fromx('Account')
//-> SELECT Name,ID FROM Account

.fromx('Account')
//-> SELECT ID FROM Account

count()

.selectCount()
.fromx('Account')
//-> SELECT count() FROM Account

toLabel

.selectx(new Field('Rating').toLabelx())
.fromx('Account')
//-> SELECT toLabel(Rating) FROM Account

Relationship Queries

.selectx('id')
.selectx(
  new SoqlBuilder()
  .selectx('id')
  .fromx('OpportunityLineItems'))
.fromx('Opportunity')
//-> SELECT id,(SELECT id FROM OpportunityLineItems) FROM Opportunity

FROM

.fromx('account')
//-> SELECT id FROM account

.fromx('Contact c, c.Account a')
//-> SELECT id FROM Contact c, c.Account a

WHERE

Field Condition

/*
You can create a field condition using any of the following formats:
new FieldCondition().field(fieldName).operator(value)
new FieldCondition(fieldName).operator(value)
new FieldCondition(fieldName,Operator,value)
*/
//the following four examples are equivalent:
.fromx('account').wherex(new FieldCondition().field('name').equals('acme'))
.fromx('account').wherex(new FieldCondition('name').equals('acme'))
.fromx('account').wherex(new FieldCondition('name',Operator.EQUALS,'acme'))
.fromx('account').wherex(new FieldCondition('name','acme'))  //special case only valid for equals
//-> SELECT id FROM account WHERE name = 'acme'

Field Operators (using Operator as constructor argument)

/*
+--------------------------+----------+
| enum value               | operator |
+--------------------------+----------+
| EQUALS                   | =        |
| NOT_EQUALS               | !=       |
| LESS_THAN                | <        |
| LESS_THAN_OR_EQUAL_TO    | <=       |
| GREATER_THAN             | >        |
| GREATER_THAN_OR_EQUAL_TO | >=       |
| LIKEX                    | like     |
+--------------------------+----------+
*/
.fromx('account').wherex(new FieldCondition('employees',Operator.EQUALS,1))
//-> SELECT id FROM account WHERE employees = 1
.fromx('account').wherex(new FieldCondition('employees',Operator.NOT_EQUALS,1))
//-> SELECT id FROM account WHERE employees != 1
.fromx('account').wherex(new FieldCondition('employees',Operator.LESS_THAN,1))
//-> SELECT id FROM account WHERE employees < 1
.fromx('account').wherex(new FieldCondition('employees',Operator.LESS_THAN_OR_EQUAL_TO,1))
//-> SELECT id FROM account WHERE employees <= 1
.fromx('account').wherex(new FieldCondition('employees',Operator.GREATER_THAN,1))
//-> SELECT id FROM account WHERE employees > 1
.fromx('account').wherex(new FieldCondition('employees',Operator.GREATER_THAN_OR_EQUAL_TO,1))
//-> SELECT id FROM account WHERE employees >= 1
.fromx('account').wherex(new FieldCondition('name',Operator.LIKEX,'acme'))
//-> SELECT id FROM account WHERE name like 'acme'

Field Operators (operator as method identifier)

/*
+----------------------+----------+
| method identifier    | operator |
+----------------------+----------+
| equals               | =        |
| notEquals            | !=       |
| lessThan             | <        |
| lessThanOrEqualTo    | <=       |
| greaterThan          | >        |
| greaterThanOrEqualTo | >=       |
| likex                | like     |
+----------------------+----------+
*/
.fromx('account').wherex(new FieldCondition('employees').equals(1))
//-> SELECT id FROM account WHERE employees = 1
.fromx('account').wherex(new FieldCondition('employees').notEquals(1))
//-> SELECT id FROM account WHERE employees != 1
.fromx('account').wherex(new FieldCondition('employees').lessThan(1))
//-> SELECT id FROM account WHERE employees < 1
.fromx('account').wherex(new FieldCondition('employees').lessThanOrEqualTo(1))
//-> SELECT id FROM account WHERE employees <= 1
.fromx('account').wherex(new FieldCondition('employees'.greaterThan(1))
//-> SELECT id FROM account WHERE employees > 1
.fromx('account').wherex(new FieldCondition('employees').greaterThanOrEqualTo(1))
//-> SELECT id FROM account WHERE employees >= 1
.fromx('account').wherex(new FieldCondition('name').likex('acme'))
//-> SELECT id FROM account WHERE name like 'acme'

Set Conditions

/*
You can create a set condition using any of the following formats:
new SetCondition().field(fieldName).operator(values)
new SetCondition(fieldName).operator(values)
new SetCondition(fieldName,Operator,values)
*/
//the following three examples are equivalent:
.fromx('account').wherex(new SetCondition().field('x').includes(new List<Object>{1,2}))
.fromx('account').wherex(new SetCondition('x').includes(new List<Object>{1,2}))
.fromx('account').wherex(new SetCondition('x',Operator.INCLUDES,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x INCLUDES (1,2)

Set Operators (using Operator as constructor argument)

/*
+------------+----------+
| enum value | operator |
+------------+----------+
| INCLUDES   | includes |
| EXCLUDES   | excludes |
| INX        | in       |
| NOT_IN     | not in   |
+------------+----------+
*/
.fromx('account').wherex(new SetCondition('x',Operator.INCLUDES,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x INCLUDES (1,2)
.fromx('account').wherex(new SetCondition('x',Operator.EXCLUDES,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x EXCLUDES (1,2)
.fromx('account').wherex(new SetCondition('x',Operator.INX,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x IN (1,2)
.fromx('account').wherex(new SetCondition('x',Operator.NOT_IN,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x NOT IN (1,2)

Set Operators (operator as method identifier)

/*
+-------------------+----------+
| method identifier | operator |
+-------------------+----------+
| includes          | includes |
| excludes          | excludes |
| inx               | in       |
| notin             | not in   |
+-------------------+----------+
*/
.fromx('account').wherex(new SetCondition('x').includes(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x INCLUDES (1,2)
.fromx('account').wherex(new SetCondition('x').excludes(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x EXCLUDES (1,2)
.fromx('account').wherex(new SetCondition('x').inx(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x IN (1,2)
.fromx('account').wherex(new SetCondition('x').notIn(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x NOT IN (1,2)

Primitives to String literals

//null
.fromx('account').wherex(new FieldCondition('x').equals(null))
//->SELECT id FROM account WHERE x = null
//Boolean
.fromx('account').wherex(new FieldCondition('x').equals(true))
//->SELECT id FROM account WHERE x = true
//String
.fromx('account').wherex(new FieldCondition('x').equals('acme'))
//->SELECT id FROM account WHERE x = 'acme'
//Integer
.fromx('account').wherex(new FieldCondition('x').equals(1))
//->SELECT id FROM account WHERE x = 1
//Long
.fromx('account').wherex(new FieldCondition('x').equals(1L))
//->SELECT id FROM account WHERE x = 1
//Double
.fromx('account').wherex(new FieldCondition('x').equals(1.1))
//->SELECT id FROM account WHERE x = 1.1
//Date
.fromx('account').wherex(new FieldCondition('x').equals(Date.newinstance(2010,1,1)))
//->SELECT id FROM account WHERE x = 2010-01-01
//Datetime
.fromx('account').wherex(new FieldCondition('x').equals(Datetime.newinstance(2010,1,1,1,1,1)))
//->SELECT id FROM account WHERE x = 2010-01-01T01:01:01Z

Date Formulas

//=========================
// Hard-coded day methods
//=========================
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().todayx()))
//->SELECT id FROM account WHERE CreatedDate = TODAY
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().yesterdayx()))
//->SELECT id FROM account WHERE CreatedDate = YESTERDAY
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().tomorrowx()))
//->SELECT id FROM account WHERE CreatedDate = TOMORROW
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last90Days()))
//->SELECT id FROM account WHERE CreatedDate = LAST_90_DAYS
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().next90Days()))
//->SELECT id FROM account WHERE CreatedDate = NEXT_90_DAYS
//=========================
// By Units
//=========================
/*
+-----------------------+-----------------+
| UnitOfTime enum value | SOQL equivalent |
+-----------------------+-----------------+
| Day                   | DAY             |
| Week                  | WEEK            |
| Month                 | MONTH           |
| Quarter               | QUARTER         |
| Year                  | FISCAL_QUARTER  |
| FiscalQuarter         | YEAR            |
| FiscalYear            | FISCAL_YEAR     |
+-----------------------+-----------------+
*/
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:1
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(UnitOfTime.Week)))
//->SELECT id FROM account WHERE CreatedDate = LAST_WEEK
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(UnitOfTime.Month)))
//->SELECT id FROM account WHERE CreatedDate = LAST_MONTH
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(UnitOfTime.Quarter)))
//->SELECT id FROM account WHERE CreatedDate = LAST_QUARTER
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(UnitOfTime.Year)))
//->SELECT id FROM account WHERE CreatedDate = LAST_YEAR
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(UnitOfTime.FiscalQuarter)))
//->SELECT id FROM account WHERE CreatedDate = LAST_FISCAL_QUARTER
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(UnitOfTime.FiscalYear)))
//->SELECT id FROM account WHERE CreatedDate = LAST_FISCAL_YEAR
//=========================
// By Interval
//=========================
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().next(UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = NEXT_N_DAYS:1
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:1
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().next(7,UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = NEXT_N_DAYS:7
.fromx('account').wherex(new FieldCondition('CreatedDate',new DateFormula().last(7,UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:7

AND, OR & NOT

//simple AND condition
.fromx('account')
.wherex(
  new AndCondition()
   .add(new FieldCondition('name','acme'))
   .add(new FieldCondition('ispartner',true))
  )
//->SELECT id FROM account WHERE (name = 'acme' AND ispartner = true)

//simple OR condition
.fromx('account')
.wherex(
  new OrCondition()
   .add(new FieldCondition('name','acme'))
   .add(new FieldCondition('ispartner',true))
  )
//->SELECT id FROM account WHERE (name = 'acme' OR ispartner = true)

//simple NOT condition
.fromx('account')
.wherex(
  new NotCondition(new AndCondition()
   .add(new FieldCondition('name','acme'))
   .add(new FieldCondition('ispartner',true)))
  )
//->SELECT id FROM account WHERE NOT((name = 'acme' AND ispartner = true))

//nested ANDs and ORs
.fromx('account')
.wherex(
    new NotCondition(
        new AndCondition()
        .add(
            new OrCondition()
            .add(new FieldCondition('name','acme'))
            .add(
                new AndCondition()
                .add(new FieldCondition('ispartner',true))
                .add(new FieldCondition('NumberOfEmployees').lessThan(10))
            )
        )
        .add(
            new OrCondition()
            .add(new FieldCondition('createddate').lessThan(new DateFormula().yesterdayx()))
            .add(new FieldCondition('Rating','Hot'))
        )
    )
)
//->SELECT id FROM account WHERE NOT(((name = 'acme' OR (ispartner = true AND NumberOfEmployees < 10)) AND (createddate < YESTERDAY OR Rating = 'Hot')))

ORDER BY

Single Order By

/*
You can create an OrderBy using the following formats:
new OrderBy(fieldName).[ascending|descending|nullsFirst|nullsLast]*()
*/
.fromx('account').orderByx(new OrderBy('name'))
//->SELECT id FROM account ORDER BY name
.fromx('account').orderByx(new OrderBy('name').ascending().nullsFirst())
//->SELECT id FROM account ORDER BY name ASC NULLS FIRST
.fromx('account').orderByx(new OrderBy('name').ascending().nullsLast())
//->SELECT id FROM account ORDER BY name ASC NULLS FIRST
.fromx('account').orderByx(new OrderBy('name').descending().nullsFirst())
//->SELECT id FROM account ORDER BY name DESC NULLS FIRST
.fromx('account').orderByx(new OrderBy('name').descending().nullsLast())
//->SELECT id FROM account ORDER BY name DESC NULLS FIRST

Multiple Order By

.fromx('account').orderByx(new List<OrderBy>{
  new OrderBy('name').ascending().nullsFirst()
  ,new OrderBy('rating').descending().nullsLast()
})
//->SELECT id FROM account ORDER BY name ASC NULLS FIRST, rating DESC NULLS LAST

LIMIT

.fromx('account').limitx(50)
//->SELECT id FROM account LIMIT 50

SOQL Options for toSoql() method

Wildcards

Enabled by default: No

//default
.fromx('account')
.wherex(
  new OrCondition()
  .add(new FieldCondition('name').likex('acme'))
  .add(new FieldCondition('name').likex('test'))
).toSoql()
//->SELECT id FROM account WHERE (name like 'acme' OR name like 'test')

.fromx('account')
.wherex(
  new OrCondition()
  .add(new FieldCondition('name').likex('acme'))
  .add(new FieldCondition('name').likex('test'))
).toSoql(new SoqlOptions().wildcardStringsInLikeOperators())
//->SELECT id FROM account WHERE (name like '%acme%' OR name like '%test%')

.fromx('account')
.wherex(
  new OrCondition()
  .add(new FieldCondition('name').likex('acme'))
  .add(new FieldCondition('name').likex('test'))
).toSoql(new SoqlOptions().doNotWildcardStringsInLikeOperators())
//->SELECT id FROM account WHERE (name like 'acme' OR name like 'test')

String Escaping

Enabled by default: Yes

//GOOD (default)
.fromx('account')
.wherex(new FieldCondition('name').likex('O\'Neal'))
.toSoql()
//->SELECT id FROM account WHERE name like 'O\'Neal'

//BAD!  The generated query below is invalid and will throw an error.
//Why even allow it as an option?  Because you never know when it might be needed - invalid or not.
.fromx('account')
.wherex(new FieldCondition('name').likex('O\'Neal'))
.toSoql(new SoqlOptions().doNotEscapeSingleQuotes())
//->SELECT id FROM account WHERE name like 'O'Neal'

.fromx('account')
.wherex(new FieldCondition('name').likex('O\'Neal'))
.toSoql(new SoqlOptions().escapeSingleQuotes())
//->SELECT id FROM account WHERE name like 'O\'Neal'

  • Base

    • Collection Utilities
    • Exceptions
    • Platform Utilities
    • Sorting
    • String Manipulation
    • Type Conversion
    • Unit Test Helpers

  • Chatter

    • Chatter Follower Manipulations

  • Environment

    • Global Variables

  • Math

    • Randomization
    • Ranges

  • Query

    • Database Utilities
    • SOQL Builder

  • Sort

    • Sorting Primitives
    • Sorting Select Options
    • Sorting SObjects

  • Visualforce

    • Pagination

This website is open source. Please help us by forking the project and adding to it.