Want to sort by numeric value of a string field on only 1 of my Bootstrap Table columns

Wappler General How To
#1

similar to this: Table Numeric Sort Order

Sorting has been working fine, I just want to refine the sort on a column that is storing a numeric sequence in a text field.

I don’t want to lose functionality of sorting any of the columns with the Bootstrap 5 table. Currently there are 9 sortable columns and I only need this to affect 1 of those cols.

So, if this is even possible, how will I setup the Custom Query to use
"order by abs(regnum) " + the $dir param, for just this one column sort,
and order defined by $sort / $dir for the other columns?

What I tried was modifying the code on the on-click of the column header.

image
image1312×144 33.5 KB

and then ultimately this:
image
image850×173 32.8 KB

But the framework still didn’t seem to like this. This would be the easiest fix.

This changes the URL to this
image
and this
image
There IS a request to the server, but the data comes back in the same order each time I click on this special column.

I looked on my localhost log and see this:
image
I think what I want to see is in this params field, but am not sure.

So I check my SqlProfiler. I see the SQL statement, but no ORDER BY:

exec sp_executesql @statement=N'select event
RegNum, concoursclass, concoursjudged, vehtype, vehcondition, vehprep, 
dispby, veh_images, image1, vehslug, eventcode
from  REGVEH
inner join ENTRY2 on ENTRY2.RegNum = RegNum 
where (ENTRY2.eventcode = @p0)',@params=N'@p0 nvarchar(2)',@p0=N'HR'

This lines up perfectly with the localhost log.
But I think the funky $sort string messes up the framework so it doesn’t add anything to the server request. Which confirms why the table doesn’t change because the exact same response comes back since no ORDER BY is being sent.

#2

Would it be much simpler to modify your base SQL to cast as an int in the select statement

#3

use an alias for the column in your custom query

i.e.

cast(regveh.RegNum as int) AS regvehint

Your sort buttons will need to be changed to set ‘regvehint’ rather than the cast statement when clicked (or rebuild the table using the Wappler generator)

#4

I think that is happening in the select statement. The problem is that the column is taking the expression as its name rather than an alias

#5

ok, appreciate the replies.
but start over… its NOT a Custom Query (yet).
I can do that easy enough (ok, 5 mins maybe to redo), but didn’t know about Sorting on a Custom. I see the trick now with the Alias.

I was hoping to modify the Sort button AS IS using the Std Database Query.