I have a column in our application that primarily displays numeric values for prices. Occasionally, this column needs to show text (e.g., "Inquire for price") when a numeric value is unavailable, typically when a vendor requires direct inquiries. Currently, I convert the entire column to a string type to accommodate text entries alongside numeric values.
Issue: The challenge arises with the default sorting behavior of this mixed-data column. Text entries ("Inquire for price") automatically sort to the top of the column, reducing the visibility of more relevant numeric data.
Objective: I seek a method to assign a measurable value to these text entries so they can be sorted as if they were numeric. Ideally, these text entries should appear towards the bottom of the column in sorted views, allowing numeric values to remain prominently at the top.
Proposed Solution: Is it possible to implement a sorting logic where text entries are assigned a nominal high numeric value during sorting operations? This adjustment would ensure that numeric prices are prioritized in the display order.
Expected Outcome: With the proposed change, users would see the most relevant numeric price data first, with text entries sorted to the bottom, enhancing data usability and relevance.
these are the 5 basic sorting algorithms (there are lots more, but these will be the goto for 90% of situations). So, which to choose.... there are 2 main factors to consider. How likely data will naturally be somewhat sorted/how much of the data is already sorted and how much data needs to be sorted (count/array length) with the later having a bigger impact. If you're not too worried about any of that Insertion Sort or Quick Sort will probably be the preferred ones. Insertion Sort is fast and is best for small data sets or when the data is likely to be nearly already sorted. Quick Sort is also fast for small data sets, but with this one if 2 items have the same value the order isn't guaranteed to be preserved. so if list item #2 has a value of 55 and list item #8 also has a value of 55, after sorting you could have #8 appear before #2 (which is the opposite of what they were before sorting). If you don't care what order items are in if they have the same value, go with Quick Sort. Bellow is a quicksort function you can use:
const quickSort = (myArray) => {
if (myArray.length <= 1) {
return arr;
}
// this can be any of the array items, I picked the middle as it's simple.
// since we're dealing with mixed types though, we do need to check it's value and have a backup index/pivot in case of a string.
// generally you want to avoid using the first or last index as the pivot as it increases the odds of hitting a worse-case scenario of O(n^2). the majority of the time you'll get around O(n log(n))
let mid = myArray.length / 2;
let pivot = myArray[typeof myArray[mid ] !== 'string'? mid : 0];
let leftArray = [];
let rightArray = [];
for (let i = 1; i < myArray.length; i++) {
// automatically add string values to the end of the sorted list
if (typeof myArray[i] === 'string'){
rightArray .push(myArray[i]);
}
// sort numeric values
else{
if (myArray[i] < pivot) {
leftArray .push(myArray[i]);
} else {
rightArray .push(myArray[i]);
}
}
}
return [...quickSort(leftArray ), pivot, ...quickSort(rightArray )];
};
it's normally suggested to use the median-of-3 (median of the first, last and mid values) but this becomes more difficult when you could pick a string value and accidently try to calculate the median. you'd get a value for it, there's just REALLY good odds of it ending up being the last index, which can result in worst-case scenarios occuring way more often than they should slowing everything down. if you notice the sort getting super slow with larger arrays you can try using different values for the pivot before implementing a new sorting algo
for more sort algo implementations w JS code (and some really nifty optimizations) you can go here
edit: forgot to mention, since this doesn't reference any values not passed in as a parameter you can make this a function and reuse it wherever by simply using {{ myQuickSort(variable1.value) }}. you could probably also use a transformer, but I'm not 100% sure since I don't normally use them
First of all thank you for your time, I really appreciate it!
My question revolves around handling the sorting behavior of a mixed-data column that contains both numeric values and occasional text entries like "Inquire for price." I am looking for a way to assign a nominal high numeric value to these text entries during sorting operations, so that they appear towards the bottom of the sorted column, while the numeric values remain at the top. At least this is how I imagine it to work. And I need a way to implement this for my table.
Unfortunately I do not see how your response helps me with that.
ya, so there isn't any built-in sorting function for mixed types. the code i posted is a custom function that will sort an array both by a numeric value and a string value.
// automatically add string values to the end of the sorted list
if (typeof myArray[i] === 'string'){
rightArray.push(myArray[i]);
}
// sort numeric values
else{
if (myArray[i] < pivot) {
leftArray.push(myArray[i]);
} else {
rightArray.push(myArray[i]);
}
}
this is the part that handles sorting. you'll need to make a few changes for your data
myArray[i] is a single object in your array. you said you want to sort on prices, so you would probably want to use myArray[i].price (this is a guess, depending on how the data inside the array your sorting is structured this could be myArray[i].data.price or something. so you might end up with:
const quickSort = (myArray) => {
// an array with 1 or less items is already sorted
if (myArray.length <= 1) {
return arr;
}
let mid = myArray.length / 2;
let pivot = myArray[typeof myArray[mid] !== 'string'? mid : 0].price;
let leftArray = [];
let rightArray = [];
for (let i = 1; i < myArray.length; i++) {
// get the price for this item. we expect this to usually be numeric but if this is a string, we'll assign a numeric value depending on what the string is (this lets us keep items with the same string next to each other in the list)
let comparison_value = myArray[i].price;
// if the price is a string instead of a number
if (typeof comparison_value === 'string'){
// assign a numerical value based on the string, this determins the order in which strings are placed at the end of the sorted array.
switch(comparison_value){
case "inquire for price":
comparison_value = 9999996;
break;
case "free":
comparison_value = 9999997;
break;
case "open for offers":
comparison_value = 9999998;
break;
default:
// if the string value is unknown we automatically place it at the bottom of the sorted list.
// this is incase we forget about a possible value, this will make sure it still gets sorted
comparison_value = 9999999;
break;
}
}
// sort using either the given numeric price, or the arbitrary value we assigned above.
// larger numbers are closer to the bottom (rightArray) and smaller ones end up at the top (leftArray)
if (comparison_value < pivot) {
leftArray.push(myArray[i]);
} else {
rightArray.push(myArray[i]);
}
}
// continue sorting the left and right arrays until we can't anymore,
// at which point our array is sorted with numerical price values at the
// top and string price values at the bottom starting with 'inquire for price',
// then 'free', then 'open for offers', and finally at the very bottom we put any
// items with string values we didn't account for
return [...quickSort(leftArray), pivot, ...quickSort(rightArray)];
}
you call this function and pass it the array of objects you want to sort and it will sort in ascending order with string values at the end. assuming myVariable1 contains an array of objects which have a price you would use const mySortedArray = quickSort({{ myVariable1.value }})
the alternative to this, is to iterate over your whole array item by item and either add a new key/value to the object (price_adj or something) and assign either the numeric price or a value based on what the string is like in the switch statement above. after that, you can start sorting based on price_adj (since you know they are all numbers now). depending on how much data you have, this could be VERY memory intensive and time consuming... which is why i suggested using a custom sorting function so you can assign strings whatever value you want and sort at the same time.
thought about this for a bit, turns out there actually is a way to sort by numbers then by strings using built-in js stuff.
so, the longer way would be to use .map() then .sort(). the 1st so you can add a key/value and the 2nd to sort using this value. since both are based on loops (fancy ones for sort) the amount of time this process takes scales. you get complexity of O(n + n * log(n))
myArray.map((item) => {...item, price_adj: typeof item.price === 'string' 9999999 : item.price }).toSorted((lhs, rhs) => {
// order small to large then strings (which we've assigned the value of 9999999 so they go last)
if(lhs.price_adj < rhs.price_adj){
return -1;
}
if(lhs.price_adj > rhs.price_adj){
return 1;
}
return 0; // lhs === rhs
});
the other way I thought of would be to use .sort() with regex, I'm not great with regex though and I don't want a headache right now so hopefully someone else, or maybe you're good with it, pitches in on what it'd be. regex is fairly fast so you'd probably see a complexity closer to O(n * log(n)), but still a bit slower than the full quickSort I gave ya. myArray.sort(fancy_regex_expression)
all 3 options accomplish this.
note:
lhs = left hand side
rhs = right hand side
they refer to what's to the left and right of an operator
Unfortunately columns of tables in retool only have two default sorting options, ascending and descending.
If all the data is strings it will compare their values and order the rows accordingly. If this sorting does not have the behavior you want (you described for the text chars to be placed separately from the numbers, which you want in order with themselves) you will have to use some custom javascript in a Javascript Query block to order the array of row data to be what you want
There are lots of resources online about custom sorting algorithms, here is a useful link I found for sorting between strings and numbers.
Once you get the sorting behavior that you want, you can apply that table data on user click!
