Server-side filtering on specific columns with input AND select
Server-side filtering on specific columns with input AND select
The sorting for server-side is working: I can see the iSortCol_0 variable in Firebug,
and it's being passed to SQL just fine.
However, I don't know how to get server-side filtering to work.
I'd like to filter with inputs on some columns,
and with select on others.
So if I have 12 columns, on three of them I want to use drop-down fields,
and on a few others I just want to use text fields.
The documentation always seems to show a filtering of all columns
with the same type of form element.
If I used drop-down fields, I'd like to populate their values based on the
call I'm already making to the database.
The documentation for column filtering with drop-down fields does not
use a server-side method for obtaining filter values that span multiple
pages of data. http://www.datatables.net/examples/api/multi_filter_select.html
Since the call to the database and the output of that database call is in a different page (for ajax)
that only allows JSON to feed the table,
I don't know how select fields could be populated with the same database call.
and it's being passed to SQL just fine.
However, I don't know how to get server-side filtering to work.
I'd like to filter with inputs on some columns,
and with select on others.
So if I have 12 columns, on three of them I want to use drop-down fields,
and on a few others I just want to use text fields.
The documentation always seems to show a filtering of all columns
with the same type of form element.
If I used drop-down fields, I'd like to populate their values based on the
call I'm already making to the database.
The documentation for column filtering with drop-down fields does not
use a server-side method for obtaining filter values that span multiple
pages of data. http://www.datatables.net/examples/api/multi_filter_select.html
Since the call to the database and the output of that database call is in a different page (for ajax)
that only allows JSON to feed the table,
I don't know how select fields could be populated with the same database call.
This discussion has been closed.
Replies
server-call (using multiple recordsets) that generates the JSON?
be updated from elements in the JSON response page:
1. add and include ID's for each hidden input
in the first row of json data
2. don't try hidden columns, because elements seem to be inaccessible
when the column is hidden. (if I'm wrong about this, someone please inform...)
3. if the hidden input only needs to be rendered once, then do so
4. access the hidden input via jQuery on the parent page
elements as search filters for a datatable on server-side?
The documentation shows either ALL inputs or ALL selects, and I need to do use
both on server-side. The ALL inputs is working for me server-side...but after that,
I'm not sure how to proceed...
[code]
// initialize dataTable plugin
var oTable = $('.tblErr').dataTable( {
"sDom": '<"dt_top">rt<"dt_bottom"ilp><"clear">',
"bFilter": true,
"bSort": true,
"bSortClasses": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "my_source_page.asp",
"aoColumnDefs": [
{ "bSearchable": false, "aTargets": [ 0 ] }
],
"fnServerData": function (sSource, aoData, fnCallback) {
/* Add some extra data to the sender */
aoData.push({"name": "z_StartDate", "value": $('#z_StartDate').val()});
aoData.push({"name": "z_EndDate", "value": $('#z_EndDate').val()});
$.getJSON( sSource, aoData, function (json) {
/* Do whatever additional processing you want on the callback, then tell DataTables */
fnCallback(json);
});
},
"fnInitComplete": function() {
var oSettings = $('.tblErr').dataTable().fnSettings();
for ( var i=0 ; i0){
$("thead input")[i].value = oSettings.aoPreSearchCols[i].sSearch;
$("thead input")[i].className = "";
}
}
},
"bStateSave": true,
"sPaginationType": "two_button",
"iDisplayLength": 15,
"aLengthMenu": [[15, 20, 25, 50, 100, 500, -1], [15, 20, 25, 50, 100, 500, "All"]],
"oLanguage": {
"sEmptyTable": "ZERO
Non-Fatal Errors",
"sInfo": "_START_ to _END_ from _TOTAL_",
"sLengthMenu": "Show _MENU_",
"sInfoFiltered": " - filtered out of _MAX_",
"sInfoEmpty": "No records to show"
}
});
var search_timeout = undefined;
//col filter
$("thead input").keyup( function () {
// if ( event.keyCode == 13) {
if(search_timeout != undefined) {
clearTimeout(search_timeout);
}
$this = this;
search_timeout = setTimeout(function() {
search_timeout = undefined;
oTable.fnFilter( $this.value, $("thead input").index($this) );
}, 1500);
// }
});
var asInitVals = new Array();
$("thead input").each( function (i) {
asInitVals[i] = this.value;
} );
$("thead input").focus( function () {
if ( this.className == "search_init" )
{
this.className = "";
this.value = "";
}
});
$("thead input").blur( function (i) {
if ( this.value == "" )
{
this.className = "search_init";
this.value = asInitVals[$("thead input").index(this)];
}
});
[/code]
I'm afraid I'm on my phone at the moment - away from my desktop - so I can't answer quiet as in-depth as I would like off the bat, but when I get back later on I'll give a more detailed reply. Basically what you want to do is make use of fnServerData to intercept the JSON sent back from the server (the bit where it says to do whatever additional processing - this is the additional processing :-) ) and populate the select menus from extra data sent back. So on the first call to the server (sEcho==0) you could Pass back a few extra parameters tothe client side which are used to populate the select menus.
One thing I wasn't clear on, have you managed to get individual column filterign working at all with server side processing? The demo server side script includes this ability.
Regards,
Allan
Yes, I was able to get server-side filtering working with the text inputs.
Okay that's good news :-) The following code will hopefully help - basically in the JSON for the first request (sEcho == 1) it assumes that an array called 'select' is passed back as part of the JSON object. The array length is equal to the number of columns, and each array element contains an array of the values you want to search for:
[code]
function fnCreateSelect( aData )
{
var r='', i, iLen=aData.length;
for ( i=0 ; i
and thanks so much for making DataTables.
I've fumbled around a bit, and after some modifications,
it appears to be running as intended. I used select
instead of select[i] for the fnCreateSelect,
as the [i] was only returning one element in the array.
I also hard-coded the column value for the select filters, e.g.:
[code]
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), 8 );
});[/code]
I don't know if the tweaks I made were best practice, but it works.
That's the important bit ;-)
I had assumed a 2D array for select[i], but if you were using just a 1D array, then the modification you've made is the correct thing to do!
Regards,
Allan
I'm fiddling with the $("thead input") functions now,
because they are causing problems as they currently stand...
Dave177's post seems to help, though...
http://datatables.net/forums/comments.php?DiscussionID=943&page=1
var queueTable = $('#queues').dataTable( {
"sPaginationType": "full_numbers",
"bServerSide": true,
"bProcessing": true,
"bJQueryUI": true,
"sAjaxSource": "/jquery/hello",
"fnServerData": function ( sSource, aoData ) {
$('#queues tbody tr').each( function () {
$.getJSON( sSource, aoData, function (json) {
aData = queueTable.fnGetData( json.select[4] );
/* Insert the select menu */
console.log("inside each " + aData);
this.innerHTML = fnCreateSelect(aData);
/* Add the event listener for the newly created element */
$('select', this).change( function () {
queueTable.fnFilter( $(this).val(), i );
} );
} );
i am new to data tables, i am not sure how to get the json string to write into the inner html of each "tr". i read from the 'usage' that fnServerdata must be used to get json string (getjson) and following the above code in the discussion, i am not able see the table itself. it just says 'processing'. if i use the code i pasted here without the fncallback method it doesnt give any output like earlier. how is it that i can get access to the json string returned from server, so that i could take out the select list from it and populate with the fnCreateSelect method? any help would be greatly appreciated. please correct me.