4

I want to show a table of items, when a user clicks on the header of the column, it will sort the items by ASC/DESC.

Based on my internet searches, I need to use dynamic SOQL to include variables after the ORDER BY clause. But, I can't seem to find good example where dynamic SOQL was used in the standardsetcontroller.

// the current sort direction. defaults to asc
public String sortDir {
    get  { if (sortDir == null) {  sortDir = 'asc'; } return sortDir;  }
    set;
}

// the current field to sort by. defaults to event date
public String sortField {
    get  { if (sortField == null) {sortField = 'Event_Date__c'; } return sortField;  }
    set;
}    
String orderByClause = 'ORDER BY ' + sortField + ' ' + sortDir;
String eventDateClause = 'AND Event_Date__c >= TODAY';

SSC = new ApexPages.StandardSetController(Database.query(
        'select Id, name, Event_Date__c, Event_Description_Short__c, 
         Event_GO_Region__c, Event_Image_URL__c, Event_Publish_Date__c,
         Event_Time__c, Event_Time_Start__c, Event_Time_End__c, Event_Status__c,
         Event_Type__c, Public_Page_URL__c, Event_Coordinator__c,
         Event_Location__c from GO_Event__c 
         WHERE RecordType.Name = \'Corporate Wellness\'
         AND Event_Status__c = \'Display\'
         eventDateClause
         orderByClause'));

I'm not getting any errors in the developer console. But I am getting this on my VF page.

enter image description here

1

1 Answer 1

4

No, dynamic SOQL does not support expression evaluation, so you can't use Date.today() inside your string. You can include local variables, but not reference their properties or methods. There is an Idea out there to allow properties, but I doubt dynamic soql will ever support expression evaluation.

Date Literals may be a better choice here, since you can already use:

String eventDateClause = 'Event_Date__c >= TODAY';

I don't believe the ORDER BY clause supports dynamic binding either. That clause would have to merge the variables into the string directly:

String orderByClause = 'ORDER BY ' + sortField + ' ' + sortDir;
6
  • to clarify, if I make all my AND's and ORDER BY strings, it should work?
    – Tyler Zika
    Commented Sep 8, 2016 at 17:53
  • I think the only things you need to change are the one Date.today() filter and the ORDER BY clause. You can do dynamic bindings, just not when you have any properties (:myVariable.someProperty) or expressions (:myVariable.someMethod()) . And ORDER BY in particular doesn't support them (not all clauses do).
    – Adrian Larson
    Commented Sep 8, 2016 at 17:55
  • If I include the eventDateClause at the end of my string it 'returns unexpected token: '<EOF>' '
    – Tyler Zika
    Commented Sep 8, 2016 at 18:11
  • Hard to say what you're doing without seeing it, but you can just modify your initial string. You don't have to introduce a new one, if that's the issue. I can compile this snippet without issue: Database.query('SELECT Id FROM Account WHERE CreatedDate = TODAY')
    – Adrian Larson
    Commented Sep 8, 2016 at 18:16
  • 1
    You should be getting a compile error, line breaks not allowed in string literals. But it would be 'SELECT ... FROM ... WHERE ...' + ' AND ' + eventDateClause + ' ' + orderByClause.
    – Adrian Larson
    Commented Sep 8, 2016 at 18:21

You must log in to answer this question.

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