function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
The_FoxThe_Fox 

Ajax+Sorting

Hello DevAngel,

Would it be possible to have records sorted with an option into the query by as many fields (at least two) as we want when we wake sforceclient.Query

for example sforceclient.Query(SOQL_Query,sorting_field1,sorting_field2) or
sforceclient.Query(SOQL_Query,sorting_field) with sorting field being an array.

or something like sforceclient.Querysorted(SOQL,sorting_field)


I have some recursive quick sort scripting in javascript if you want them

contact me chumbert at odyssey-group dot com

Regards
DevAngelDevAngel

Hmm... Sounds like a nice extension .

I have played with the javascript sort function.  You can write your own camparer function to sort objects.  Not sure what kind of performance issues might arise from that, but since a the records value of a query result is an array it seems fairly simple.

There are some issues with regard to using query more as well, since you can sort the results of the query and then when you do query more and want those sorted then you have to splice the two results together and then resort the combined array.  Then keep doing that until you've got all the records.

To create a sforceClient.Query(soql, sort_field) you could write a function like this:

sforceClient.Query.prototype = function(soql, sort_field) { 
    var ret = this.Query(soql); 
    ret.records = this.yourRecurSort(ret.records, sort_field); 
    return ret; 
} 
sforceClient.yourRecurSort.prototype = function(recs, sort_field) { 
    //All the nifty high perf sort code 
    return recs; 
}

An alternative would be to extend the QueryResult object:


QueryResult.prototype.sort = function(sort_field) {
    qsort = function(recs, fields) {
        //All the nifty high perf sort code
    };
    qsort(this.records);
}
 

Then when making the query call it would look like:


var queryResult sforceClient.Query("Select Id, FirstName, LastName From Lead");
queryResult.sort(["FirstName", "LastName"]);

My hope is that all the creative developers out there will begin building extensions to the toolkit to provide this extra functionality.  That way I can concentrate on the base toolkit and release updates.

 

Federico LarsenFederico Larsen
hellow there....

I have done some sample code for sorting query results.
I hope this can be usefull for other beginners like me.

Best regards, from Argentina.



sort







Federico LarsenFederico Larsen
sorry, but i pasted the html code, but not displayed .... here i go again with the javascript code only

function load(){
sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_60}");
setTimeout('main()',200);
}

function main(){
var orden = new Array();
var cd = new Array();
cd.push("CloseDate");
cd.push("ASC");

var n = new Array();
n.push("Name");
n.push("DESC");

orden.push(cd);
orden.push(n);

var opps = sforceClient.QueryAllSorted("select Id, Name, CloseDate, Amount from Opportunity", orden);
var out ="";
for (var i=0; i out += opps[i].get("Name") + " " +opps[i].get("CloseDate")+ " " +opps[i].get("Amount") + "
";
}

document.getElementById("maindiv").innerHTML = out;
}

function sortSObject(a, b){

for (var i=0; i var va = a.get(sforceClient.sortArray[i][0]);
var vb = b.get(sforceClient.sortArray[i][0]);

var s = 1;

if (sforceClient.sortArray[i][1] == "DESC"){
s = -1;
}
if (va > vb){
return 1 * s;
}
else{
if (va < vb){
return -1 *s;
}
}
}

return 0;
}

sforceClient.sortArray = new Array();
sforceClient.QueryAllSorted = function(soql, sort_orders) {
sforceClient.sortArray = sort_orders;

var sobjects = new Array();
var ret = this.Query(soql);
if (ret.size > 0){
sobjects = ret.records;
while (!ret.done){
sobjects = sobjects.concat(ret.records);
}
}
return sobjects.sort(sortSObject);
}
DevAngelDevAngel

Thanks for the code Federico.

I've modified it a bit to illustrate 2 things.

First, the queryMore. Your code will only grab the first batch of 200. If there are more results, it won't pick them all up without queryMore.

Second, asynchronous calls. If you are pulling lots of data that takes more than a couple of seconds, you should be using the asynchronous nature of the toolkit.

As an aside, you should migrate all your scontrols to the beta3.3 toolkit as shown in the revised snippet below. Cheers.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>sort</title> <script src="https://www.salesforce.com/services/lib/ajax/beta3.3/sforceclient.js?browser=true" type="text/javascript"></script> <script type="text/javascript"> function load(){ sforceClient.registerInitCallback(main); sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_70}", true); } function main(){ var orden = new Array(); var cd = new Array(); cd.push("CloseDate"); cd.push("ASC"); var n = new Array(); n.push("Name"); n.push("DESC"); orden.push(cd); orden.push(n); sforceClient.QueryAllSorted("select Id, Name, CloseDate, Amount from Opportunity", orden); } function displayResults(opps) { var out =""; out += "<table border='1'><tbody>"; for (var i=0; i<opps.length; i++){ out += "<TR><TD>" + opps[i].get("Name") + "</TD><TD>" +opps[i].get("CloseDate")+ "</td><TD>" +opps[i].get("Amount") + "</TD></TR>"; } out += "</tbody></table>"; document.getElementById("maindiv").innerHTML = out; } function sortSObject(a, b){ for (var i=0; i<sforceClient.sortArray.length; i++){ var va = a.get(sforceClient.sortArray[i][0]); var vb = b.get(sforceClient.sortArray[i][0]); var s = 1; if (sforceClient.sortArray[i][1] == "DESC"){ s = -1; } if (va > vb){ return 1 * s; } else { if (va < vb){ return -1 *s; } } } return 0; } sforceClient.QueryAllSorted = function(soql, sort_orders) { sforceClient.sortArray = sort_orders; sforceClient.sobjects = new Array(); this.Query(soql, queryCallback); } sforceClient.sortArray = new Array(); queryCallback = function(ret) { if (ret.size > 0){ sforceClient.sobjects = sforceClient.sobjects.concat(ret.records); if (ret.done == false){ sforceClient.queryMore(ret.queryLocator, queryCallback) } else { displayResults(sforceClient.sobjects.sort(sortSObject)); } } } </script> </head> <body onLoad="load();"> <div id="maindiv"></div> </body> </html>

Federico LarsenFederico Larsen
Thanks a lot for your comments Dave!

I will take them out.

Federico.-
Federico LarsenFederico Larsen
Hi Dave Angel!

Now you gave me some ideas with the asyncronous thing, I am trying to write a sforceClient.QueryInParallel method which aims to execute N querys at the same time, and once all are done and sorted call a specific callback.

I thought it was finished, but making this test, I have problems with callbacks. My question is,

does the AJAX Toolki can handle more than once callback for querys in parallel?

Thanks a lot in advance for your comments and help.

Federico

code:

Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>  
    <title>sort</title>
    <script src="https://www.salesforce.com/services/lib/ajax/beta3.3/sforceclient.js—browser=true" type="text/javascript"></script>
    <script type="text/javascript">
 var _opps = null;
 var _cases = null;
 
    function load(){
        sforceClient.registerInitCallback(main);
        sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_70}", true);
    }
    
    function main(){
  var querys = new Array();
  var ordenes = new Array();
  
        var orden = new Array();
        var cd = new Array();
        cd.push("CloseDate");
        cd.push("ASC");
        
        var n = new Array();
        n.push("Name");
        n.push("DESC");
        
        orden.push(cd);
        orden.push(n);
  
        querys.push("select Id, Name, CloseDate, Amount from Opportunity")
  ordenes.push(orden);
  
  var orden = new Array();
        var cd = new Array();
        cd.push("ClosedDate");
        cd.push("DESC");
        
        var n = new Array();
        n.push("Description");
        n.push("ASC");
        
        orden.push(cd);
        orden.push(n);
  
  querys.push("select Id, Description, ClosedDate from Case")
  ordenes.push(orden);
  
        sforceClient.QueryInParallel(querys, ordenes, alldoneCallback);
    }
    
 function alldoneCallback(){
  _opps = sforceClient.sobjectsArray[0];
  _cases = sforceClient.sobjectsArray[1];
  
  alert("I have it all!.\nOpportunities: " + _opps.length + "\nCases: " + _cases.length);
 }
 
    function displayResults(opps) {
        var out ="";
        out += "<table border='1'><tbody>";
        for (var i=0; i<opps.length; i++){
            out += "<TR><TD>" + opps[i].get("Name") + "</TD><TD>" +opps[i].get("CloseDate")+ "</td><TD>" +opps[i].get("Amount") + "</TD></TR>";
        }
        out += "</tbody></table>";      
        document.getElementById("maindiv").innerHTML = out;
    }

    function sortSObject(a, b){
        for (var i=0; i<sforceClient.sortArray.length; i++){
            var va = a.get(sforceClient.sortArray[i][0]);
            var vb = b.get(sforceClient.sortArray[i][0]);
            
            var s = 1;
            
            if (sforceClient.sortArray[i][1] == "DESC"){
                s = -1;
            }
            if (va > vb){
                return 1 * s;
            } else {
                if (va < vb){
                    return -1 *s;
                }
            }
        }
        
        return 0;
    }
    
    sforceClient.QueryAllSorted = function(soql, sort_orders) { 
        sforceClient.sortArray = sort_orders;
        
        sforceClient.sobjects = new Array();
        this.Query(soql, queryCallback);
    }
    
    sforceClient.sortArray = new Array();
    
    queryCallback = function(ret) {
        if (ret.size > 0){
            sforceClient.sobjects = sforceClient.sobjects.concat(ret.records);
            if (ret.done == false){
                    sforceClient.queryMore(ret.queryLocator, queryCallback)
            } else {
                displayResults(sforceClient.sobjects.sort(sortSObject));
            }
        }
    }
 
 sforceClient.sobjectsArray = new Array();
 sforceClient.sobjectsDoneArray = new Array();
 sforceClient.sortsArray = new Array();
 sforceClient.soqlsArray = new Array();
 parallelCallback  = null;
 
 sforceClient.QueryInParallel = function (soqlArray, sort_ordersArray, callback){
  //verify input data
  if (soqlArray.length != sort_ordersArray.length){
   return;
  }
  //define paralllel data
  sforceClient.sobjectsArray = new Array(soqlArray.length);
  sforceClient.soqlsArray = new Array(soqlArray.length);
  sforceClient.sortsArray = new Array(soqlArray.length);
  
  //altar all querys are finished callback will be called
  sforceClient.parallelCallback = callback;
  
  //init parallel data
  for (var i=0; i<sforceClient.sobjectsArray.length;i++ ){
   sforceClient.sobjectsArray[i] = new Array();
   sforceClient.sobjectsDoneArray[i] = false;
   
   sforceClient.sortsArray[i] = sort_ordersArray[i];
   sforceClient.soqlsArray[i] = soqlArray[i];
   
   sforceClient.Query(soqlArray[i], innerCallback);
  }
  // start all querys in parallel
  for (var i=0; i<sforceClient.sobjectsArray.length;i++ ){
   sforceClient.Query(soqlArray[i], innerCallback);
  }
  
 }
 
 innerCallback = function (ret){
  // find index of soql
  var ix = -1;
  var founded = false;
  for (var i=0; sforceClient.soqlsArray.length; i++){
   if (ret.SOQL.toUpperCase() == sforceClient.soqlsArray[i].toUpperCase()){
    ix = i;
    founded = true;
    break;
   }
  }
  
  if (!founded){
   alert("Could not find soql in parallel soql array. soql: " + ret.SOQL);
   return;
  }
  //process query result
  if (ret.size > 0){
   
            sforceClient.sobjectsArray[ix] = sforceClient.sobjectsArray[ix].concat(ret.records);
            if (ret.done == false){
                    sforceClient.queryMore(ret.queryLocator, innerCallback)
            } else {
    //this query is finished
    sforceClient.sortArray = sforceClient.sortsArray[ix];
                sforceClient.sobjectsArray[ix] = sforceClient.sobjectsArray[ix].sort(sortSObject);
    sforceClient.sobjectsDoneArray[ix] = true;
    
    //check if all is done
    var alldone = true;
    for (var i=0; i<sforceClient.sobjectsDoneArray.length; i++){
     if (!sforceClient.sobjectsDoneArray[i]){
      alldone = false;
      break;
     }
    }
    //if all is done then call the main callback.
    if (alldone){
     sforceClient.parallelCallback();
    }
            }
        }
  else{
   if (ret.faultstring != null && ret.faultstring.length > 0)
    alert(ret.faultstring);
  }
 }
    </script>
</head>
<body onLoad="load();">
    <div id="maindiv"></div>
</body>
</html>
</html>

 

Federico LarsenFederico Larsen
There was a bug there, now it works fine. I leave the code here if someone is interested.

Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>  
    <title>sort</title>
    <script src="https://www.salesforce.com/services/lib/ajax/beta3.3/sforceclient.js—browser=true" type="text/javascript"></script>
    <script type="text/javascript">
 var _opps = null;
 var _cases = null;
 var _accounts = null;
 
    function load(){
        sforceClient.registerInitCallback(main);
        sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_70}", true);
    }
    
    function main(){
  var querys = new Array();
  var ordenes = new Array();
  
        var orden = new Array();
        var cd = new Array();
        cd.push("CloseDate");
        cd.push("ASC");
        
        var n = new Array();
        n.push("Name");
        n.push("DESC");
        
        orden.push(cd);
        orden.push(n);
  
        querys.push("select Id, Name, CloseDate, Amount from Opportunity")
  ordenes.push(orden);
  
  var orden = new Array();
        var cd = new Array();
        cd.push("ClosedDate");
        cd.push("DESC");
        
        var n = new Array();
        n.push("Description");
        n.push("ASC");
        
        orden.push(cd);
        orden.push(n);
  
  querys.push("select Id, Description, ClosedDate from Case")
  ordenes.push(orden);
  
  var orden = new Array();
  
        var n = new Array();
        n.push("Name");
        n.push("ASC");
        
        orden.push(n);
  
  querys.push("select Id, Name from Account")
  ordenes.push(orden);
  
        sforceClient.QueryInParallel(querys, ordenes, alldoneCallback);
    }
    
 function alldoneCallback(){
  _opps = sforceClient.sobjectsArray[0];
  _cases = sforceClient.sobjectsArray[1];
  _accounts = sforceClient.sobjectsArray[2];
  
  alert("I have it all!.\nOpportunities: " + _opps.length + "\nCases: " + _cases.length+ "\nAccounts: " + _accounts.length);

 }

    function sortSObject(a, b){
        for (var i=0; i<sforceClient.sortArray.length; i++){
            var va = a.get(sforceClient.sortArray[i][0]);
            var vb = b.get(sforceClient.sortArray[i][0]);
            
            var s = 1;
            
            if (sforceClient.sortArray[i][1] == "DESC"){
                s = -1;
            }
            if (va > vb){
                return 1 * s;
            } else {
                if (va < vb){
                    return -1 *s;
                }
            }
        }
        
        return 0;
    }
 
 sforceClient.sobjectsArray = new Array();
 sforceClient.sobjectsDoneArray = new Array();
 sforceClient.sortsArray = new Array();
 sforceClient.soqlsArray = new Array();
 parallelCallback  = null;
 
 sforceClient.QueryInParallel = function (soqlArray, sort_ordersArray, callback){
  //verify input data
  if (soqlArray.length != sort_ordersArray.length){
   return;
  }
  //define paralllel data
  sforceClient.sobjectsArray = new Array(soqlArray.length);
  sforceClient.soqlsPending = soqlArray.length;
  sforceClient.sortsArray = new Array(soqlArray.length);
  
  //altar all querys are finished callback will be called
  sforceClient.parallelCallback = callback;
  
  //init parallel data
  for (var i=0; i<sforceClient.sobjectsArray.length;i++ ){
   sforceClient.sobjectsArray[i] = new Array();   
   sforceClient.sortsArray[i] = sort_ordersArray[i];
   sforceClient.soqlsArray[i] = soqlArray[i];
  }
  // start all querys in parallel
  for (var i=0; i<sforceClient.sobjectsArray.length;i++ ){
   sforceClient.Query(soqlArray[i], innerCallback);
  }
  
 }
 
 innerCallback = function (ret){
  // find index of soql
  var ix = -1;
  var founded = false;
  for (var i=0; sforceClient.soqlsArray.length; i++){
   if (ret.SOQL.toUpperCase() == sforceClient.soqlsArray[i].toUpperCase()){
    ix = i;
    founded = true;
    break;
   }
  }
  
  if (!founded){
   alert("Could not find soql in parallel soql array. soql: " + ret.SOQL);
   return;
  }
  //process query result
  if (ret.size > 0){
   
            sforceClient.sobjectsArray[ix] = sforceClient.sobjectsArray[ix].concat(ret.records);
            if (ret.done == false){
                    sforceClient.queryMore(ret.queryLocator, innerCallback)
            } else {
    //this query is finished
    sforceClient.sortArray = sforceClient.sortsArray[ix];
                sforceClient.sobjectsArray[ix] = sforceClient.sobjectsArray[ix].sort(sortSObject);
    if (--sforceClient.soqlsPending == 0){
     sforceClient.parallelCallback();
    }
            }
        }
  else{
   if (ret.faultstring != null && ret.faultstring.length > 0)
    alert(ret.faultstring);
  }
 }
    </script>
</head>
<body onLoad="load();">
    <div id="maindiv"></div>
</body>
</html>
</html>