Sort columns when server side pagination is enabled

[Karan]

Hello,

I want to sort data based on the creation date, but nothing happens when I click on the header.
I have enabled server-side pagination.
And we have an ordering option available on the API. Just not sure how to trigger the API when header is clicked

[lauren.gus]

@Karan

Hey there Thanks for reaching out! Just to confirm, toggling server-side pagination on should disable column sorting. Here is a link to the section in our docs on Server side pagination with dynamic column sorting

[Karan]

Ok, Thanks @lauren.gus

[duc-gp]

hello, it seems the section you describe does not longer exists on the page you linked.
do you have any documentation about best practices to handle sorting in server-side pagination?

[Kabirdas]

Hey @duc-gp!

Here's an updated link to our server-side pagination docs. Typically, you'll want to use the sortedColumn and sortedDesc properties of the table as inputs for the query itself and have it trigger off of changes in those properties. Is there any particular issue you're running into when setting it up?

[cubeadinda]

It would be great if an example is provided in that docs since it's quite common. I can't even add the following to my SQL query:

{{table1.sortedColumn !== null ? `order by ${table1.sortedColumn} desc` : ''}}

Also, how can I put the desc / asc dynamically?

Appreciate your help.

[Kabirdas]

Hey @cubeadinda

It looks like there's currently a bug with server-side paginated tables that's interfering with setting up dynamic sorting but I can report back here when it has been fixed!

To give an example of how this should work:

Dynamically setting column sort order in SQL mode depends on whether or not you have converting queries to prepared statements enabled for your resource (it is by default). Meaning your query is converted to a prepared statement and every transformer is passed as a parameter to prevent SQL injection attacks. Typically, SQL prepared statements don't let you set ORDER BY clauses using query parameters, see this PostgreSQL post for an example.

If you turn prepared statements off you can dynamically create any SQL string and pass it through. However, with it turned on, you might need to do something like the following for each of your sort columns:

CASE WHEN {{ table.sortedColumn == ‘name’ && tblResults.sortedDesc }} THEN name END DESC,
CASE WHEN {{ table.sortedColumn == ‘name’ && !tblResults.sortedDesc }} THEN name END ASC

[Jamal_Saleh]

@Kabirdas

sortedColumn

when calling the table you dont have property of sortedColumn or sortedDesc. i am not sure if your referencing the old table or the new one but the new doesnt have that capability

[Justin_Greywolf]

Unfortunately, most of the documentation on server-side pagination appears to be focused on the old table. So far I have been able to find that the property you are looking for is sortArray on the new table.

New Table component sorting by column not working

Hi Barry! With server-side paginated tables (as your table seems to be set up), it's best to apply server-side sorting -- I'm guessing you want to sort your entire data set instead of just the page. You can do this by accessing the sortArray property on the table to get the currently table's active sort (indicated via headers), and then use that property in your query to sort the data. [image] If your resource has prepared statements disabled, you can reference these directly in your query li…

[Vitor_Dal_Pra]

Hey, @Kabirdas. I'm trying to use the the example you described to sort columns with server side pagination, but I found that I cannot order by a calculated field. Do you know if there is workaround for that? Thanks.

Also, it seems like the new table does not have the sortedColumn property, but one called sortArray.

[Jack_T]

Hello @Vitor_Dal_Pra!

Unfortunately Kabirdas is no longer with the team.

There should be some type of workaround, if you are running calculation logic on the front end, then you will have to sort the data with a JS Query after that calculation logic has run.

Given that server side pagination will break up the un-calculated data as it comes in from the backend, you won't be able to sort or run the calculation on the backend (unless the values are calculated in the data base but that is a whole other story )

[Vitor_Dal_Pra]

Hey @Jack_T!

I couldn't understand why, but it didn't work when I used something like:

ORDER BY {{table.sortArray[0].columnId}} {{table.sortArray[0].direction}}

So in the end, I did the following solution. Not sure if there is a less verbose way, but it did work:

ORDER BY 
  CASE 
    WHEN {{table.sortArray[0].columnId == 'field_a' && table.sortArray[0].direction == 'desc'}} THEN field_a
    WHEN {{table.sortArray[0].columnId == 'field_b' && table.sortArray[0].direction == 'desc'}} THEN field_b
  END DESC,
  CASE 
    WHEN {{table.sortArray[0].columnId == 'field_A' && table.sortArray[0].direction == 'asc'}} THEN field_a
    WHEN {{table.sortArray[0].columnId == 'field_b' && table.sortArray[0].direction == 'asc'}} THEN field_b
  END ASC

Best,
Vitor

[Jack_T]

Hmm that is odd that the first snippet didn't work, not sure why.

Glad you were able to get this working! Thank you for sharing the code snippet, that could definitely be useful for others that are also looking to implement dynamic column sorting!