Sorting in queryAll

[knadh]

Sorting in queryAll

[Valkhan]

@w35l3y As promised i've tested with your suggestion and it worked by inverting the values on 'sort'.

In short, if I want to sort by column A,B and C I should sort by C,B and A like:
sort: [['C','ASC'],['B','ASC'],['A','ASC']]

Hope it helps anyone else in the future as you've helped me now.

[Valkhan]

@w35l3y
About sorting: I'll try by inverting as you've suggested: "Hour and Date", i confess that inverting it didn't cross my mind.
About sanitizing: I don't sanitize the value while sorting, but after and IF they're used in views for the End User.
About datetime: The system i'm working on is a Legacy one, they don't user timestamps, i'm introducing it in new routines but as of now we couldn't change this data structure.

[w35l3y]

Your field must contain only numeric values (not stringified numbers!).
You should update them if they really aren't numbers. Once done, just make sure to only insert numbers.

db.queryAll("tableName", {
  //...
  sort: [
    ["fieldName", "DESC"]
  ]
});

[Valkhan]

The sorting is running fine to me when using only one sorting condition, if I use 2 conditions as:

sort: [['date','ASC'],['hour','ASC']]

It doesn't work, seems like it considers only the last position of the array*, in this case ['hour','ASC'], am I doing something wrong?

I've taken a look at the source code:

// there are sorting params
if(sort && sort instanceof Array) {
      for(var i=0; i<sort.length; i++) {
           results.sort(sort_results(sort[i][0], sort[i].length > 1 ? sort[i][1] : null));
      }
}

The problem is probably because (assumption) the results aren't "Caching" previous sorting, it probably would be best if all possibilities were given at once instead of doing one by one, something like:

// there are sorting params
if(sort && sort instanceof Array) {
      var sortCondition = [];
      for(var i=0; i<sort.length; i++) {
          // sortCondition.push("field" [if ASC >= else <=])
      }
      sortCondition.join(" AND ");
      // call a sorting function on results that will consider all given options instead of overwritting with the last one
}

[Valkhan]

Well, i've taken the liberty of altering my source code to suit the need for mult-sorting as stated above, the solutions was quite simple:

I've changed the "select" function to pass the sort array:

// there are sorting params
if(sort && sort instanceof Array) {
    results.sort(sort_results(sort));
}

Then I've changed the sorting function "sort_results":

// sort a result set
function sort_results(sort) {
    return function(x,y) {
        var ret = 0;
        for (var a = 0; a < sort.length; a++) {
            var field = sort[a][0];
            var order = sort[a][1];
            var tmp = function(x,y) {
                var v1 = typeof(x[field]) === "string" ? x[field].toLowerCase() : x[field],
                    v2 = typeof(y[field]) === "string" ? y[field].toLowerCase() : y[field];
                if(order === "DESC") {
                    return v1 == v2 ? 0 : (v1 < v2 ? 1 : -1);
                } else {
                    return v1 == v2 ? 0 : (v1 > v2 ? 1 : -1);
                }
            };
            ret+= tmp(x,y);
        }
        return ret >= 0;
    }
}

The complete files was forked on my account, feel free to test and evaluate if this could be added to your main project.

I would also add a function to "sanitize" the values when sorting, this function by default should do nothing but would be an entry point to a custom function to remove special chars for example.

[knadh]

@Valkhan Can you send a pull request?

[Valkhan]

@knadh I did some more testing with my "solution" but it did not work in all cases, just in the case I was testing :(

I found out this function that does the trick: https://bl.ocks.org/ryankane/d344314e7e45d26f3522, I'm using it in my project so I didn't dig too deep into this.

[w35l3y]

According to the documentation ( https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Array/sort ), the current implementation should work flawless.

I think the problem is because you should ALWAYS list sorting conditions from most specific to less specific.
In your case, hour should go first. [['hour','ASC'],['date','ASC']]

If this isn't the case, then it is probably because the way you store these values.
'date' shouldn't include time (as you decided to store it separately) and should be in ISO8601 format or timestamp.

[w35l3y]

"Sanitizing" the values while sorting isn't recommended for performance reasons.
You should consider storing them sanitized already.
Formatting should only be applied when showing the result or in fields that doesn't require operations at all like sorting, filtering, grouping, validating, etc.

[w35l3y]

This is great to know that it worked @Valkhan
That's right, always from the most granular units to less granular units.
Seconds > Minutes > Hours > Days > Weeks > Months > Years

There are 24 hours in 1 day, so hour is the most granular unit.
Later sorting conditions tend to treat equal values more often, so the movements of the elements occur less.

This issue may be closed.