ColumnFilterWidgets server-side
ColumnFilterWidgets server-side
Major breakthrough : I've got ColumnFilterWidgets working with server-side processing (sqlsvr in my case).
In order to do this, I've had to run a prefetch of the distinct column data.
First of all, I've made a generic, reusable function to build up a datatable in php.
This is of course unnecessary, but since I'm posting my script, I'm explaining what I've done.
This function is called with the proper parameters
$columns is an array of the column name strings
$indexcolumn is the index column name string
$column headers is an array of header name strings (thead & tfoot)
$cookie is a targetcookie string in 'c[s]' form. Selected rows are saved as a cookie array to use elsewhere (in queries).
$excludeWidgets is an array of column index integers.
$invisible is an array of column index integers.
[code]datatable ($query,$columns,$indexcolumn,$columnheaders,$cookie,$excludeWidgets,$invisible);[/code]
my base table is a query, so to just run it on one table or one view, you'll have to set
[code]$query= " select a,b,c from yourtable ";[/code]
The datatable function is on a separate page, which I php-include.
I'm not going to explain the following functions (which are included elsewhere) unless u want me to:
- (php) QueryDatabase -> fetches a 2d array from the db.
- (js) BoolCookie and the (php) Cookie class -> toggles a value in a cookie array.
the $cookie is of the form 'cookie_arr[cookiesub]' with a '?' delimiter.
In the first part, I build up the prefetch query in the $s variable.
This is the key to get ColumnFilterWidgets working server-side.
It's pretty fast. I believe it should also work with MySQL but I'm not sure.
If needed I'll work that one out.
I'm adding this result as a property of oColumnFilterWidgets
[code]$r.=' ,"oColumnFilterWidgets": { ';
$r.=' "trueData": '.json_encode($cfw).'
}';[/code]
In order to do this, I've had to run a prefetch of the distinct column data.
First of all, I've made a generic, reusable function to build up a datatable in php.
This is of course unnecessary, but since I'm posting my script, I'm explaining what I've done.
This function is called with the proper parameters
$columns is an array of the column name strings
$indexcolumn is the index column name string
$column headers is an array of header name strings (thead & tfoot)
$cookie is a targetcookie string in 'c[s]' form. Selected rows are saved as a cookie array to use elsewhere (in queries).
$excludeWidgets is an array of column index integers.
$invisible is an array of column index integers.
[code]datatable ($query,$columns,$indexcolumn,$columnheaders,$cookie,$excludeWidgets,$invisible);[/code]
my base table is a query, so to just run it on one table or one view, you'll have to set
[code]$query= " select a,b,c from yourtable ";[/code]
The datatable function is on a separate page, which I php-include.
I'm not going to explain the following functions (which are included elsewhere) unless u want me to:
- (php) QueryDatabase -> fetches a 2d array from the db.
- (js) BoolCookie and the (php) Cookie class -> toggles a value in a cookie array.
the $cookie is of the form 'cookie_arr[cookiesub]' with a '?' delimiter.
In the first part, I build up the prefetch query in the $s variable.
This is the key to get ColumnFilterWidgets working server-side.
It's pretty fast. I believe it should also work with MySQL but I'm not sure.
If needed I'll work that one out.
I'm adding this result as a property of oColumnFilterWidgets
[code]$r.=' ,"oColumnFilterWidgets": { ';
$r.=' "trueData": '.json_encode($cfw).'
}';[/code]
This discussion has been closed.
Replies
[code]<?php
// add "LimitRequestLine 65536" to httpd.conf on the apache in order to work with large $_GET variables (via aoData.push in fnServerData)
function datatable ($query,$columns,$indexcolumn,$columnheaders,$cookie,$excludeWidgets="none",$invisible="none") {
$nc=count($columns);
$r='';
// get unique column content in order to set ColumnFilterWidgets
$s = "with s as ( $query ) select * from ( select ";
for ($i=0;$i<$nc;$i++) {
if ($excludeWidgets!="none") {
if (!(in_array($i,$excludeWidgets))) {
$s.= " case when row_number() over (partition by ".$columns[$i]." order by ".$columns[$i].") = 1 then ".$columns[$i]." else null end as ".$columns[$i].",";
}
} else {
$s.= " case when row_number() over (partition by ".$columns[$i]." order by ".$columns[$i].") = 1 then ".$columns[$i]." else null end as ".$columns[$i].",";
}
}
if (substr($s, -1)==',') {
$s=substr($s, 0, -1);
}
$s.= " from s ) t where ";
for ($i=0;$i<$nc;$i++) {
if ($excludeWidgets!="none") {
if (!(in_array($i,$excludeWidgets))) {
$s.= " ".$columns[$i]." is not null or";
}
} else {
$s.= " ".$columns[$i]." is not null or";
}
}
if (substr($s, -2)=='or') {
$s=substr($s, 0, -2);
}
global $qhcon;
$cfw_ray=QueryDatabase($qhcon,$s);
//result to new array omitting nulls
$cfw_rno=count($cfw_ray);
$cfw_cno=count($cfw_ray[0]);
$cfw=array();
for ($i=0;$i<$cfw_rno;$i++) {
for ($j=0;$j<$cfw_cno;$j++) {
if ($cfw_ray[$i][$j]!=null) {
$cfw[$j][]=utf8_encode($cfw_ray[$i][$j]);
}
}
}
//end of ColumnFilterWidgets prefetch
$b=new Cookie;
$c=$b->getCook($cookie);
$d=($c=='unset_cookie')?"":explode("?",$c);
$e=($d=="")?"":"'".implode("','",$d)."'";
$r.=' var oTable;
var selected = new Array();
selected=['.$e.']
var select_all;
$(document).ready(function() {
$(\'#form\').submit( function() {
alert (selected);
return false;
} );
TableTools.DEFAULTS.sSwfPath = "http://datatables.net/release-datatables/extras/TableTools/media/swf/copy_csv_xls_pdf.swf";
TableTools.DEFAULTS.sRowSelect = "multi";
var oTable=$(\'#example\').dataTable( {
"sDom": \'>rtp>\'
,"oSearch": {"sSearch": ""}
,"aoColumnDefs": [ ';
if ($invisible!="none") {
$r.=' { "bVisible": false, "aTargets": [ '.implode(',',$invisible).' ] } ';
}
$r.=' ] ';
$r.=' ,"aoColumns": [ ';
for ($i=0;$i<$nc;$i++) {
$r.=' { "sName": "'.$columns[$i].'", "sTitle": "'.$columnheaders[$i].'" } ';
if (($i+1)<$nc) { $r.=','; }
}
$r.=' ]';
$r.=' ,"oColumnFilterWidgets": { ';
if ($excludeWidgets!="none") {
$r.=' "aiExclude": [ '.implode(",",$excludeWidgets).' ], ';
}
$r.=' "trueData": '.json_encode($cfw).'
}';
$r.=' ,"oTableTools": {
"sSwfPath": "http://datatables.net/release-datatables/extras/TableTools/media/swf/copy_csv_xls_pdf.swf"
,"sRowSelect": "multi"
,"sSelectedClass": "row_selected"
,"aButtons": [
"copy"
,"print"
,{
"sExtends": "collection",
"sButtonText": "Save",
"aButtons": [ "csv", "xls", "pdf" ]
}
,{
"sExtends": "text",
"sButtonText": "Invert Selection of Visible Rows",
"fnClick": function ( nButton, oConfig, nRow ) {
var nNodes = oTable.fnGetNodes( );
$(nNodes).each( function () {
var iPos = oTable.fnGetPosition( this );
var aData = oTable.fnGetData( iPos );
var iId = aData[0];
is_in_array = jQuery.inArray(iId, selected);
if (is_in_array==-1) {
selected[selected.length]=iId;
} else {
selected.splice(jQuery.inArray(iId, selected), 1);
//selected = jQuery.grep(selected, function(value) {
// return value != iId;
//});
}
if ( $(this).hasClass(\'row_selected\') ) {
$(this).removeClass(\'row_selected\');
} else {
$(this).addClass(\'row_selected\');
}
BoolCookie(\''.$cookie.'\',iId);
});
}
}
,{
"sExtends": "text",
"sButtonText": "Clear Selection",
"fnClick": function () {
selected=[];
document.cookie = \''.$cookie.'=; expires=Fri, 29 Apr 1975 02:47:11 UTC; path=/\'
oTable.fnDraw(false);
}
}
]
,"fnRowSelected": function ( node ) {
//alert( "The row with ID " + node.id + " was selected" );
}
}
,"iDisplayLength": 50
,"sPaginationType": "full_numbers"
,"bAutoWidth": false
,"bProcessing": true
,"bServerSide": true
,"sAjaxSource": "http://its-webserver.agfa.be:81/powerhelpsla/includes/js/datatables/scripts/server_processing.php"
,"fnServerData": function ( sSource, aoData, fnCallback ) {
aoData.push( { "name": "trueQuery", "value": "'.$query.'","type": "POST" } );
aoData.push( { "name": "trueColumns", "value": "'.implode(",",$columns).'" } );
aoData.push( { "name": "trueIndex", "value": "'.$indexcolumn.'" } );
$.getJSON( sSource, aoData, function (json) {
fnCallback(json)
} );
$.ajax( {
"dataType": \'json\',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}
,"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
if ( jQuery.inArray(aData[0], selected) != -1 ) {
$(nRow).addClass(\'row_selected\');
}
return nRow;
}
,"fnDrawCallback": function ( oSettings ) {
$(\'#example tbody tr\').each( function () {
var iPos = oTable.fnGetPosition( this );
if (iPos!=null) {
var aData = oTable.fnGetData( iPos );
if (jQuery.inArray(aData[0], selected)!=-1) {
$(this).addClass(\'row_selected\');
} else {
$(this).removeClass(\'row_selected\');
}
}
$(this).click( function () {
var iPos = oTable.fnGetPosition( this );
var aData = oTable.fnGetData( iPos );
var iId = aData[0];
if (jQuery.inArray(iId, selected)==-1) {
selected[selected.length]=iId;
} else {
selected.splice(jQuery.inArray(iId, selected), 1);
}
if ( $(this).hasClass(\'row_selected\') ) {
$(this).removeClass(\'row_selected\');
} else {
$(this).addClass(\'row_selected\');
}
BoolCookie(\''.$cookie.'\',iId);
oTable.fnDraw(false);
});
});
}
} ).columnFilter( {
/*sPlaceHolder: "top", */
aoColumns: [ ';
$no_headers=count($columnheaders);
for ($i=0;$i<$no_headers;$i++) {
$r.=' { type: "text" } ';
if (($i+1)<$no_headers) {
$r.=',';
}
}
$r.=' ]
} );
} );
';
$r.='
';
for ($i=0;$i<$no_headers;$i++) {
$r.=''.$columnheaders[$i].'';
}
$r.='Loading Table
';
for ($i=0;$i<$no_headers;$i++) {
$r.=''.$columnheaders[$i].'';
}
$r.='
';
return $r;
}
?>[/code]
The filtering section is adapted to split the searches on pipe symbols (with ORs)
the php command 'utf8_decode' is key to work with special chars like ñ, ö, ü ...
[code]<?php
header('Content-type: text/html; charset=UTF-8');
mb_internal_encoding('UTF-8');
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn=(isset($_GET['trueIndex']))?$_GET['trueIndex']:'call_id';
/* DB table to use */
$table=(isset($_GET['trueQuery']))?$_GET['trueQuery']:"this is a bad query";
$sTable = " ( $table ) as tab ";
/* Columns */
$aColumns=(isset($_GET['trueColumns']))?explode(",",$_GET['trueColumns']):array( 'call_id' );
/* Database connection information */
$gaSql['user'] = "user";
$gaSql['password'] = "pass";
$gaSql['db'] = "dbname";
$gaSql['server'] = "servername";
$connectionInfo = array("UID" => $gaSql['user'], "PWD" => $gaSql['password'], "Database"=>$gaSql['db'],"ReturnDatesAsStrings"=>true);
$gaSql['link'] = sqlsrv_connect( $gaSql['server'], $connectionInfo);
$params = array();
$options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
/* Ordering */
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) ) {
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
$row = array();
for ( $i=0 ; $i[/code]
passing everything in 'trueData'
[code](function($) {
var fnRegExpEscape = function( sText ) {
return sText.replace(/[-[\]{}()*+?.,\\^$|#\s]/g, "\\$&");
};
var ColumnFilterWidgets = function( oDataTableSettings ) {
var me = this;
var sExcludeList = '';
trueData = [];
me.$WidgetContainer = $( '' );
me.$MenuContainer = me.$WidgetContainer;
me.$TermContainer = null;
me.aoWidgets = [];
me.sSeparator = '';
if ( 'oColumnFilterWidgets' in oDataTableSettings.oInit ) {
if ( 'aiExclude' in oDataTableSettings.oInit.oColumnFilterWidgets ) {
sExcludeList = '|' + oDataTableSettings.oInit.oColumnFilterWidgets.aiExclude.join( '|' ) + '|';
}
if ( 'bGroupTerms' in oDataTableSettings.oInit.oColumnFilterWidgets && oDataTableSettings.oInit.oColumnFilterWidgets.bGroupTerms ) {
me.$MenuContainer = $( '' );
me.$TermContainer = $( '' ).hide();
}
if ( 'trueData' in oDataTableSettings.oInit.oColumnFilterWidgets ) {
trueData = oDataTableSettings.oInit.oColumnFilterWidgets.trueData;
}
}
// Add a widget for each visible and filtered column
var counttrueData=0;// truedata added
$.each( oDataTableSettings.aoColumns, function ( i, oColumn ) {
var $columnTh = $( oColumn.nTh );
var $WidgetElem = $( '' );
if ( sExcludeList.indexOf( '|' + i + '|' ) < 0 ) {
me.aoWidgets.push( new ColumnFilterWidget( $WidgetElem, oDataTableSettings, i, me, trueData[counttrueData] ) ); // truedata added
counttrueData++;// truedata added
}
me.$MenuContainer.append( $WidgetElem );
} );
if ( me.$TermContainer ) {
me.$WidgetContainer.append( me.$MenuContainer );
me.$WidgetContainer.append( me.$TermContainer );
}
oDataTableSettings.aoDrawCallback.push( {
name: 'ColumnFilterWidgets',
fn: function() {
$.each( me.aoWidgets, function( i, oWidget ) {
oWidget.fnDraw();
} );
}
} );
return me;
};
ColumnFilterWidgets.prototype.getContainer = function() {
return this.$WidgetContainer.get( 0 );
}
var ColumnFilterWidget = function( $Container, oDataTableSettings, i, widgets, trueData ) { // truedata added
var widget = this, sTargetList;
widget.trueData = trueData; // truedata added
widget.iColumn = i;
widget.oColumn = oDataTableSettings.aoColumns[i];
widget.$Container = $Container;
widget.oDataTable = oDataTableSettings.oInstance;
widget.asFilters = [];
widget.sSeparator = '';
widget.bSort = true;
widget.iMaxSelections = -1;
if ( 'oColumnFilterWidgets' in oDataTableSettings.oInit ) {
if ( 'sSeparator' in oDataTableSettings.oInit.oColumnFilterWidgets ) {
widget.sSeparator = oDataTableSettings.oInit.oColumnFilterWidgets.sSeparator;
}
if ( 'iMaxSelections' in oDataTableSettings.oInit.oColumnFilterWidgets ) {
widget.iMaxSelections = oDataTableSettings.oInit.oColumnFilterWidgets.iMaxSelections;
}
if ( 'aoColumnDefs' in oDataTableSettings.oInit.oColumnFilterWidgets ) {
$.each( oDataTableSettings.oInit.oColumnFilterWidgets.aoColumnDefs, function( iIndex, oColumnDef ) {
var sTargetList = '|' + oColumnDef.aiTargets.join( '|' ) + '|';
if ( sTargetList.indexOf( '|' + i + '|' ) >= 0 ) {
$.each( oColumnDef, function( sDef, oDef ) {
widget[sDef] = oDef;
} );
}
} );
}
}
widget.$Select = $( '' ).change( function() {
var sSelected = widget.$Select.val(), sText, $TermLink, $SelectedOption;
if ( '' === sSelected ) {
// The blank option is a default, not a filter, and is re-selected after filtering
return;
}
sText = $( '' + sSelected + '' ).text();
$TermLink = $( '' )
.addClass( 'filter-term-' + sText.toLowerCase().replace( /\W/g, '' ) )
.text( sText )
.click( function() {
// Remove from current filters array
widget.asFilters = $.grep( widget.asFilters, function( sFilter ) {
return sFilter != sSelected;
} );
$TermLink.remove();
if ( widgets.$TermContainer && 0 === widgets.$TermContainer.find( '.filter-term' ).length ) {
widgets.$TermContainer.hide();
}
// Add it back to the select
widget.$Select.append( $( '' ).attr( 'value', sSelected ).text( sText ) );
if ( widget.iMaxSelections > 0 && widget.iMaxSelections > widget.asFilters.length ) {
widget.$Select.attr( 'disabled', false );
}
widget.fnFilter();
return false;
} );
widget.asFilters.push( sSelected );
if ( widgets.$TermContainer ) {
widgets.$TermContainer.show();
widgets.$TermContainer.prepend( $TermLink );
} else {
widget.$Select.after( $TermLink );
}
$SelectedOption = widget.$Select.children( 'option:selected' );
widget.$Select.val( '' );
$SelectedOption.remove();
if ( widget.iMaxSelections > 0 && widget.iMaxSelections <= widget.asFilters.length ) {
widget.$Select.attr( 'disabled', true );
}
widget.fnFilter();
} );
widget.$Container.append( widget.$Select );
widget.fnDraw();
};
ColumnFilterWidget.prototype.fnFilter = function() {
var widget = this;
var asEscapedFilters = [];
var sFilterStart, sFilterEnd;
if ( widget.asFilters.length > 0 ) {
// Filters must have RegExp symbols escaped
$.each( widget.asFilters, function( i, sFilter ) {
asEscapedFilters.push( sFilter );
} );
// This regular expression filters by either whole column values or an item in a comma list
sFilterStart = widget.sSeparator ? '(^|' + widget.sSeparator + ')(' : '^(';
sFilterEnd = widget.sSeparator ? ')(' + widget.sSeparator + '|$)' : ')$';
widget.oDataTable.fnFilter( asEscapedFilters.join('|'), widget.iColumn, true, false );
} else {
// Clear any filters for this column
widget.oDataTable.fnFilter( '', widget.iColumn );
}
};
[/code]
var widget = this;
var oDistinctOptions = {};
var aDistinctOptions = [];
var aData;
if ( widget.asFilters.length === 0 ) {
// Find distinct column values
//aData = widget.oDataTable.fnGetColumnData( widget.iColumn );
aData = widget.trueData; // truedata added
$.each( aData, function( i, sValue ) {
var asValues = widget.sSeparator ? sValue.split( new RegExp( widget.sSeparator ) ) : [ sValue ];
$.each( asValues, function( j, sOption ) {
if ( !oDistinctOptions.hasOwnProperty( sOption ) ) {
oDistinctOptions[sOption] = true;
aDistinctOptions.push( sOption );
}
} );
} );
// Build the menu
//
// ### changed the sTitle filled in the dropdown base to the sName
widget.$Select.empty().append( $( '' ).attr( 'value', '' ).text( widget.oColumn.sTitle ) );
//
//widget.$Select.empty().append( $( '' ).attr( 'value', '' ).text( widget.oColumn.sName ) );
if ( widget.bSort ) {
if ( widget.hasOwnProperty( 'fnSort' ) ) {
aDistinctOptions.sort( widget.fnSort );
} else {
aDistinctOptions.sort();
}
}
$.each( aDistinctOptions, function( i, sOption ) {
var sText;
sText = $( '' + sOption + '' ).text();
widget.$Select.append( $( '' ).attr( 'value', sOption ).text( sText ) );
} );
if ( aDistinctOptions.length > 1 ) {
// Enable the menu
widget.$Select.attr( 'disabled', false );
} else {
// One option is not a useful menu, disable it
widget.$Select.attr( 'disabled', true );
}
}
};
/*
* Register a new feature with DataTables
*/
if ( typeof $.fn.dataTable === 'function' && typeof $.fn.dataTableExt.fnVersionCheck === 'function' && $.fn.dataTableExt.fnVersionCheck('1.7.0') ) {
$.fn.dataTableExt.aoFeatures.push( {
'fnInit': function( oDTSettings ) {
var oWidgets = new ColumnFilterWidgets( oDTSettings );
return oWidgets.getContainer();
},
'cFeature': 'W',
'sFeature': 'ColumnFilterWidgets'
} );
} else {
throw 'Warning: ColumnFilterWidgets requires DataTables 1.7 or greater - www.datatables.net/download';
}
}(jQuery));[/code]
http://www.chaubet.be/share/cfw-serverside.rar