Datatables Nested tables getting rows for child table based on parent table

jelder227

I have set up nested Datatables, with a parent table MAINtable and child table adjlinesTable. Everything almost works, except I am drawing all lines from the adjlinesTable to every row in the MAINtable. For each row in the MAINtable, I only want the adjlinesTable to contain the records where MAINtable(id) = adjlinesTable(adjustmentid). I am very new to javascript, and I have been running loops trying different methods for 3 days, and just can't beat this!

I know I need to define the variable for the MAINtable, and then either send it to my fetch_adjlines.php file, where I can use it to limit the draw, or perhaps I can do something in the createChild section to only generate the lines where the adjustmentid is equal to the MAINrowID?

Here is my script:

$(document).ready(function(){
function createChild ( row ){
var table = $('<table class="display" width="100%"/>');
 
    row.child( table ).show();
 
    var adjlinesTable = table.DataTable( {
    ajax: "php_pages/fetch_adjlines.php",
    pageLength: 5,
    columns: [
      { data: "id" },
      { data: "adjustmentid" },
      { data: "product" },
      { data: "quantity" }
    ],
    order: [[1, 'asc']],
    } );
}
var table = $('#MAINtable').DataTable( {
    ajax: "php_pages/fetch_adjustments.php",
    pageLength: 5,
    columns: [
      {
         className: 'details-control',
         orderable: false,
         data: null,
         defaultContent: ''
      },
      { data: "id" },
      { data: "date" },
      { data: "reason" }
    ],
    order: [[1, 'asc']],
  } );
  $('#MAINtable tbody').on('click', 'td.details-control', function () {
     var tr = $(this).closest('tr');
     var row = table.row( tr );
     var rowData = row.data();
 
     if ( row.child.isShown() ) {

       row.child.hide();
       tr.removeClass('shown');

       $('#' + rowData.id.replace(' ', '-')).DataTable().destroy();
     }
     else {
        createChild(row);
       tr.addClass('shown');
      }
  } );
} );

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

jelder227

Ok - I think I did this as best I can. Here is a test case. Obviously, in this case I am drawing from the same file for both. In reality, I have two different tables and two different php pages per the code above. However, this does replicate the issue in that everyone shows up in all offices.

live.datatables.net/muyuguji/1/edit

kthorngren

I only want the adjlinesTable to contain the records where MAINtable(id) = adjlinesTable(adjustmentid)

Looks like you are doing something similar as this blog but maybe without the Editor. Whether using the Editor with this or not doesn't matter. Take a look at this section of the child Datatable configuration for how to use the ajax.data option to send the id to the server.

Your server script will need to get the id parameter to limit the results to the desired data.

Kevin

jelder227

OK, for the subtable, I need to make sure the only records pulled have the adjustmentid = rowData.id (assuming this is the id from the main row). So I revised my main script to the following:

$(document).ready(function() {
  
function createChild ( row ) {
    // This is the table we'll convert into a DataTable
    var table = $('<table class="display" width="100%"/>');
 
    // Display it the child row
    row.child( table ).show();
 
    // Initialise as a DataTable
    var adjlinesTable = table.DataTable( {
    ajax: {
        url: 'php_pages/fetch_adjlines.php',
        type: 'post',
        data: function ( d ) {
            d.adjustmentid = rowData.id;
        }
    },
    pageLength: 5,
    columns: [
      { data: "id" },
      { data: "adjustmentid" },
      { data: "product" },
      { data: "quantity" }
    ],
    order: [[1, 'asc']],
    
    } );
}
  
  // Main table
  var table = $('#MAINtable').DataTable( {
    ajax: "php_pages/fetch_adjustments.php",
    pageLength: 5,
    columns: [
      {
         className: 'details-control',
         orderable: false,
         data: null,
         defaultContent: ''
      },
      { data: "id" },
      { data: "date" },
      { data: "reason" }
    ],
    order: [[1, 'asc']],
  } );
  
  
  // Add event listener for opening and closing first level childdetails
  $('#MAINtable tbody').on('click', 'td.details-control', function () {
     var tr = $(this).closest('tr');
     var row = table.row( tr );
     var rowData = row.data();
 
     if ( row.child.isShown() ) {
       // This row is already open - close it
       row.child.hide();
       tr.removeClass('shown');
        
       // Destroy the Child Datatable
       $('#' + rowData.id.replace(' ', '-')).DataTable().destroy();
     }
     else {
       // Open this row
        createChild(row);
       tr.addClass('shown');
      }
  } );
             

          
} );

This totally broke it ... presumably I need to do something in fetch_adjustments.php?

This is my code there:

// DB table to use
$table = 'adj_lines';

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier - in this case object
// parameter names
$columns = array(
    array(
        'db' => 'id',
        'dt' => 'DT_RowId',
        'formatter' => function( $d, $row ) {
            // Technically a DOM id cannot start with an integer, so we prefix
            // a string. This can also be useful if you have multiple tables
            // to ensure that the id is unique with a different prefix
            return 'row_'.$d;
        }
    ),
    array( 'db' => 'id', 'dt' => 'id' ),    
    array( 'db' => 'adjustmentid', 'dt' => 'adjustmentid' ),        
    array( 'db' => 'product', 'dt' => 'product' ),  
    array( 'db' => 'quantity',  'dt' => 'quantity' )

);

/*has server connection with value $sql_details */
 include('../include/basic_conn.php');


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( '../include/ssp.class.php' );

echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

kthorngren

This totally broke it ... presumably I need to do something in fetch_adjustments.php?

What happens?

Yes, you will need to do something in your fetch_adjustments.php script to get the parameters sent and use it for the query. I don't use PHP so won't be much help there.

Kevin

kthorngren

Maybe you are getting an error with this d.adjustmentid = rowData.id;. I don't think you have a variable rowData in your createChild function. Yu probably just want to use row for the row parameter in the function.

Kevin

jelder227

Slowly, slowly, step by step... You are correct.

d.adjustmentid = rowData.id I get an error, rowData is not defined.

I replaced it with d.adjustmentid = row.id
What I am getting over to my php file now is row_1 - which is the DT_RowId, not my id from the column. Any clue on how to send the column value?

kthorngren

Use console.log( row ); inside the createChild() function to see the structure of row. The id property should be { data: "id" }, from your main table. Then use the browser's network inspector tool to verify the parameters sent by looking at the bottom of the header tab (at least when using Chrome).

Do both show the correct id?

Then in your fetch_adjlines.php script you will need some code to get the parameters. Have you done that? Sorry I'm not much help with PHP.

Kevin

jelder227

Kevin, you are wonderful! While you didn't answer it, you definitively pointed me in the right direction to make it work. I had to throw out the base php file from Datatables and do a bunch of rewriting to make my code generate the right formats to be imported correctly, but doing this I was able to define the DT_RowId as my table id. The tables still work, as this is a unique field. And this field then gets passed to my child table and draws the correct records.

After over a week beating my head against a wall on this, it works!!! I just need to pretty it up, and then move on to my next set of problems ...