In my last post, I showed how to use the new Javascript Remoting functionality that was just released in Spring ’11. After reading a bit about the apex-lang project’s Soql Builder classes, I thought that I would try to rewrite my controller. While I wasn’t able to reduce the number of lines of code as I hoped I would, I think it made it more readable. The Soql Builder let me not worry about escaping single quotes to avoid Soql injection.
One area where I felt that I had to do more work than was necessary was working around filtering by additional fields. In my design, I wanted to be able to always filter by Name, and then by any optional additional fields passed into the controller. This created quite a bit more code than I was hoping to use, but it works!
I also took the time to do a little bit more error handling in the controller. I realized that any exceptions thrown could be seen in the calling Javascript. The controller now checks that all the additional fields passed in are actually fields in the object. If they aren’t it throws an error.
Without further ado, here’s my new controller:
global class autoCompleteController { public class applicationException extends Exception{} @RemoteAction global static SObject[] findSObjects(string obj, string qry, string addFields) { // check to see if the object passed is valid Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe(); Schema.SObjectType sot = gd.get(obj); if (sot == null) { throw new applicationException('Object name ' + obj + ' not valid'); return null; } al.SoqlBuilder soqlB = new al.SoqlBuilder() .selectx('ID') .selectx('Name') .fromx(obj) .orderbyx(new al.OrderBy('name')) .limitx(20); //add the where statement. If no additional fields were sent, it is easy if (addFields == '') { soqlB.wherex(new al.FieldCondition('Name').likex(qry)); } else { // Split the list of additional fields passed List<String> fieldList = addFields.split(','); // get a map of all fields in the object Map<String, Schema.SObjectField> gf = sot.getDescribe().fields.getMap(); // Create the condition for the soql al.OrCondition orC = new al.OrCondition(); // always search the Name field orC.add(new al.FieldCondition('Name').likex(qry)); // loop through the list of fields to validate and add them to the condition for (String s : fieldList) { Schema.SObjectField sof = gf.get(s); if (sof == null) { throw new applicationException('Additional Field ' + s + ' not valid for object ' + obj); return null; } orC.add(new al.FieldCondition(s).likex(qry)); } // add the list of additional fields to the soql soqlB.selectx(fieldList); // add the where clause soqlB.wherex(orC); } String soql = soqlB.toSoql(new al.SoqlOptions().wildcardStringsInLikeOperators()); List<sObject> L = new List<sObject>(); try { L = Database.query(soql); } catch (QueryException e) { throw e; return null; } return L; } }
So, there you have it. If you need to build dynamic Soql in apex, then take a look at apex-lang. It definitely helps avoid common mistakes when building Soql strings and makes the code more readable.
Pingback: Salesforce Javascript Remoting, jQuery and Autocomplete | Vertical Code
Hi Daniel,
Is there any reason why you would not use SOSL instead of SOQL ?
Rup
That’s a very good question. You probably could. It didn’t even occur to me to use that since I came from traditional databases and am so comfortable with SQL. One reason to use SOQL over SOSL would be if you want to limit the search to specific fields rather than having it search most fields. Users might get confused if they typed in “Dan” and got back contacts with Dan in their name as well as in their address.