I am build an application in OIC VBCS and one aspect is showing a table of results. I've tried the progressive scrolling option but can't get that to work (I presume because of the issue I'm trying to resolve) so in the mean time added prev and next buttons to adjust the offset by the page row limit.
What I want to do next is add in filtering and sorting (I'll start with sorting). The database is a DBaaS database with ORDS installed so I have REST APIs to access the database tables. I can test using queries, e.g. {"column_a":{"$like":"%LXV%"},"$orderby":{"column_a":"asc"}} and that works fine if I add a q parameter in the service connection.
I have a service data provider variable to fetch from the source and mapped the paging, filter, sort control but they don't seem to do anything (hence manually adding offset and limit parameters with prev and next buttons).
From what I understand so far, a SDP will send a sortBy parameter rather than forming the parameters as per what ORDS REST APIs expect (e.g. q={filter, $orderBy…}. From what I have read so far the custom transform js should be able to do that, but I am yet to find a worked example and the documentation is spartan at best.
Is this a case that its not possible in the transform js without a total re-write of the default to convert to using the "q=" format parameter or is it a case that I need to re-develop the database ORDS connections to use PL/SQL to translate the requests to SQL queries? It feels like the latter is a bit of a sledge hammer to crack a nut. I can't find any proper documentation on how to use the "sort(configuration, options, transformsContext) " method and translate that along with the filter (given that ultimatley they are both feeding the same parameter).
Am I missing something simple in my ORDS setup that can control the parameter setup to use orderBy rather than q?