Feb 8, 2012

Dynamic SOQL Queries With Zero to Many Conditions

CloudSpokes is currently running a challenge to create a Multiple Campaign Picker. I don't participate in the challenges, but usually browse through the challenge descriptions because I think it is another good reference point for the kind of functionality users are looking for in Salesforce. This particular challenge is definitely a worth while exercise by the way, and a nice addition to the standard sfdc features.

Anyway, as I was reading the challenge, I realized that the part of it that relates to dynamically create a SOQL statement is something that I do all the time, and have an elegant way to handle it. The problem with creating SOQL statements dynamically is that you often have different requirements based on user selection or record state - that means that you may need many conditions, just one condition, or no conditions. You can very easily create a giant nested IF statements tree, but that looks crappy, and you end up repeating lines of code. Also, the more conditions you might have increases the size of the tree exponentially. And, you end up with code that you cannot reuse at all.

The code sample below is nice because it is compact and fairly reusable. The idea is to create a list of strings, where each string in the list is a complete condition segment. Once you go through all the possible scenarios, you have a list of zero to many strings that can be easily handled by 5 lines of generic code:

public string getQuery() {

    // create a string to hold the query and
    string strQuery = 'SELECT Id, Name, Status__c FROM  MyCustObj__c ';
    // create a list of strings to hold the conditions
    List conditions = new List();
    // go through your possible scenarios one by one, and add each individual condition to the array.
    // in this example the user can select from 3 dropdowns, each corresponds to a picklist field.

    // this dropdown was constructed with all the picklist values plus "no value" and "all values" option.
    if (getSelectedStatus().startsWith('No '))
        conditions.add('Status__c = null ');
    else if (!getSelectedStatus().startsWith('All '))
        conditions.add('Status__c LIKE \'%' + getSelectedStatus() + '%\' ');
    // this dropdown was also constructed with all the picklist values plus "no value" and "all values" option.
    if (getSelectedType().startsWith('No '))
        conditions.add('Type__c = null ');
    else if (!getSelectedType().startsWith('All '))
        conditions.add('Type__c LIKE \'%' + getSelectedType() + '%\' ');
    // same as above but this field is a multi select picklist, so you need INCLUDE instead of LIKE.
    if (getSelectedCategory().startsWith('No '))
        conditions.add('Category__c = null ');
    else if (!getSelectedCategory().startsWith('All '))
        conditions.add('Category__c INCLUDES (\'' + getSelectedCategory() + '\') ');
    //////// this is the beef of this sample:
    //////// five lines that dynamically figure out what needs to go in the query
    if (conditions.size() > 0) {
        strQuery += 'WHERE ' + conditions[0];
        for (Integer i = 1; i < conditions.size(); i++)
            strQuery += 'AND ' + conditions[i];
    // add sort and limits at the end
    strQuery += 'ORDER BY InvestmentDate__c DESC, Series__c DESC, CreatedDate DESC';
    return strQuery;