I have a Mariadb table with a varchar field. I’m having trouble sorting it. I’m sure this has to do with the varchar status but I don’t know what is the best way to convert it during the API database pull.
Any suggestions and guidance is appreciated…
Hi.
Sorting works fine in Wappler query builder.
What is the data that you have as varchar in db, what is the sorting you get and what is the expected output?
If you need to convert/cast the column, you will have to use custom query.
If you store your numbers in a varchar field they will be treated as strings, as they are strings, which means they will also be sorted as strings.
You could create a view in the DB with the column CAST as a suitable number. That way you can stick to standard queries in Wappler.
Thanks everyone - I did see the cast/abs guidance in stackoverflow but couldn't figure out how add it to my API.
As the amount of data isn't large, I just added a second field and made it an integer. I'll figured I will end up with two views & APIs.
How can the CAST function be added to the sorting function on the Wappler side?
Custom query. Such functions/options are not available in the query builder UI.
As an alternative to using custom queries, views or extra APIs etc, you could create an extra field specifically for sorting, defined as a virtual column, eg:
right(concat('0000',numberAsText),5)
… where numberAsText is your existing field. Obviously, you might have to tweak the expression but this would result in:
00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00012
If defined as VIRTUAL as opposed to STORED, the extra field wouldn’t take any extra space. (I don’t know if this affects sorting speed.)
