Array of SysIds (in order) not showing order when in gliderecord "while" loop

[TCole94]

I have built a custom array that contains sysIds (same table) in the exact order I wish the gliderecord query should execute during the "while" loop. However, when inserting the array within the query, the records are not showing in the order in the array. When I iterate the array and log the sysIds, the correct order is showing up. Examples below:

THIS WORKS (order is correct)

var arr = [];

var str = "";

for(var i in arr){

str += arr[i] + "\n"

}

THIS DOES NOT WORK (incorrect order)

var arr = [];

var gr = new GlideRecord(tablename);

gr.addQuery("sys_id",arr);

gr.query();

while(gr.next())(

//this produces incorrect order but contains correct records

)

Does anyone have an idea why the "while" loop is not executing the array by the sysID order and the "for" loop is?

Need to print out an email template with an exact order but the while loop is throwing it off.

[DrewW]

The system will always return the data based on the random order of how it got it from the database.  If you need your data ordered then you need to use GlideRecord.orderBy("<FIELD>") or GlideRecord.orderByDesc("<FIELD>").

[TCole94]

There are parent/child records in this array to orderBy isn't working either unfortunately. The order in the array would help with the numbering sequence as well such as:

1 = parent xxx

1.1 = child of parent xxx

1.2 = child of parent xxx

2 = parent xyz

2.1 = child of parent xyz

[DrewW]

Based on the limited code you provided you have just a single dimension in your array and all of the sys_id's are from a single table.  So without knowing the data and having to guess you should still be able to order the data you will just need to order on multiple columns. Like the Parent column then the number column or something.  Otherwise you are going to have to do multiple queries against the DB to get things into the order you would like.

[Nick Parsons]

As mentioned, if you don't specify a sorting order the order that the records are iterated in will be somewhat random. Consider doing one of the following:

1. As you build your array of sorted sys_ids, can you also determine which to keep at that point, rather than querying all the sys_ids again later?

2. In your while loop, push the sys_ids of the items into a new array, and then sort this new array based on the order of the sys_ids in your ordered array with:

while(gr.next()) {
  ...
  newArr.push(gr.getValue("sys_id"));
  ...
}
...
var sorted = newArr.sort(function(a, b) {
  return orderedArr.indexOf(a) - orderedArr.indexOf(b); 
});
// ... use `sorted` ...

you can further optimise that by making a "sys_id -> index" map of your ordered array before the .sort() to avoid a linear time look-up each iteration of your the sort-loop by the indexOf() call.

3. Use some sort of insertion sort logic during your while loop, this avoids an additional loop over your items to sort later, and instead you sort as you loop through them in your while-loop (ie: it's an online-algorithm for sorting). Again, you can create a look-up (with an object / ES6 Map) if you don't want to call indexOf() each time and want constant-time look-up times to help optimise this if your data set is large:

function insertionSort(sortedArr, sysId) {
  var sysIdIdx = ordered.indexOf(sysId);
  for(var i = 0; i < sortedArr.length; i++) {
    var currIdx = ordered.indexOf(sysId);
    if (sysIdIdx <= currIdx)
      break;
  }
  sortedArr.splice(i, 0, sysId); // insert the sysId into its sorted position
  return sortedArr;
}

var sorted = [];
while (gr.next()) {
  ...
  insertionSort(sorted, gr.getValue("sys_id"));
  ...
}
// ... use `sorted` ...