DB Data Soruce %%keywords%%

(Squiz) #1

I’m trying to create a DB Data Source using GET parameters to modify the functionality of the query on the fly. Essentially I’m trying to implement a server-side Datatables processing thing close to the level of functionality in that PHP script.


I’ve got pagination and sorting, but I’m having difficulty with ordering. I’ve tried the following:


    ORDER BY %%iSortCol_0%% %%sSortDir_0%%


Which obviously doesn't work, because after escaping it equates to something like:

    ORDER BY '1' 'asc'


Sadly I don't have the first clue where to go from here. I've tried to_number(%%iSortCol_0%%), which converts the 'string' to a number which doesn't throw any errors but still fails to sort.

I just can't think of any way to programmatically determine the sort order and direction based on these strings in SQL. We're eventually going to end up with thousands and thousands of rows, so I don't particularly want to have to pull the entire lot down into Matrix to sort it. So with that in mind, I'm wodnering if anyone else might have any ideas as to what else I can try?

Cheers,
Ash.
(Anthony Barnes) #2

If there isn't a SQL guru handy who can think of a neat query to get around this you could always create 2 data source assets with the versions of the query with ASC and DESC then supply the dynamic root node to the asset listing based on the sorting selection (or even supply it by 'default' as a GET parameter in a nest content area).

(Squiz) #3

I'm beginning to think it probably can't be done in any elegant way. Might just disable sorting and rely further on the search functionality.


Cheers for the ideas. :slight_smile: