It’s been my observation that one of the most error prone pieces of code any force.com developer will write is the code which builds a SOQL query. Think about it. How many times have you forgotten a comma, misplaced an AND, mis-formatted a date string, or worst of all, forgotten to escape user input?
These types of mistakes are silly for several reasons. First, they happen to everyone, second, they seem to happen over and over again – mainly during development but even still, that’s wasted time, and wasted time is wasted money. And the worst part is apex is a good enough language that the rules governing SOQL syntax should be apparent via a clear API. Something where many mistakes could be caught at compile-time – and just in case an error did occur at run-time, something that pinpoints the error better than “hey, your SOQL is invalid”.
My response is a concept called the “SOQL Builder”: a set of classes contained in the apex-lang library whose main class is appropriately called SoqlBuilder. My hope is this set of classes will alleviate force.com developers of having to remember the mundane details of SOQL syntax and provide them a developer friendly tool for creating SOQL. This article is quick introduction to SoqlBuilder followed by a full reference. I hope you find SoqlBuilder useful and if you have any suggestions for improvement, please don’t hesitate to let me know!
Basic Example
Let me start by showing a quick 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 al.SoqlBuilder()
.selectx('name')
.fromx('account')
.wherex(new al.FieldCondition('employees').lessThan(10))
.toSoql();
NOTE: the above code sample assumes you’ve installed the managed package version of apex-lang. If you’ve directly included the apex-lang code in your org, then remove all al. namespace prefix references.
Right off the bat, 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, I chose to append an x to the end of the method name (this convention is followed throughout other classes as well). Wish I didn’t need to append the x, but it is what it is. 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. That’s the “silly” class of mistakes I mentioned earlier and whether it’s a parenthesis here or a comma there, it’s surprisingly easy to do. 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 al.SoqlBuilder()
.selectx(new Set<Object>{'id','name','ispartner','industry'})
.fromx('account')
.wherex(new al.AndCondition()
.add(new al.FieldCondition('CreatedDate').lessThan(aDatetime))
.add(new al.FieldCondition('Name').likex(aName))
.add(new al.SetCondition('industry').includes(aList))
)
.toSoql(new al.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 al.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 al.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 al.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 al.FieldCondition().field(fieldName).operator(value)
new al.FieldCondition(fieldName).operator(value)
new al.FieldCondition(fieldName,Operator,value)
*/
//the following four examples are equivalent:
.fromx('account').wherex(new al.FieldCondition().field('name').equals('acme'))
.fromx('account').wherex(new al.FieldCondition('name').equals('acme'))
.fromx('account').wherex(new al.FieldCondition('name',al.Operator.EQUALS,'acme'))
.fromx('account').wherex(new al.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 al.FieldCondition('employees',al.Operator.EQUALS,1))
//-> SELECT id FROM account WHERE employees = 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.NOT_EQUALS,1))
//-> SELECT id FROM account WHERE employees != 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.LESS_THAN,1))
//-> SELECT id FROM account WHERE employees < 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.LESS_THAN_OR_EQUAL_TO,1))
//-> SELECT id FROM account WHERE employees <= 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.GREATER_THAN,1))
//-> SELECT id FROM account WHERE employees > 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.GREATER_THAN_OR_EQUAL_TO,1))
//-> SELECT id FROM account WHERE employees >= 1
.fromx('account').wherex(new al.FieldCondition('name',al.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 al.FieldCondition('employees').equals(1))
//-> SELECT id FROM account WHERE employees = 1
.fromx('account').wherex(new al.FieldCondition('employees').notEquals(1))
//-> SELECT id FROM account WHERE employees != 1
.fromx('account').wherex(new al.FieldCondition('employees').lessThan(1))
//-> SELECT id FROM account WHERE employees < 1
.fromx('account').wherex(new al.FieldCondition('employees').lessThanOrEqualTo(1))
//-> SELECT id FROM account WHERE employees <= 1
.fromx('account').wherex(new al.FieldCondition('employees'.greaterThan(1))
//-> SELECT id FROM account WHERE employees > 1
.fromx('account').wherex(new al.FieldCondition('employees').greaterThanOrEqualTo(1))
//-> SELECT id FROM account WHERE employees >= 1
.fromx('account').wherex(new al.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 al.SetCondition().field(fieldName).operator(values)
new al.SetCondition(fieldName).operator(values)
new al.SetCondition(fieldName,Operator,values)
*/
//the following three examples are equivalent:
.fromx('account').wherex(new al.SetCondition().field('x').includes(new List<Object>{1,2}))
.fromx('account').wherex(new al.SetCondition('x').includes(new List<Object>{1,2}))
.fromx('account').wherex(new al.SetCondition('x',al.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 al.SetCondition('x',al.Operator.INCLUDES,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x INCLUDES (1,2)
.fromx('account').wherex(new al.SetCondition('x',al.Operator.EXCLUDES,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x EXCLUDES (1,2)
.fromx('account').wherex(new al.SetCondition('x',al.Operator.INX,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x IN (1,2)
.fromx('account').wherex(new al.SetCondition('x',al.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 al.SetCondition('x').includes(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x INCLUDES (1,2)
.fromx('account').wherex(new al.SetCondition('x').excludes(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x EXCLUDES (1,2)
.fromx('account').wherex(new al.SetCondition('x').inx(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x IN (1,2)
.fromx('account').wherex(new al.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 al.FieldCondition('x').equals(null))
//->SELECT id FROM account WHERE x = null
//Boolean
.fromx('account').wherex(new al.FieldCondition('x').equals(true))
//->SELECT id FROM account WHERE x = true
//String
.fromx('account').wherex(new al.FieldCondition('x').equals('acme'))
//->SELECT id FROM account WHERE x = 'acme'
//Integer
.fromx('account').wherex(new al.FieldCondition('x').equals(1))
//->SELECT id FROM account WHERE x = 1
//Long
.fromx('account').wherex(new al.FieldCondition('x').equals(1L))
//->SELECT id FROM account WHERE x = 1
//Double
.fromx('account').wherex(new al.FieldCondition('x').equals(1.1))
//->SELECT id FROM account WHERE x = 1.1
//Date
.fromx('account').wherex(new al.FieldCondition('x').equals(Date.newinstance(2010,1,1)))
//->SELECT id FROM account WHERE x = 2010-01-01
//Datetime
.fromx('account').wherex(new al.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 al.FieldCondition('CreatedDate',new al.DateFormula().todayx()))
//->SELECT id FROM account WHERE CreatedDate = TODAY
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().yesterdayx()))
//->SELECT id FROM account WHERE CreatedDate = YESTERDAY
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().tomorrowx()))
//->SELECT id FROM account WHERE CreatedDate = TOMORROW
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last90Days()))
//->SELECT id FROM account WHERE CreatedDate = LAST_90_DAYS
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.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 al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:1
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Week)))
//->SELECT id FROM account WHERE CreatedDate = LAST_WEEK
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Month)))
//->SELECT id FROM account WHERE CreatedDate = LAST_MONTH
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Quarter)))
//->SELECT id FROM account WHERE CreatedDate = LAST_QUARTER
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Year)))
//->SELECT id FROM account WHERE CreatedDate = LAST_YEAR
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.FiscalQuarter)))
//->SELECT id FROM account WHERE CreatedDate = LAST_FISCAL_QUARTER
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.FiscalYear)))
//->SELECT id FROM account WHERE CreatedDate = LAST_FISCAL_YEAR
//=========================
// By Interval
//=========================
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().next(al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = NEXT_N_DAYS:1
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:1
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().next(7,al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = NEXT_N_DAYS:7
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(7,al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:7
AND, OR & NOT
//simple AND condition
.fromx('account')
.wherex(
new al.AndCondition()
.add(new al.FieldCondition('name','acme'))
.add(new al.FieldCondition('ispartner',true))
)
//->SELECT id FROM account WHERE (name = 'acme' AND ispartner = true)
//simple OR condition
.fromx('account')
.wherex(
new al.OrCondition()
.add(new al.FieldCondition('name','acme'))
.add(new al.FieldCondition('ispartner',true))
)
//->SELECT id FROM account WHERE (name = 'acme' OR ispartner = true)
//simple NOT condition
.fromx('account')
.wherex(
new al.NotCondition(new al.AndCondition()
.add(new al.FieldCondition('name','acme'))
.add(new al.FieldCondition('ispartner',true)))
)
//->SELECT id FROM account WHERE NOT((name = 'acme' AND ispartner = true))
//nested ANDs and ORs
.fromx('account')
.wherex(
new al.NotCondition(
new al.AndCondition()
.add(
new al.OrCondition()
.add(new al.FieldCondition('name','acme'))
.add(
new al.AndCondition()
.add(new al.FieldCondition('ispartner',true))
.add(new al.FieldCondition('NumberOfEmployees').lessThan(10))
)
)
.add(
new al.OrCondition()
.add(new al.FieldCondition('createddate').lessThan(new al.DateFormula().yesterdayx()))
.add(new al.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 al.OrderBy(fieldName).[ascending|descending|nullsFirst|nullsLast]*()
*/
.fromx('account').orderByx(new al.OrderBy('name'))
//->SELECT id FROM account ORDER BY name
.fromx('account').orderByx(new al.OrderBy('name').ascending().nullsFirst())
//->SELECT id FROM account ORDER BY name ASC NULLS FIRST
.fromx('account').orderByx(new al.OrderBy('name').ascending().nullsLast())
//->SELECT id FROM account ORDER BY name ASC NULLS FIRST
.fromx('account').orderByx(new al.OrderBy('name').descending().nullsFirst())
//->SELECT id FROM account ORDER BY name DESC NULLS FIRST
.fromx('account').orderByx(new al.OrderBy('name').descending().nullsLast())
//->SELECT id FROM account ORDER BY name DESC NULLS FIRST
Multiple Order By
.fromx('account').orderByx(new List<al.OrderBy>{
new al.OrderBy('name').ascending().nullsFirst()
,new al.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 al.OrCondition()
.add(new al.FieldCondition('name').likex('acme'))
.add(new al.FieldCondition('name').likex('test'))
).toSoql()
//->SELECT id FROM account WHERE (name like 'acme' OR name like 'test')
.fromx('account')
.wherex(
new al.OrCondition()
.add(new al.FieldCondition('name').likex('acme'))
.add(new al.FieldCondition('name').likex('test'))
).toSoql(new al.SoqlOptions().wildcardStringsInLikeOperators())
//->SELECT id FROM account WHERE (name like '%acme%' OR name like '%test%')
.fromx('account')
.wherex(
new al.OrCondition()
.add(new al.FieldCondition('name').likex('acme'))
.add(new al.FieldCondition('name').likex('test'))
).toSoql(new al.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 al.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 al.FieldCondition('name').likex('O\'Neal'))
.toSoql(new al.SoqlOptions().doNotEscapeSingleQuotes())
//->SELECT id FROM account WHERE name like 'O'Neal'
.fromx('account')
.wherex(new al.FieldCondition('name').likex('O\'Neal'))
.toSoql(new al.SoqlOptions().escapeSingleQuotes())
//->SELECT id FROM account WHERE name like 'O\'Neal'
Facebook
flickr
Linked In
Twitter