How to sort a column which contains server-side generated values other than database values

vatco

Hi,

I am using Datatables for one of my page to display data.I am using server-side scripting to display the table.

In my HTML I have 17 columns.Some columns are fetched from the DB and others are created in the script and contain some mathematical calculations .Those are not present in any of DB fields.

My problem is that I need to sort those columns that have generated values. But this is not possible because serverSide sends the name of the column that i need to sort and in the serverside script i apply the order by in the query, but those are not present in the DB.

Does anyone knows how I can keep the serverSIde option and sort those generated columns? Or is this possible only on the client side?

colin

Hi @vatco ,

If you've enable serverSide, then all sorting/paging/filtering is the responsibility of the server, I'm afraid, it's not possible to have one column sorted by the client, and the rest sorted by the server.

How many rows are in your dataset? If it's not too many (say in the low thousands at most) you could use ajax to get the data (see examples here), but the client would still be responsible for the sorting/paging/filtering.

Hope that helps,

Cheers,

Colin

vatco

Hi @colin ,

Unfortunately my dataset has over 1 milion records, and depending of the client the query will return between 10.000 and 20.000 records. I had implemented the ajax before with all kind of filters so I could limit the informations returned with every call. But now I need to display some raw informations without any filters, so, loading 20.000 records takes 3 mins to load. All the generated columns and calculations are done on the serverside script in the loop.

Bellow there is an example of how i handle this in the serverside script:

 <cfquery name="gettabledata" datasource="#application.dsn#">
    WITH Rows AS
          (
    SELECT  column1, column2, column3 from table 1 left join table2 
  where column1 = 1 and column2 = active 
  ROW_NUMBER() OVER(ORDER BY #sort# #OrderFilter#) as [Row] )

  select * from Rows
  </cfquery>```


<cfquery datasource="#application.dsn#" name="qCount">
        SELECT COUNT( DISTINCT column1) as total
        FROM   table1
        where table1.clientID=#client.userclientid#
        and  table1.active=1
    </cfquery>

  <cfset data = [] />
  <cfset c=1>

<cfloop query="gettabledata" group="column1" startrow="#start+1#" 
endrow="#start+lenght#">

        <cfif trim(cty) is "GB">
            <cfset pound = "£">
        <cfelseif trim(cty) is "IE">
            <cfset pound="€">
        </cfif>

 <cfquery name="totalOnscreen" datasource="#application.dsn#">
        select 
            sum(balance) as totalBalance,
            sum(CREDITOUTSTANDING) as totalClientCredit
        from clientReferences
        where table2.clientId = #client.userclientid#
        and table2.column2=#param#
    </cfquery>
  <cfset TotalOSB = "#totalOnscreen.totalBalance#">

  cfset obj = {
"number" = c,
    "Name" = column1,
    "balance" = TotalOSB 
 } />

<cfset "recordsTotal" = #qCount.total#>
    <cfset  "recordsFiltered" = #gettabledata.RecordCount#>
    <cfset arrayAppend(data, obj) />
    <cfset c=c+1> 

As you can see in the object I have number and balance that are generated in the loop.These are the columns that i need to sort and i could't find any solution yet.

Many thanks!

colin

Hi @vatco ,

Yep, unfortunately you're going to need to do that in your server script, there's no other way I'm afraid.

Cheers,

Colin