This post ("Order by" not working for dynamic dropdown value (MySQL)) brought up the problem with not being able to dynamically sort a query, and it failed utterly when using SQL with JSON (alaSQL) query.
I found a solution that will work with any SQL query by using a transformer. Full credit goes to Ege Özcan from this post: javascript - Sort array of objects by string property value - Stack Overflow
Put the following code into your Scripts and Styles:
function dynamicSort(property) {
var sortOrder = 1;
if(property[0] === "-") {
sortOrder = -1;
property = property.substr(1);
}
return function (a,b) {
/* next line works with strings and numbers,
* and you may want to customize it to your needs
*/
var result = (a[property] < b[property]) ? -1 : (a[property] > b[property]) ? 1 : 0;
return result * sortOrder;
}
}
function dynamicSortMultiple() {
/*
* save the arguments object as it will be overwritten
* note that arguments object is an array-like object
* consisting of the names of the properties to sort by
*/
var props = arguments;
return function (obj1, obj2) {
var i = 0, result = 0, numberOfProperties = props.length;
/* try getting a different result from 0 (equal)
* as long as we have extra properties to compare
*/
while(result === 0 && i < numberOfProperties) {
result = dynamicSort(props[i])(obj1, obj2);
i++;
}
return result;
}
}
This use the following in your query's transformer:
return data.sort(dynamicSort({{selSort1.selectedItem.value}}));
or
return data.sort(dynamicSortMultiple({{selSort1.selectedItem.value}}, {{selSort2.selectedItem.value}}));
dynamicSort does one level of sorting and dynamicSortMultiple does two. With a little work this can be extended to do more than two levels if you like.
The value you pass should be the exact property name ('venue_name') and if you want a reverse sort prepend a - ('-venue_name')