Individual column filtering (using select) doesn't work with Ajax source

embrion

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]

allan

Use fnInitComplete to initialise the column filters - you need to wait for the data to come back from the server - which fnInitComplete lets you do.

Allan

embrion

Ok, I've used fnInitComplete like below

[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

embrion

Small update

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?

embrion

Should I report it as a bug ?

allan

I think actually this is working exactly as intended :-). With server-side processing, the full data set is not available on the client-side - that's the whole point of server-side processing! So reading the data front he client-side is always going to result in incomplete data.

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

embrion

Thanks for Answer 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?

allan

You've got two options I can see:

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

embrion

Thank you 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 :)

embrion

Hi Allan,

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.

embrion

Ok, I've used approach where I uniquely identify each dynamically created select and I replace their content by using $("#sel3").html("something"). It works fine as long as I display (in datatable) values taken directly from DB without modifications. If I modify value in backend php script ( if ($aColumns[$i] == 'category') $row[] = "different_value";) ) it stops to filter properly.
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.

allan

> 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 ?

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

embrion

But I don't run it on every call. I've added PHP into
[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

embrion

OK, I think I'm getting closer to nail it. I see that [code]!oSettings.oFeatures.bServerSide[/code] is not met so filtering is taking place on server side. I believe that in that case "Individual column filtering" in backend.php kicks in so I need to somehow reverse WHERE values back to numerical form just like they are held in the database.