Passing parent table RowID to nested table

nigel pasco

Hi all,
I have been trying to figure getData to pass the rowID of a parent table to filter the results of that rows nested table. I have set up the nested table but am not sure how to define the rowID to a variable that can be used in the ajax call of the nested table.

I did note a couple previous forum solutions, however the links to these are no longer active. Also, it seems that Allan's blog on drill down rows (https://datatables.net/blog/2011-06-19) no longer works. The getData reference stuff doesn't really show how I can use the data in another table.

I have set up a live demo at: http://nigelpasco.com/pascon-ajax/#ajax/timesheet.php
(user: newuser, pass: newuser).

Any assistance would be most appreciated.

The full code is too long to put in this post, but I have included the event listener code below:

$('#table_timesheet').DataTable( opts );

        // Add event listener for opening and closing details
        $('.container').on('click', 'td.details-control', function () {
            var tr = $(this).closest('tr');
            var table = $(this).closest('table').DataTable();
            var row = table.row( tr );

NB:  USE getData TO GET THE ROW ID FROM TABLE opts AND PASS TO timesheetdetail.php
            // var stimesheet = opts.fnGetData( row, 2 );

            if ( row.child.isShown() ) {
                // This row is already open - close it
                row.child.hide();
                tr.removeClass('shown');
            }
            else {

// NB: Have used a constant for variable 'timesheet' - this needs to be replaced by the rowID of the parent table.
                editor3 = new $.fn.dataTable.Editor( {
                    ajax: "mymedia/php/table.timesheetdetail.php?timesheet=5&user=<?php echo$user_id;?>",
                    table: "#table_timesheetdetail",
                    fields: [
                        {
                            "name": "timesheet_code.timesheet_id",
                            "type": "hidden"
                        },
                        {
                            "label": "Cost Code:",
                            "name": "timesheet_code.cc_id",
                            "type": "select"
                        },
                        {
                            "label": "Quantity:",
                            "name": "timesheet_code.qty",
                            "type": "text"
                        },
                        {
                            "name": "timesheet_code.added",
                            "type": "hidden"
                        },
                        {
                            "name": "timesheet_code.addedby_id",
                            "type": "hidden"
                        },
                        {
                            "label": "Notes:",
                            "name": "timesheet_code.notes",
                            "type": "text"
                        },
                        {
                            "name": "timesheet_code.statis",
                            "type": "hidden"
                        }
                    ]
                } );

                var nestedopts = {
                    //"bFilter": false,
                    //"bInfo": false,
                    //"bLengthChange": false
                    //"bPaginate": false,
                    "bStateSave": true, // saves sort state using localStorage
                    "sDom": "<'dt-toolbar'<'col-xs-12 col-sm-6'f><'col-sm-6 col-xs-12 hidden-xs'T>r>"+
                        "t",
                    "autoWidth": true,
                    "preDrawCallback" : function() {
                        // Initialize the responsive datatables helper once.
                        if (!responsiveHelper_table_timesheetdetail) {
                            responsiveHelper_table_timesheetdetail = new ResponsiveDatatablesHelper($('#table_timesheetdetail'), breakpointDefinition);
                        }
                    },
                    "rowCallback" : function(nRow) {
                        responsiveHelper_table_timesheetdetail.createExpandIcon(nRow);
                    },
                    "drawCallback" : function(oSettings) {
                        responsiveHelper_table_timesheetdetail.respond();
                    },
                    ajax: {
                        url: "mymedia/php/table.timesheetdetail.php?timesheet=5&user=<?php echo$user_id;?>",
                        type: 'POST'
                    },
                    "columns": [
                        { data: null, render: function ( data, type, row ) {
                            // Combine the first and last names into a single table field
                            return data.cc_pascon.code+''+data.cc_pascon.code;
                        }, "title":"Cost code" },
                        { "data": "cc_global.gdescript", "title":"Description" },
                        { "data": "timesheet_code.qty", "title":"Qty" }
                    ],

                    "order": [[1, 'asc']],
                    tableTools: {
                        sRowSelect: "os",
                        sSwfPath: "datatables/extensions/TableTools/swf/copy_csv_xls_pdf.swf",
                        aButtons: [
                            { sExtends: "editor_create", editor: editor3 },
                            { sExtends: "editor_edit",   editor: editor3 },
                            { sExtends: "editor_remove", editor: editor3 },
                            {
                                sExtends: "collection",
                                sButtonText: "Save",
                                sButtonClass: "save-collection",
                                aButtons: [ 'copy', 'csv', 'xls', {
                                    "sExtends": "pdf",
                                    "sTitle": "SmartAdmin_PDF",
                                    "sPdfMessage": "SmartAdmin PDF Export",
                                    "sPdfSize": "letter"
                                }]
                            },
                            {
                                "sExtends": "print",
                                "sMessage": "Generated by PASCON CMS <i>(press Esc to close)</i>" // ADD PRINT HEADER HERE?!?
                            }
                        ]
                    },
                    initComplete: function ( settings, json ) {
                        // Populate the site select list with the data available in the
                        // database on load
                        editor3.field( 'timesheet_code.cc_id' ).update( json.cc_global );
                    }
                };

                // Open this row
                row.child( $('<table/>') ).show();
                tr.addClass('shown');
                row.child().find('table').DataTable(nestedopts);


            }
        } );

allan

// var stimesheet = opts.fnGetData( row, 2 );

This is the bit that is causing problems is it? What is opts? It looks like a configuration object rather than an instance? The other thing is that fnGetData is the old style API - if you are using 1.10, use the new one :-)

You already had a row reference in the event handler code above:

var row = table.row( tr );

so you could simply use the row().data() method to get the data:

var data = row.data();

Then data.timesheet_code.timesheet_id or whatever to access the data and pass it on through to the Ajax.

Allan

nigel pasco

Thank you for the response Allan.

I am a bit confused with exactly how that works, but I will study up some on the row().data() method and go from there.

Have a good day. Sleep time on this side of the globe :)

nigel pasco

Ok. I think I got that, but how would I pass the parent table ID (data.timesheet.timesheet_id) into the ajax address?

I tried to use 'render' to define the ajax address with the variable as below:. However, I get the json error "DataTables warning: table id=DataTables_Table_0 - Invalid JSON response..." - a strange return for table id?!?

Note that if the ajax call is replaced with a simple address with a constant for the timesheet_id the child rows work fine. The issue seems to be in how I am trying to pass the timesheet_id variable.

$('#table_timesheet').DataTable( primarytable );

        // Add event listener for opening and closing details
        $('.container').on('click', 'td.details-control', function () {
            var tr = $(this).closest('tr');
            var table = $(this).closest('table').DataTable();
            var row = table.row( tr );
            var data = row.data();

            if ( row.child.isShown() ) {
                // This row is already open - close it
                row.child.hide();
                tr.removeClass('shown');
            }
            else {

var nestedtable = {
                    "ajax": {
                        "url": null, render: function ( data, type, row ) {
                            return '"mymedia/php/table.timesheetdetail.php?timesheet='+data.timesheet_timesheet_id+'&user=<?php echo$user_id;?>"'
                        }, "type": 'POST'
                    },
"columns":

/* rest of nestedtable goes here... */

};

                // Open this row
                row.child( $('<table/>') ).show();
                tr.addClass('shown');
                row.child().find('table').DataTable(nestedtable);
}
        } );

allan

'"mymedia

That looks a bit odd. A url that starts with a double quote. Is that intentional?

What is the server returning if it isn't valid JSON (see tech note 1)? My guess, based on the above, is a 404 error page.

Allan

nigel pasco

SOLVED! somehow?!?
The '" was intentional although unnecessary, however it did not seem to affect the result. The debugger showed status 200, but the requested filename was not going through at all well. Still not sure why the render function was not working.
In the end I just experimented with different syntaxs and eventually a very simple solution worked. Go figure.

"ajax": {
"url":'mymedia/php/table.timesheetdetail.php?timesheet='+data.timesheet.timesheet_id+'&user=<?php echo$user_id;?>',
"type":'POST'
},

Thank you again for your support Allan. Have a great day.
nige

nigel pasco

...incidentally, I seem to have somehow lost the Tabletools in the nested table in the process of experimenting... ooops!

allan

Heh - good to hear you got it working!

...incidentally, I seem to have somehow lost the Tabletools in the nested table in the process of experimenting... ooops!

Looks like you need either the dom property with T (for TableTools) in it, or you use the new $.fn.dataTable.TableTools( ... ) constructor. Neither are shown above.

Allan

nigel pasco

Thanks Allan, they were, but I just managed to delete them in the process of mucking around with the code. Happy Days :P