Sorting Tables
STOP: Before reading this go vote for this idea: Native sorting functionality on <apex:datatable> and other list objects. I think the code in this article is pretty darn cool but if this type of functionality could be supported with a simple Visualforce attribute I am all for that. Okay, you can keep reading now.
One of the more common requirements you may come across when working with Visualforce is the need to sort data in a table, either a dataTable or pageBlockTable. There are several ways to accomplish this.
1) Re-query the data using dynamic SOQL and changed the "order by" part of the SOQL statement. The problem with this is that if you have inputFields in the table the data will be lost if there is a change before saving. Also, why query again if you don't have to?
2) Use a 3rd party javascript library. This works and may provide the fastest execution but it also inserts another layer of complexity. Not only must you now maintain the Apex code but also the javascript integration. And what happens when new browsers are released or updated? Unless you are a javascript pro you must rely on the library developers to ensure the javascript will work. You will also need to update your static resource files with the latest version of the javascript library. As you can see this adds additional overhead no developer would want to take on if they don't have to.
3) Use 100% Apex to perform all of your sorting needs. This may not be as quick as the javascript method as there is a round trip to the server but I think the benefits are worth the costs. 100% native platform code means salesforce.com will provide all the updates and make sure it works with all the supported browsers. Once created it requires very little maintenance in the future.
To me the choice is clear that option #3 is the best.
Looking at the Apex documentation you may have come across the unfortunate realization that the sort() method for Lists only supports Lists that contain primitive data types. Noooooo! This is no good so I set out to create a sort class that can take a list of sObjects of any type, custom or standard, and sort the contents.
Before starting I had two goals this class had to meet:
1) It had to be truly dynamic. There could be no hard coding of object or field names.
2) Performance had to be usable. I knew it wouldn't be as fast as the javascript approach but it had to be decent.
Using Dynamic Apex, a wrapper class, and the generic object data type I was able to come up with what I think is a crafty little class that can dynamically sort nearly any list that is composed of standard or custom objects.
First let’s take a look at the class. It is a little complicated so if you don't want to take a deep dive in the code you may want to skip to the demo code below.
public class superSort {
/*This method takes 3 arguments, the List of objects to sort, the field to sort,
and the order, asc or desc*/
public static void sortList(List<sObject> items, String sortField, String order){
/*I must give credit where it is due as the sorting algorithm I am using is the
one supplied by Andrew Waite here: http://blog.sforce.com/sforce/2008/09/sorting-collect.html */
Boolean isSortFieldReference = false;
Map<Id,String> referenceName;
/*Determine the type of the field that needs to be sorted, if it is a
reference we will want sort by the name of the related object, not the
ID itself*/
if(items[0].getSObjectType().getDescribe().fields.getMap().get(sortField).getDescribe().getType().Name() == 'REFERENCE'){
isSortFieldReference = true;
referenceName = new Map<Id,String>();
/*Determine the type of this object and populate the Id to Name map*/
Set<Id> referenceIds = new Set<Id>();
for(sObject s : items){
referenceIds.add((Id)s.get(sortField));
}
// DevAngel - EDIT - Because you may not have a value for the reference field in the first record
// in the table you are sorting, this approach will fail on the substring method. Below is
// is a more reliable method using describe calls
/*
String objectID = (String)items[0].get(sortField);
String prefix = objectID.substring(0,3);
String objectType;
Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe();
for(Schema.SObjectType s : gd.values()){
if(prefix == s.getDescribe().getKeyPrefix()){
objectType = s.getDescribe().Name;
}
}
*/
// DevAngel - EDIT - New approach...
List<Schema.Sobjecttype> objType = items[0].getSObjectType().getDescribe().fields.getMap().get(sortField).getDescribe().getReferenceTo();
String objectType = objType[0].getDescribe().getName();
//Query the related objects for the name and populate the Id -> Name map
String queryString = 'select Id, Name from ' + objectType + ' where ID IN :referenceIDs';
for(sObject s : Database.query(queryString )){
// DevAngel - EDIT - if the reference field is null then we will not have a result, so we need to "create one"
if (s.get('Name') == null) {
referenceName.put((Id)s.get('Id'), 'n/a');
} else {
referenceName.put((Id)s.get('Id'),(String)s.get('Name'));
}
}
}
/*Declare a list that will contain the sorted results. I think this is one of the
coolest parts of this method as the system will not let you declare a list of
sObjects (List<sObject> objects = new List<sObjects>();) but using a
wrapper class you can bypass this system limitation to create this type of list */
List<cObject> resultList = new List<cObject>();
//Create a map that can be used for sorting
Map<object, List<cObject>> objectMap = new Map<object, List<cObject>>();
for(sObject ob : items){
if(isSortFieldReference == false){
if(objectMap.get(ob.get(sortField)) == null){
objectMap.put(ob.get(sortField), new List<cObject>());
}
cObject o = new cObject(ob);
objectMap.get(ob.get(sortField)).add(o);
}else{
if(objectMap.get(referenceName.get((Id)ob.get(sortField))) == null){
objectMap.put(referenceName.get((Id)ob.get(sortField)), new List<cObject>());
}
cObject o = new cObject(ob);
objectMap.get(referenceName.get((Id)ob.get(sortField))).add(o);
}
}
//Sort the keys
List<object> keys = new List<object>(objectMap.keySet());
keys.sort();
for(object key : keys){
resultList.addAll(objectMap.get(key));
}
//Apply the sorted values to the source list
items.clear();
if(order.toLowerCase() == 'asc'){
for(cObject ob : resultList){
items.add(ob.obj);
}
}else if(order.toLowerCase() == 'desc'){
for(integer i = resultList.size()-1; i >= 0; i--){
items.add(resultList[i].obj);
}
}
}
public class cObject{
sObject obj {get; set;}
public cObject(sObject obj){
this.obj = obj;
}
}
/*Some test methods that provide 100% coverage */
public static testMethod void sortAscendingTest(){
List<Opportunity> opps = new List<Opportunity>();
for(integer i = 0; i<1000; i++){
opps.add(new Opportunity(Name = 'test' + i, Amount = 1000 * Math.random()));
}
Test.startTest();
Long start = system.currentTimeMillis();
sortList(opps,'Amount','asc');
system.debug(system.currentTimeMillis() - start);
Test.stopTest();
//Assert the list was sorted correctly
Decimal assertValue = -1;
for(Opportunity o : opps) {
System.debug('Opp value: ' + o.amount);
System.assert(assertValue <= o.amount);
assertValue = o.amount;
}
}
public static testMethod void sortDescendingTest(){
List<Opportunity> opps = new List<Opportunity>();
for(integer i = 0; i<1000; i++){
opps.add(new Opportunity(Name = 'test' + i, Amount = 1000 * Math.random()));
}
Test.startTest();
sortList(opps,'Amount','desc');
Test.stopTest();
//Assert the list was sorted correctly
Decimal assertValue = 1001;
for(Opportunity o : opps) {
System.debug('Opp value: ' + o.amount);
System.assert(assertValue >= o.amount);
assertValue = o.amount;
}
}
}
What is great about this class is that it meets the two initial goals. It is a truly dynamic method in that it can take a list containing any Standard or Custom objects and sort it. It also performs fairly well. In my testing the average time to sort a List of 1000 records was between 500-700ms. When sorting Reference fields this was slower as the method performs a query to fetch the related names. Next let's apply this class to a Visualforce page.
Here we have a very simple page and controller. In each column header we create a commandLink that calls the doSort method in the controller. There is also a param within this commandLink that controls which field we want to sort. The value of this param will be the API name of the field for the column's content.
First the page:
<apex:page controller="tableSort">
<apex:form >
<apex:pageBlock >
<apex:pageBlockTable value="{!opps}" var="o" id="table">
<apex:column >
<apex:facet name="header">
<apex:commandLink value="{!$ObjectType.Opportunity.Fields.Name.Label}" action="{!doSort}" rerender="table">
<apex:param name="sortField" value="Name" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:inputField value="{!o.name}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="{!$ObjectType.Opportunity.Fields.StageName.Label}" action="{!doSort}" rerender="table">
<apex:param name="sortField" value="StageName" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:inputField value="{!o.StageName}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="{!$ObjectType.Opportunity.Fields.Probability.Label}" action="{!doSort}" rerender="table">
<apex:param name="sortField" value="Probability" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:inputField value="{!o.Probability}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="{!$ObjectType.Opportunity.Fields.CloseDate.Label}" action="{!doSort}" rerender="table">
<apex:param name="sortField" value="CloseDate" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:inputField value="{!o.CloseDate}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="{!$ObjectType.Opportunity.Fields.Amount.Label}" action="{!doSort}" rerender="table">
<apex:param name="sortField" value="Amount" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:inputField value="{!o.Amount}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="{!$ObjectType.Opportunity.Fields.CreatedDate.Label}" action="{!doSort}" rerender="table">
<apex:param name="sortField" value="CreatedDate" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!o.CreatedDate}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="{!$ObjectType.Opportunity.Fields.CreatedById.Label}" action="{!doSort}" rerender="table">
<apex:param name="sortField" value="CreatedById" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!o.CreatedById}"/>
</apex:column>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>
</apex:page>
And then the controller:
public class tableSort {
List<Opportunity> opps;
public String sortField {get; set;}
public String previousSortField {get; set;}
public List<Opportunity> getOpps() {
if(opps == null){
opps = [select Id, Name, StageName, Probability, CloseDate, Amount, CreatedDate, CreatedById from Opportunity limit 10];
}
return opps;
}
public void doSort(){
String order = 'asc';
/*This checks to see if the same header was click two times in a row, if so
it switches the order.*/
if(previousSortField == sortField){
order = 'desc';
previousSortField = null;
}else{
previousSortField = sortField;
}
//To sort the table we simply need to use this one line, nice!
superSort.sortList(opps,sortField,order);
}
}
I hope this is useful and provides a simple, scalable, way to sort tables in Visualforce.
One thing to note is that since this class uses the Global Describe methods the user must have read access to the object they are trying to sort.
All rights reserved Various trademarks held by their respective owners.
General Enquiries: 415-901-7000 | Fax: 415-901-7040 | Sales: 1-800-no-software