Using apex-lang to build Soql statements

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:

[sourcecode]

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;
}
}

[/sourcecode]

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.

Salesforce Javascript Remoting, jQuery and Autocomplete

I was very excited when I found out that Salesforce was releasing Javascript remoting with Spring 11. There have been several cases where having access to Apex classes and logic in Javascript would make my development much easier. One area that I wanted to try out was in an autocomplete component that I built a while ago. I had hacked together a component that used a jQuery autocomplete plugin. While it worked, I had a couple of problems: it didn’t work in IE8 (everybody uses Chrome or Firefox, right?) and the autocomplete consumed a separate Visualforce page, so when I had developer mode turned on, it would choke on the wrapper that developer mode uses.

Using Josh Birk’s blog post and the Salesforce documentation as a reference, I got to work.

First I created the controller. I made this controller as flexible as possible. You can pass it the API object name (Account, Contact, etc), the search string and any additional fields you want to include in the search. It will always search the Name field for the search string.

[Edit: I have created a new controller that uses apex-lang to build the Soql. You can find it in my blog post: http://verticalcode.wordpress.com/2011/02/21/using-apex-lang-to-build-soql-statements/.]

global class autoCompleteController {
    @RemoteAction
    global static SObject[] findSObjects(string obj, string qry, string addFields) {
        // more than one field can be passed in the addFields parameter
        // split it into an array for later use
        List<String> fieldList;
        if (addFields != null) fieldList = addFields.split(',');
       // 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) {
            // Object name not valid
            return null;
        }
        // create the filter text
        String filter = ' like '%' + String.escapeSingleQuotes(qry) + '%'';
        //begin building the dynamic soql query
        String soql = 'select id, Name';
        // if an additional field was passed in add it to the soql
        if (fieldList != null) {
            for (String s : fieldList) {
                soql += ', ' + s;
            }
        }
        // add the object and filter by name to the soql
        soql += ' from ' + obj + ' where name' + filter;
        // add the filter by additional fields to the soql
        if (fieldList != null) {
            for (String s : fieldList) {
                soql += ' or ' + s + filter;
            }
        }
        soql += ' order by Name limit 20';
        List<sObject> L = new List<sObject>();
        try {
            L = Database.query(soql);
        }
        catch (QueryException e) {
            return null;
        }
        return L;
   }
}

Next I created the component. Note that the component requires some jQuery resources which are being loaded from Google’s CDN for the jquery files.

I also chose to use a spinner type indicator to queue the user that the autocomplete is working, which I uploaded as a resource. You can generate your own gif spinner at http://ajaxload.info/.

<apex:component controller='autoCompleteController'>
  <!-- JQuery Files -->
  <script src='https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js'/>
<script src='https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.12/jquery-ui.min.js'/>
<apex:stylesheet value='http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.12/themes/ui-smoothness/jquery-ui.css'/>
  <!-- Attributes Required For Component -->
  <apex:attribute name='objectname' description='The object name you want to look for.'     type='String' required='true'/>
  <apex:attribute name='additionalfield' description='Any additional fields you'd like to search and include in the display.'     type='String' required='false'/>
  <apex:attribute name='autocomplete_textbox' description='The ID for the Autocomplete List Textbox.'     type='String' required='true'/>
  <style>
    .ui-autocomplete-loading { background: white url({!$Resource.circleIndicator}) right center no-repeat; }
  </style>
  <script type='text/javascript'>
    var j$ = jQuery.noConflict();
    j$(document).ready(function() {
 
        var sObjects;
        var queryTerm;
 
        j$(esc('{!autocomplete_textbox}')).autocomplete({
            minLength: 2,
            source: function(request, response) {
                        queryTerm = request.term;
                        autoCompleteController.findSObjects('{!objectname}', request.term, '{!additionalfield}', function(result, event){
                            if(event.type == 'exception') {
                                  alert(event.message);
                            } else {
                                 sObjects = result;
                                 response(sObjects);
                            }
                        });
                   },
            focus: function( event, ui ) {
                    j$(esc('{!autocomplete_textbox}')).val( ui.item.Name );
                    return false;
                    },
            select: function( event, ui ) {
                        j$(esc('{!autocomplete_textbox}')).val( ui.item.Name );
                        j$(esc('{!autocomplete_textbox}_lkid')).val( ui.item.Id );
                        j$(esc('{!autocomplete_textbox}_lkold')).val( ui.item.Name );
                        return false;
                    },
         })
         .data( 'autocomplete' )._renderItem = function( ul, item ) {
            var entry = '<a>' + item.Name;
            j$.each('{!additionalfield}'.split(',') , function(key, value) {
                entry = entry + ' ' + item[value];
            });
            entry = entry + '</a>';
            entry = entry.replace(queryTerm, '<b>' + queryTerm + '</b>');
            return j$( '<li></li>' )
                .data( 'item.autocomplete', item )
                .append( entry )
                .appendTo( ul );
        };
    });
 
    function esc(myid) {
           return '#' + myid.replace(/(:|.)/g,'\\$1');
    }
 
  </script>
</apex:component>

Finally, I can use the component in any Visualforce page to autocomplete on any field. In the below example, I’m looking for account name or ticker symbol:

    <apex:page>
    <apex:form><br />
        <apex:inputText id='account'>
            <c:AutoComplete2 objectname='Account' additionalfield='TickerSymbol' autocomplete_textbox='{!$Component.account}' />
        <apex:inputText>
    </apex:form>
</apex:page>