Dynamic Queries with Apex

Some of our recent projects have had very complex requirements that forced us to delve into unchartered terrority: Dynamic Apex. These requirements included changing the filter criteria for a query based on user input, custom merge functionality, and custom cloning a record to a different object. All of these functions are best written using Dynamic Apex. There is little documentation out there on using Dynamic Apex so we’ve decided to share our experiences with you. This is the first in a series of posts which will explain Dynamic Apex and how we used it for these projects.

Dynamic SOQL
With our partners Arrowpointe and Spark3, we recently integrated a php website with salesforce.com. Visitors to the website can browse through a list of contacts filtering on various attributes. In the past, this functionality would be written solely using the PHP Toolkit. However, to ensure consistency and reduce the amount of labor to transition to Force.com Sites, we used the PHP Toolkit to connect to salesforce and then call an Apex Web Service. Apex code does all the work to ensure that the data is queried with the correct criteria and then returned properly. Since the Where clause varies in our query we had to either use Dynamic SOQL or a large number of If/Then statements, we chose the former.

Below is a scaled down sample of code you can use to perform the same dynamic query. We’ve also provided you with the syntax for calling the function.

public class website_WebServices
{
    //Function to perform Dynamic SOQL query on Contacts
    public static List<Contact> getContacts(String whereClause)
    {
        //Sample list of Fields
        String fieldList = 'id, FirstName, LastName, Phone, Email ';

        //Define an Order By clause to sort your query
        String orderBy = ' Order By MailingState ';

        //Define your entire Query as a string
        String qryString = 'SELECT ' + fieldList + ' FROM Contact ' + whereClause + orderBy + ' limit 10';

        //Query salesforce with your Query string
        List<Contact> queryResults = Database.query(qryString);

        return queryResults;
    }

    //Sample Where Clause
    public static String whereClause = 'WHERE Lastname != null ';

    //Sample function to get a list of Contacts by passing the Where Clause to the getContacts function
    public static list<contact> contacts = getContacts(whereClause);

}

Related posts:

1. Apex Describe with Dynamic SOQl
2. Dynamically inserting the results from Dynamic SOQL
3. Dynamic Picklist Creation and Sorting
4. Properly Formatting Names in Apex
5. Converting Strings to Integers with Apex

Permalink | Post a Comment | Subscribe to Post Discussion