Xojo2024R2: WebListBox with DataSource using Postgres Sorting issue

Targets Web
1

I see that WebListBox using DataSource in the example supplied with Xojo is capable of sorting by simply clicking on column heading. That is what I expect to see. However, I don’t see it happening in my web app. I use ColumnData to setup columns using the code:

col = New WebListboxColumnData
col.DatabaseColumnName = "name" // the name of the field in your database or data source
col.Heading = "Name" // the name that appears above the column
col.Sortable = True // Whether or not the column is sortable
col.SortDirection = Weblistbox.SortDirections.Ascending // The default sort direction for the column
col.Width = "200"
cols.Add(col)

The column “Name” is supposed to be sortable, I expect the list to sort when I click on “Name” heading. However, nothing is happening except of the little arrow change Up/Down on the column heading. I have checked the settings of the WebListBox as well, it is enabled.

According to latest suggestions I only have modified ColumnData, RowCount and RowData methods. I don’t have the SortedPrimaryKeys and UnsortedPrimaryKeys methods anymore.

What else might be happening there?

2

The RowData method comes with a parameter with the sort String you have to use in your SQL query. Are you using it?

3

In this particular case the query has order by clause such as:

vQuery = vQuery + " ORDER BY m.code, a.custodian_account_code"

So, are you suggesting that I need to substitute this with the name of the column clicked as stored in SortColumns?

Screenshot 2024-06-27 at 2.36.28 PM
Screenshot 2024-06-27 at 2.36.28 PM2078×1156 231 KB

4

My guess is that your “ORDER BY …” is overriding what Xojo is trying to do.

1 Like
5

OK, finally I am beginning to enjoy this excercise.
I have modified QueryPageDataSource.RowData method by substitution of order by clause as:

Var vPos As Integer
Var sql As String 

sql = mQuery


vPos = sql.IndexOf("ORDER BY")
If vPos > 0 Then
  sql = sql.Middle(0,vPos)
End If
If SortColumns.Length > 0 Then
  sql = sql + " ORDER BY " + SortColumns
End If

This now seems to be working as expected, I click on the column and get the list sorted, very nice, thank you.

Now having said this, I need to modify stored queries (the queries this is going to use are stored in database), making sure the names are not ambiguous such as “name” and there is no duplication of names in the returned data set (sadly I have to deal with this “inheritance” as queries were written long time ago with “free sprit” approach)

Anyway, thanks a lot for solving this little mystery to me. I wish I could learn it the easy way without bother you guys, but hey, it is what it is. Thanks again for your patience.

2 Likes