Apex Unit Test: SOQL Filter

Today I participated in an online meeting, which discussed some of the "best practices" for testing Apex code, Visualforce pages, etc. One of the topics that was pretty much skipped but came up during the Q&A portion of the meeting was regarding how to ensure that the test code you write refrains from grabbing any actual live production data and only grabs the records created to facilitate the test. They discussed one way to do this but I have another and I thought it could be useful to other developers.

Depending on your experience you may not think that this is a very important topic. However, those of you that have worked with Enterprise organizations understand that these orgs may have tons and tons of records. For orgs that have millions of records in any given object it may be best to write test code that actually filters the data used within the unit tests so that the test actually completes in a timely manner. This can be especially important for deploying code because test coverage that runs for a long time can cause problems during the deployment process.

The filtering that I sometimes use within my unit tests is fairly straight forward. I create a time stamp (DateTime variable) at test run-time so that I can use it as a filter for the CreatedDate field on the object, which I am using in the test.

To illustrate this concept we are going to create a very simple batch apex process that will grab all Accounts in an org and update a custom field. I know this is not a particularly useful use case but it will illustrate my point.

The batch apex for this is below:

    Created by: Greg Hacic
    Last Update: 30 March 2011 by Greg Hacic
    Questions?: greg@interactiveties.com
global class batch_on_account_object implements Database.Batchable<SObject> {
	public String soql = 'SELECT custom_text_field__c, Id FROM Account'; //query string variable
	//the "start" method is called at the beginning of a batch Apex job 
	//use this method to collect the records (of objects) to be passed to the "execute" method for processing 
	global Database.QueryLocator start(Database.BatchableContext BC) {
		return Database.getQueryLocator(soql); //executes the query 
	//the "execute" method is called after the "start" method has been invoked and passed a batch of records 
	global void execute(Database.BatchableContext BC, List<SObject> scope) {
		List<Account> account_updates = new List<Account>(); //list for holding account updates
		DateTime now_datetime = Datetime.now(); //datetime variable for right now
		String now_datetime_as_string = now_datetime.format('yyyy-MM-dd')+'T'+now_datetime.format('HH:mm:ss')+now_datetime.format('Z').substring(0, now_datetime.format('Z').length()-2)+':00';
		for (SObject s : scope) { //for all objects from our query 
			Account a = (Account)s; //grab the Account
			a.custom_text_field__c = now_datetime_as_string; //update the custom field
			account_updates.add(a); //add the record to our list
		if (!account_updates.isEmpty()) { //if the list of accounts is not empty 
			update account_updates; //pass back the updates 
	//the "finish" method is called once all the batches are processed 
	global void finish(Database.BatchableContext info) {
		//send a message about the batch processing completion 
		AsyncApexJob a = [SELECT CreatedBy.Email, CreatedBy.Name, CreatedDate, CompletedDate, Id, JobItemsProcessed, NumberOfErrors, Status, TotalJobItems FROM AsyncApexJob WHERE Id =: info.getJobId()]; //query the AsyncApexJob object to retrieve the current job's information. 
		Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); //instantiate a new message object 
		String[] toAddresses = new String[]{a.CreatedBy.Email}; //denote the email address for receiving the message 
		mail.setToAddresses(toAddresses); //to 
		mail.setSubject('Apex batch_on_account_object: '+a.Status); //subject 
		mail.setPlainTextBody('The batch Apex job created by '+a.CreatedBy.Name+' ('+a.CreatedBy.Email+') processed '+a.TotalJobItems+' batches with '+a.NumberOfErrors+' failures. The process began at '+a.CreatedDate+' and finished at '+a.CompletedDate+'.'); //body 
		Messaging.sendEmail(new Messaging.SingleEmailMessage[]{ mail }); //send 

If you review the code above you will see that I am simply updating a custom field on all Account records with a new value. If you are familiar with batch apex then you already know that you need to put constraints within your unit test logic so that only one execution of the execute method is performed. This is a requirement of the Force.com platform. Now most developers will simply append a " LIMIT 200" clause to their SOQL statement and I am not opposed to this. As a matter of fact this will often be enough to provide adequate unit test coverage for your code.

However, what if you are not simply updating all accounts (as I am in my demo batch logic above)? Specifically, what if you are only updating Accounts that meet very specific criteria. Furthermore, what if you have three million Account records in your salesforce org? What you’ll find is that the SOQL query within your batch apex code will take much, much longer to complete. This will likely be true even if you pass a "LIMIT" clause within your unit test. Additionally, this may cause headaches when you try to deploy your code.

As I mentioned much earlier in this post is that I will create a time stamp to use as a filter within my SOQL statement. This doesn’t necessarily mean that I will only get records that I created during the unit test but it does mean that there is a finite number of records that may be returned in the query and it also means that the query (or the execute method in our batch logic) will return data much faster than having this filter condition excluded. So here’s the test class with the filter condition.

    Created by: Greg Hacic
    Last Update: 30 March 2011 by Greg Hacic
    Questions?: greg@interactiveties.com
private class test_batch_on_account_object {
	static testMethod void test_logic() {
	//BEGIN: perform some setup steps...
		//create a variable for use in our query later (it's declared here because we need it populated prior to creation of test accounts)
		DateTime createdDateValue = DateTime.now(); //DateTime variable
		String createdDate_Formatted_Date = createdDateValue.format('yyyy-MM-dd'); //date portion of the createdDateValue variable formatted as Strting
		String createdDate_Formatted_Time = createdDateValue.format('HH:mm:ss'); //time portion of the createdDateValue variable formatted as Strting
		String createdDate_Formatted_Zone = createdDateValue.format('Z'); //timezone offset of the createdDateValue variable formatted as Strting
		String finalDateTimeValue = createdDate_Formatted_Date+'T'+createdDate_Formatted_Time+createdDate_Formatted_Zone.substring(0, createdDate_Formatted_Zone.length()-2)+':00';
		//create some accounts
		Account[] accounts = new Account[0]; //new Account sObject
		for (Integer a = 0; a < 150; a++) {
			accounts.add(new Account(Name = 'Test - '+a));
		insert accounts; //insert the account records
		Set<Id> Ids = new Set<Id>(); //set for holding the Ids of all the created Account records
		for (Account act : accounts) { //for all inserted Accounts
			Ids.add(act.Id); //add the Id to our set
	//END: perform some setup steps...
		//validate that all the records have a NULL value for custom_text_field__c
		List<Account> queried_accounts = [SELECT custom_text_field__c FROM Account WHERE Id =: Ids];
		for (Account qa : queried_accounts) { //for all accounts
			System.assertEquals(null, qa.custom_text_field__c); //assert that the custom_text_field__c is null
		Test.startTest(); //switch to test context
		batch_on_account_object batch = new batch_on_account_object();
		batch.soql += ' WHERE CreatedDate >= '+finalDateTimeValue; //append the string to our soql variable
		ID batch_processId = Database.executeBatch(batch); //execute the batch
		Test.stopTest(); //switch back to runtime context
		//validate that all the records now have a value for custom_text_field__c
		queried_accounts.clear(); //remove all the records from our Account List
		queried_accounts = [SELECT custom_text_field__c FROM Account WHERE Id =: Ids]; //re-query
		for (Account qa : queried_accounts) { //for all accounts
			System.assertNotEquals(null, qa.custom_text_field__c); //assert that the custom_text_field__c is NOT null

As you can see above, I create a datetime variable at run-time and then create all my test account records, which I want to be returned in the execute method of the batch apex. Since all the test account records were created after the datetime variable was declared they will be returned (as intended).

Anyway, I'm sure many of you have your own tricks for limiting results in your unit tests. This is just one of the ones that I use.

Automated Exchange Rates in Salesforce.com

Reduce Repetitive Tasks, Eliminate Errors & Free Up Your Administrators.

Birthday Reminders for Salesforce.com

It might lead to a sale. Or it might make you feel good.