3

Do you know if Force.com supports variable binding in SOQL "Order By" clause?

I tried to do the following binding with no success:

     applicantQuizzes = [Select SOME_FIELDS From Quiz__c s
                  where SOME_CONDITION Order By :sortBy ];

Where sortBy is a string carrying the name of the required field.

Last time I checked they didn't support it, but I figure they must support it eventually, unless so we must continue duplicating lots of code when supporting table sorting capabilities.

2 Answers 2

8

SOQL Bind variables are only for replacing literal values in the expression. ex:

applicantQuizzes = [Select SOME_FIELDS From Quiz__c where SOME_FIELD__C = :value];

If you want pick the order by fields at run-time you will need to use dynamic soql:

string sortby = 'field1,field2,field3';
string querystr = '';
querystr += 'Select SOME_FIELDS From Quiz__c where SOME_CONDITION Order By ';
querystr += sortby ;
applicantQuizzes = database.query(querystr);
1
  • I have a field picklist field 'urgency' with values "high,medium,low". How can I sory by using that order. (sory by asc, desc gives not in the order i want!) asc gives - high,low,medium. BUT I want high, medium and low ordering.
    – sfdcFanBoy
    May 14, 2015 at 4:19
0

An example using variables binding, string concatenation, and String class methods. It might help someone:

// List to store all the data from Leads matching the search parameter, if any
List<sObject> allLeads = new List<sObject>();

            List<String> queryArray = new List<String>{
                'SELECT Id, ContactFullName__c, L2B_Contact__r.First_Name__c, L2B_Contact__r.Last_Name__c, L2B_Contact__c, Next_Followup_Date__c, Status__c, Assigned_To__c, Enquiry_Date__c',
                'FROM L2B_Lead__c',
                'WHERE Status__c = :status',
                'ORDER BY ' + sortByForQuery + ' ' + sortDirectionForQuery,
                'LIMIT 14 OFFSET :offset'};
                    
            allLeads = Database.query(String.join(queryArray, ' '));

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .