ChaseC ChaseC - 3 months ago 14
Javascript Question

Datatables - api.column().data() call on rendered column

My issue is that I'm trying to get a "total" row at the bottom of the datatable. I get the row, but I get $0 for total. If I change the api.column(4) to api.column(3), it adds the column correctly, so I know it works.

I think my issue is HOW I'm getting the data for column index 4 (item total). I'm setting the data: to null, because I'm rendering the data by multiplying the cost and quantity together. So I'm not sure why the api.column(4).data() isn't getting the rendered information.

$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
processing: true,
seriverSide: true,
ajax: "DataTables-1.10.0/extensions/Editor-1.3.1/examples/php/test.php?PID=<? echo $PID ?>",
table: "#pexpenses",
fields: [ {
label: "Type:",
name: "tbl_pexpenses.type",
type: "select"
}, {
label: "Cost:",
name: "tbl_pexpenses.cost"
}, {
label: "Quantity:",
name: "tbl_pexpenses.quantity"
}, {
label: "Description:",
name: "tbl_pexpenses.description"
}, {

label: "PEID:",
name: "tbl_pexpenses.PEID",
type: "hidden"
}, {
label: "PID:",
name: "tbl_pexpenses.PID",
def: '<? echo $PID; ?>',
type: "hidden"
}
]
} );


$('#pexpenses').DataTable( {
dom: "Tfrtip",
pageLength: -1,
type: 'POST',
paging: false,
info: false,
idSrc: "tbl_pexpenses.PEID",
ajax: "DataTables-1.10.0/extensions/Editor-1.3.1/examples/php/test.php?PID=<? echo $PID ?>",
columns: [
{ data: "tbl_types.type_desc" },
{ data: "tbl_pexpenses.description" },
{ data: "tbl_pexpenses.cost" },
{ data: "tbl_pexpenses.quantity" },
{ data: null,
render: function ( data, type, row ) {
return (data.tbl_pexpenses.cost*data.tbl_pexpenses.quantity);
} }
],
tableTools: {
sRowSelect: "os",
sSwfPath: "../DataTables-1.10.0/extensions/TableTools/swf/copy_csv_xls_pdf.swf",
aButtons: [
{ sExtends: "editor_create", editor: editor },
{ sExtends: "editor_edit", editor: editor },
{ sExtends: "editor_remove", editor: editor },
"print",
{
"sExtends": "collection",
"sButtonText": "Save",
"aButtons": [ "csv", "xls", "pdf" ]}
]
},
"order": [[ 0, 'asc' ]],
"drawCallback": function ( settings ) {
var api = this.api();
var rows = api.rows( {page:'current'} ).nodes();
var last=null;

api.column(0, {page:'current'} ).data().each( function ( group, i ) {
if ( last !== group ) {
$(rows).eq( i ).before(
'<tr class="grouping" ><td colspan="5">'+group+'</td></tr>'
);

last = group;
}
} );
},
initComplete: function ( settings, json ) {
editor.field( 'tbl_pexpenses.type' ).update( json.tbl_types );
},
footerCallback: function ( row, data, start, end, display ) {
var api = this.api(), data;

// Remove the formatting to get integer data for summation
var intVal = function ( i ) {
return typeof i === 'string' ?
i.replace(/[\$,]/g, '')*1 :
typeof i === 'number' ?
i : 0;
};

// Total over all pages
data = api.column( 4 ).data();
total = data.length ?
data.reduce( function (a, b) {
return intVal(a) + intVal(b);
} ) :
0;

// Update footer
$( api.column( 4 ).footer() ).html(
'$'+ total
);
}


} );



// Order by the grouping
$('#example tbody').on( 'click', 'tr.group', function () {
var currentOrder = table.order()[0];
if ( currentOrder[0] === 0 && currentOrder[0] === 'asc' ) {
table.order( [ 0, 'desc' ] ).draw();
}
else {
table.order( [ 0, 'asc' ] ).draw();
}
} );





<table id="pexpenses" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>Type</th>
<th>Description</th>
<th>Cost</th>
<th>Quantity</th>
<th>Item Total</th>
</tr>
</thead>

<tfoot>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<th style="text-align:right">Total:</th>
<th></th>
</tr>
<tr>
<th>Type</th>
<th>Description</th>
<th>Cost</th>
<th>Quantity</th>
<th>Item Total</th>
</tr>
</tfoot>



Answer
// Total over all pages
        data = api.column( 4 ).cache('search');
        total = data.length ?
            data.reduce( function (a, b) {
                    return intVal(a) + intVal(b);
            } ) :
            0;

        // Update footer
        $( api.column( 4 ).footer() ).html(
            '$'+ total
        );
    }


} );

} );

I had to cache the column to get the correct data for it. It now calculates correctly.