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