I am having an issue sorting a rendered column in DataTables

mtroup

I have a table using server side processing and it displays the data correctly and I have used mRender to add a column to the results that uses data from 2 columns to do an equation. However, when I try to sort the new column results I get the following SQL error as a response

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
 for the right syntax to use near 'asc
        LIMIT 0, 10' at line 5

I realize the error is coming from the php script that is used for server side processing since the data I added to the table is not available in the database itself. So, my question is, is it possible to sort the data on the client side with using the data from the newly generated column.

Here is my function on the page:

$(document).ready(function() {
    $('#gbs').dataTable( {
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "../getData.php",
        "aoColumnDefs": [
            {
                "aTargets": [7],
                mRender: function ( data, type, row ) {
                    return row[6]-row[5]; //function for subtracting col5 from col6 and displaying new results in column 7
                }
            }
        ]
    } );
} );

allan

So the problem here is that you are attempting to sort by data on the server-side that the server-side does not have access too (since the value is calculated on the client-side).

Really you have two options:

1. Perform the calculation in SQL so you can sort on it - which would require a modification to getData.php to allow that to happen. If you are using the demo server-side processing scripts that might be quite involved as they were not designed for such a use.
2. Use client-side processing. If you have < tens of thousands of records, just go client-side processing.

Allan

mtroup

The database will have tens of thousands of records, so the client side processing isn't really an option for me I don't think, but looking at the script, would it be possible to modify it to perform the calculation and output it as an additional column in the JSON array?

I noticed you had included a section for special formatting for the "version" column so I wasn't sure if that's where it would have to be done, if it were possible?

{
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }

allan

The problem with performing the calculation in the output section is that it is already too late. You need the calculation to be performed in SQL so that the SQL can perform the sort. That will require updating the SQL.

My demo scripts weren't really designed for that, so you might want to consider using them as a basic template and then modifying them to suit your needs.

Allan