Server side, column sorting with render function

wkeull

I suspect that the answer is right in front of me but I am having trouble trying to get my data tables to sort properly when rendering data through a function such as:
` columns: [
{data: 'work.id'},
{data: null,
render: function (data, type, row) {

                if (data.customer.cname) {
                    return data.customer.cname;
                } else {
                    return data.customer.fname + ' ' + data.customer.lname;
                }
            }`

I imagine that I should be able to append a custom sort delimiter within this function but I have not been able to succeed as of yet.
Thanks in advance.

kthorngren

Maybe I'm missing the point of the question but if you are using serverSide your server script is responsible for the sorting. The server script isn't going to know about the results of the render function. You would need to replicate the render function in your server side code and have it sort the data appropriately.

Kevin

wkeull

Kevin,
Thanks for the quick reply. I do understand where your coming from and I should have been more clear and included the below in the original post.

{data: 'null,'
                render: function (data, type, row) {

                    if (data.customer.cname) {
                        return data.customer.cname;
                    } else {
                        return data.customer.fname + ' ' + data.customer.lname;
                    }

                }
            },

The issue that I am not understanding is when using the above function in the manner that I am, with the data:'null' the server is of course not getting anything to sort with. In short I guess a better way to phrase the question is: how can I achieve the rendering functionality alongside with a "data: value" to facilitate the server side sorting functionality?

kthorngren

how can I achieve the rendering functionality alongside with a "data: value" to facilitate the server side sorting functionality?

I may still be missing something in your question. Two of the column parameters sent in the Server Side request come from the columns.data option and the columns.name option. You can either set the columns.data option to one of your returned fields or you can set the columns.name or both. This example shows both:
http://live.datatables.net/loqevoji/1/edit

The first column is set to data: first_name but it renders data from different objects in the row: return row.position + ' ' + row.last_name;

Still not sure I'm answering your question. If not what exactly are you wanting to send to the server to sort with?

Kevin

wkeull

Kevin,
I am sorry to take so long in getting back to this, but taking another look at this issue, I am thinking the issue I am running into is related to the array formats as all of the columns that I am having issues with are formed from nested arrays. An example from the server data:
{"data":[{"DT_RowId":"row_1965","work":{"id":"1965","name":"284","date_ord":"2018-08-30 00:00:00","date_start":"2018-09-12 00:00:00","date_due":"2018-09-14 00:00:00","mu":"0.00","note":"<p>text<\/p><p>123456<\/p><p>&nbsp;<\/p>","company":"1"},"customer":{"cname":"","fname":"Dennis","lname":"Dennis"},"work_status_options":[{"id":"0","desc":"Not-Complete"}],"roles":[{"role_id":"2","role_name":"Admin"}]},.....

The columns in this case that are an issue are pulled from "customer", "work_status_options" and "roles".

This is used with the following data table construct:

     table = $('#editor_table').DataTable({
    
            lengthChange: false,
            ajax: {
                url: "work_editor",
                type: "POST"
            },
            processing: true,
            serverSide: true,
            order: [[0, "desc"]],
            columnDefs: [{ "orderable": false, "searchable": false, "targets": 1 }],
            columns: [
                {data: 'work.id'},
                {data: null,
                    render: function (data, type, row) {
    
                        if (data.customer.cname) {
                            return data.customer.cname;
                        } else {
                            return data.customer.fname + ' ' + data.customer.lname;
                        }
    
                    }
                },
                {
                    data: 'work.date_start',
                    render: function (d) {
                        return moment(d).format("MM/DD/YYYY");
                    }
                },
                {
                    data: 'work.date_due',
                    render: function (d) {
                        return moment(d).format("MM/DD/YYYY");
                    }
                },
         
                
                { data: "roles[].role_name"
                },
                
                
                {data: 'work_status_options[].desc'},
                {data: 'DT_RowId',
                    render: function (data, type, row, meta) {
                        return  ' <div class="dropdown"><button class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">+<span class="caret"></span></button><ul class="dropdown-menu"><li><a href="/index.php/Millwork/profiles/' + data.substr(4) + '">Update Profiles</a></li><li><a href="/index.php/Millwork/status/' + data.substr(4) + '">Update Status</a></li><li><a href="/index.php/work_order_pdf/index/' + data.substr(4) + '">Work Order</a></li><li><a href="/index.php/work_conf_quote_pdf/index/' + data.substr(4) + '">Quote</a></li><li><a href="/index.php/work_conf_pdf/index/' + data.substr(4) + '">Order Confirmation</a></li></ul></div>';
    
                    }}
    
    
            ],
            select: true
        });

It appears as though the issue in all of these instances is more related to the naming/interpreting of each columns name as the error that is received is along these lines when dealing with the roles column:

"DataTables warning: table id=editor_table - Unknown field: roles[].role_name (index 4)"

allan

As Kevin says, if you are using server-side processing, then its up to the server-side script to do any sorting and filtering. Since the renderer is on the client-side, the server-side processing script can't take into account what you have on the client-side.

So the first key question is: do you need server-side processing? Have you got tens of thousands or more records? If not, disable server-side processing.

If it is needed, that you'd most likely need to create a VIEW in your database that the server-side processing script can query. That VIEW would do the conditional formatting needed, and that will allow correct sorting and filtering.

Allan

wkeull

Allen and Kevin,
Thanks for all of the information you did get me not he right track. I imagine that there might be a better way to achieve this but the following did work well and scales for other similar instances. I am using codeigniter as a framework so there is a little bit of handing things around but the concept was to use the rest interface and use a direct create/edit of the table and a mysql_view for the datatable. this was achieved by simply renaming the json data before(server side) and after the create/edit model (client side via editor.on )so as to keep all validation and error catching.

Editor:
`})(jQuery, jQuery.fn.dataTable);

editor = new $.fn.dataTable.Editor({
    ajax: {
        create: {
            type: 'POST',
            url:  'work_editor_create'
        },
        edit: {
            type: 'PUT',
            url:  'work_editor_edit?id=_id_'
        }
    },

    serverSide: true,
    table: "#editor_table",
    template: '#customForm',
    fields: [{

......

editor.on('postSubmit', function (e, json, data, action) {
    if (typeof json.fieldErrors !== "undefined") {
        for (var i = 0; i < json.fieldErrors.length; i++) {
            var obj = json.fieldErrors[i];
            var name = obj.name;
            var nameold = obj.name.substring(0, 4);


            if (nameold === "work") {

                name = name.replace("work", "work_view");
                json.fieldErrors[i].name = name;
            }
        }
    }
});

`

Table:
` table = $('#editor_table').DataTable({

    lengthChange: false,
    ajax: {
        url: "work_editor_get",
        type: "POST"
    },

    processing: true,
    serverSide: true,`

Model:
` case 'work_editor_create':

            if ($post <> NULL) {
                $post['data'][0]['work']['name'] = $post['data'][0]['work_view']['name'];
                $post['data'][0]['work']['date_ord'] = $post['data'][0]['work_view']['date_ord'];
                $post['data'][0]['work']['date_start'] = $post['data'][0]['work_view']['date_start'];
                $post['data'][0]['work']['date_due'] = $post['data'][0]['work_view']['date_due'];
                $post['data'][0]['work']['date_del'] = $post['data'][0]['work_view']['date_del'];
                $post['data'][0]['work']['approved'] = $post['data'][0]['work_view']['approved'];
                $post['data'][0]['work']['note'] = $post['data'][0]['work_view']['note'];
                $post['data'][0]['work']['company'] = $post['data'][0]['work_view']['company'];
            }

        case 'work_editor_edit':
            if ($post <> NULL) {
                $key = array_keys($post['data'])[0];
                $post['data'][$key]['work']['name'] = $post['data'][$key]['work_view']['name'];
                $post['data'][$key]['work']['date_ord'] = $post['data'][$key]['work_view']['date_ord'];
                $post['data'][$key]['work']['date_start'] = $post['data'][$key]['work_view']['date_start'];
                $post['data'][$key]['work']['date_due'] = $post['data'][$key]['work_view']['date_due'];
                $post['data'][$key]['work']['date_del'] = $post['data'][$key]['work_view']['date_del'];
                $post['data'][$key]['work']['approved'] = $post['data'][$key]['work_view']['approved'];
                $post['data'][$key]['work']['note'] = $post['data'][$key]['work_view']['note'];
                $post['data'][$key]['work']['company'] = $post['data'][$key]['work_view']['company'];
                unset($post['data'][$key]['work_view']);
            }

            Editor::inst($this->editorDb, 'work')
                    ->fields(

        case 'work_editor_get':

            Editor::inst($this->editorDb, 'work_view')
                    ->fields(

'