Individual column filtering (using select) doesn't work with Ajax source
Individual column filtering (using select) doesn't work with Ajax source
Hi,
I've combined two examples:
http://www.datatables.net/release-datatables/examples/api/multi_filter_select.html
http://www.datatables.net/release-datatables/examples/ajax/ajax.html
together but unfortunately when I use Ajax source all filtering selects are empty. Can I somehow combine those two functionalities ?
Apparently this is not a bug as it has been fixed (http://code.google.com/p/jquery-datatables-column-filter/issues/detail?id=34) but it still doesn't work for me.
My code is exactly the same as one from this example: http://www.datatables.net/release-datatables/examples/api/multi_filter_select.html
The only difference is that I've removed whole tbody content and added Ajax stuff so now initialization looks like that
[code]
$(document).ready(function() {
/* Initialise the DataTable */
var oTable = $('#example').dataTable( {
"bProcessing": true,
"sAjaxSource": "arrays.txt",
"oLanguage": {
"sSearch": "Search all columns:"
}
} );
[/code]
I've combined two examples:
http://www.datatables.net/release-datatables/examples/api/multi_filter_select.html
http://www.datatables.net/release-datatables/examples/ajax/ajax.html
together but unfortunately when I use Ajax source all filtering selects are empty. Can I somehow combine those two functionalities ?
Apparently this is not a bug as it has been fixed (http://code.google.com/p/jquery-datatables-column-filter/issues/detail?id=34) but it still doesn't work for me.
My code is exactly the same as one from this example: http://www.datatables.net/release-datatables/examples/api/multi_filter_select.html
The only difference is that I've removed whole tbody content and added Ajax stuff so now initialization looks like that
[code]
$(document).ready(function() {
/* Initialise the DataTable */
var oTable = $('#example').dataTable( {
"bProcessing": true,
"sAjaxSource": "arrays.txt",
"oLanguage": {
"sSearch": "Search all columns:"
}
} );
[/code]
This discussion has been closed.
Replies
Allan
[code]
"fnInitComplete": function(oSettings, json) {
/* Add a select menu for each TH element in the table footer */
$("tfoot th").each( function ( i ) {
this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
} );
} );
}
[/code]
and now selects are filled. The problem is that if particular value is not listed among {default number of rows listed} then it's not listed in select as well.
[quote]
For example (default: Show 10 entries):
---
1. x
2. x
[...]
10.y
---
11.z
[/quote]
Only x and y will be listed in select but not z as it's not listed on the first page
This works fine:
[code]
$(document).ready(function() {
var oTable = $('#example').dataTable( {
"bProcessing": true,
"sAjaxSource": 'arrays.txt',
"fnInitComplete": function(oSettings, json) { [...]
[/code]
and this gives me incomplete select values
[code]
$(document).ready(function() {
var oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing.php",
"fnInitComplete": function(oSettings, json) { [...]
[/code]
The only difference I can see is different Ajax source and of course "bServerSide": true in second case. Might "bServerSide": true be source of my problem?
Do you need server-side processing? If so, then you'll need to modify the first JSON return to include the data from the server that you want to include in the select filter.
Allan
You're right, if data isn't physically on client side then it cannot be included as option for select.
The only reason I use AJAX is pagination as I have about 600K records so reading all of them at one isn't an option.
Could you give me some tips where in code I should look for place to perform modification you've described? I'm mainly PHP guy so playing with jQuery in general is next to black magic for me ;)
Maybe I could modify jQ somewhere so select options will be filled not with datatables table but rather with MySQL SELECT DISTINCT result?
1. Use an auto-complete like Google's suggest, which makes a call to the server on each key press. There are loads of jQuery plug-ins available for that kind of thing.
2. When sEcho == 1 in your server-side script, return the results from SELECT DISTINCT and add them to the JSON response which your fnInitComplete method would be able to see and add to the select list.
2 is probably easier I guess, while 1 might scale better? (although it does involve lots of Ajax requests...).
Allan
I'm currently on a business trip but as soon I get back I'll try one of those two solutions. Please don't close the topic yet :)
I've analized option nr 1 and I don't like the fact that SELECT DISTINCT would run each time backend php is executed by AJAX.
I think I have (maybe not very nice) solution. What if I would run SELECT DISTINCT from the main file that contains table etc. and then use jQuery or even pure JS to clear and then repopulare select values ?
Do you think it might work?
The only problem that comes to my mind is that AJAX will repopulate selects with incomplete list of values again but that would be the case only if populating selects is not one time process but rather something that AJAX updates on each change like using filtering. Personally I think it's one time and even if not, then I can always cripple code so selects will not be populated at all by AJAX.
I do this because for example in "city" column I hold city's id and in backend php I replace it with real name. As soon as I do this and select city from select menu I see that filtered up list is empty even if such city does exist on the list.
Sounds fair. I don't know why the select distinct would need to run on every call though? Just have it run on the first one (when sEcho == 1).
Allan
[code] "fnInitComplete": function(oSettings, json) {[/code]
that makes SELECT DISTINCT, put values into string (together with option tags) and then populate select with this string by using [code]$("#sel1").html("<?php echo $sel1_string ?>"); [/code]
It does work fine but new problem appeared.
Like I've said database record contains for example cities IDs instead of their names. I have "5" instead of "London". So, I replace those numerical values in backend.php into normal values so JSON will return "London" instead of "5". The problem is that for those columns that have their values replaced like that filtering does not work. For those columns that have their values not replaced at backend (for ex. person name) it DOES work. It doesn't make sense to me as JSON returns strings in the end no matter if it's string taken directly from table in unchanged form or the one that is modified like example with city name. Do you have any clues what might be wrong?
Adrian