I am exploring the idea of moving “live” SQL queries from RowData method of the DataSource class into Thread.Run event, this implies caching of the results in an array in the Thread class so that the data reloading (ex: when scrolling through the list) is quick (this is about 500+ rows and complex SQL query). The problem I have now pertains to sorting the list by clicking on column.
Since the result set is now cached in thread I don’t need/want to issue SQL query with modified ORDER BY clause but rather need to sort the cached data in WebListBoxRowData array knowing column name. Where can I find the info to learn how to do it?
Can someone please help with this?
Well, I have managed to come with the custom method that can do the sorting, here is an example of string type column:
Public Sub SortWebListBoxRowCacheByColumn_string(Byref data() As WebListBoxRowData, columnName As String, ascending As Boolean = False)
Var keys() As String
Var indexes() As Integer
// Step 1: Extract sort keys and track original indexes
For i As Integer = 0 To data.LastIndex
Var row As WebListBoxRowData = data(i)
Var key As String
Try
Var raw As String = row.Value(columnName)
If InStr(raw, "Xojo-Style|") = 1 Then
// Extract JSON portion after second "|"
Var parts() As String = raw.Split("|")
If parts.Ubound >= 2 Then
Try
Var js As Dictionary = ParseJSON(parts(2))
If js.HasKey("xojo_value") Then
key = Str(js.Value("xojo_value"))
End If
Catch
key = ""
End Try
End If
Else
key = raw
End If
Catch
key = ""
End Try
keys.Add(key)
indexes.Add(i)
Next
// Step 2: Sort the index array based on keys
keys.SortWith(indexes)
//indexes.SortWith(keys)
If Not ascending Then
Var reversed() As Integer
For i As Integer = indexes.LastIndex DownTo 0
reversed.Add(indexes(i))
Next
indexes = reversed
End If
// Step 3: Rebuild sorted data
Var sorted() As WebListBoxRowData
For Each i As Integer In indexes
sorted.Add(data(i))
Next
data = sorted
// 2025.07.07 <gp> created
End Sub
In my new DataSource class in RowData method I can now call this method:
Var cache() As WebListBoxRowData = cacheThread.MyCache
If pSortColumnStr <> "" Then
Var parts() As String = pSortColumnStr.Split(" ")
Var columnName As String = parts(0).Trim
Var descending As Boolean = parts.Count > 1 And parts(1).Trim.Uppercase = "DESC"
SortWebListBoxRowCacheByColumn_string(cache, columnName, not(descending))
End If
The above seems to be working for columns that are strings, I just need to come up with similar methods for numbers, dates, etc.
In GitHub: cubesql/webadmin we have a similar situation.
There the SQL Queries for Database Server Information can’t be paged, filtered or sorted - but that’s what the WebDataSource requires.
If you’d like to see the approach there, look into the files DataSourceColumn and cntDatasourceBase (which implements WebDataSource).
You’ll see that the original RowSet is being filled into an own (Dictionary based) structure. The DataSourceColumn defines what type a column is (for sorting/filtering).
Should you want to try or debug CubeSQLWebAdmin yourself, but you don’t have cubeSQL installed - then the Xojo Blog: Docker, Database Servers and Xojo explains how you can easily set this up with Docker Compose (and throw it away anytime) .