+ Start a Discussion
cmtgolf05cmtgolf05 

SOQL ORDER BY variable

I have built a custom report using Visual Force. I am trying to give the end user the ability to sort the report by a few different fields. I have created a pick list on a Custom Object that contains the field names (API Names) that the user can sort by. In my class I query the Custom object to get the field the end user wants to sort by, I store that in a variable. I then want to use that vairble to order by my SOQL statement that builds the report.

 

Here is my codes:

 

//Set the variable from the custom field

List<Custom__c> SortBy = [Select SortBy__C from Custom__c Where Name = 'FreightDashboard' ];

 

//My Query to build the report:

SELECT Estimated_Ship_Date__c, Date_Shipped__c, Opportunity.CloseDate, Opportunity.Account.Name, From OpportunityLineItem WHERE Opportunity.StageName = 'Closed Won' and (NOT ShippingType__c like '%Small Parcel%') ORDER BY :SortBY]

 

I want my end user to be able to sort by Estimated_Ship_Date__c or  Date_Shipped__c or Opportunity.CloseDate at their choosing.

 

Any help?

JonathanBaltzJonathanBaltz

Well, I was going to say try setting the SELECT query as a string and adding the SortBy as a variable in the string and then running a database.query(queryString); to get the result.  But then I looked at the query closer. 

 

Instead of setting the ORDER BY to a string, it looks like you're setting it to a list of values.  Try changing the ORDER BY to SortBy[0] and see if that works first.  If not, then try setting the query to a string. 

pumbaapumbaa

I also create a customized Activity HIstory with filter and sorting feature. What I did is passing the parameter from VF page to my apex class, and from Apex class you use string or boolean to store all the sort fields. 

 

If they are selected for sorting you can put them all to a string orderClause  eg:

  String query = 'select A, B, C from Object '+ whereClause + orderClause;

  List<SObject> os =  Database.query(query);

 

Let me know if this helps.

 

cheers,

Ricky

 

 

 

cmtgolf05cmtgolf05

Thank you for your help. I have been trying to rework this but I am unable to get it to work. It appears I am not setting my variable correctly. My new Report Query is

 

String shipping = '%Small Parcel%';
String Stage = 'Closed Won';

String Query = 'SELECT Estimated_Ship_Date__c, Date_Shipped__c, Opportunity.CloseDate, Opportunity.Account.Name, From OpportunityLineItem WHERE Opportunity.StageName = :Stage and (NOT ShippingType__c like :shipping) ORDER BY ';
Query += SortBy ;

List<OpportunityLineItem> myOCR = Database.query(Query);

return myOCR;

 

When I hard code the SortBy Variable the query works perfectly however I need to set the SortBy varible to the result of another query and I cannot get the correct syntax. Here is what I have tried so far and none of these worked:

 

String SortBy = 'Select SortBy__C from Custom__c Where Name = FreightDashboard' ;

 

String SortBy = '(Select SortBy__C from Custom__c Where Name = FreightDashboard)' ;

 

String sortx = 'Select SortBy__C from Custom__c Where Name = FreightDashboard' ;
List<Custom__c> Sortby = Database.query(sortx);
Return Sortby;

 

list<Custom__c> Sortby;
list <Custom__c> customa = ([Select SortBy__C from Custom__c Where Name = 'FreightDashboard']);{
   for (Custom__c cx:customa){
   Sortby.add(cx.SortBy__C); //not sure on this syntax 
  }
}

 

 

list<Custom__c> Sortby;

  public list<Custom__c> getCustom__c() {

        Sortby = ([Select SortBy__C from Custom__c Where Name = 'FreightDashboard']);

        return Sortby;
}

 

 

As you can probably tell I am new to SOQL. I have experience with SQL but I am struggling with this Order by variable.

 

Thank you,