Issues with using drop down (select) to filter datatable that uses a join

gehorne

First let me apologize for not providing a link to the site I'm having an issue with. Its behind a firewall. If needed I can create a static mapping and open it to the public, but maybe someone has some wisdom for me.

Using datatables editor as a starting point and have added a lot of functionality. Issue now is I want to filter/search the datatable based on a drop down/select. I have this positioned this element in a table above the main table and it populates correctly with options from the table. Issue is if I select anything from the drop down using joined data, I get no matching records. This is ONLY the case, if I choose a column that is the result of a join. Use column 1 or 2 (in my example) and life is good.

Here is a short cut and paste of a section of the code:

var table = $('#chemical_inv').DataTable( {
    dom: "RlC<'clear'>Tfrtip",
    ajax: "php/table.chemical_inv.php",
    initComplete:function () {
                this.api().columns(0).every( function () {
                    var column = this;
                    var select = $('<select><option value=""></option></select>')
                        .appendTo( '#top' )
                        .on( 'change', function () {
                            var val = $.fn.dataTable.util.escapeRegex(
                                $(this).val()
                            );

                            column
                                .search( val ? '^'+val+'$' : '', true, false )
                                .visible( false )
                                .draw();
                        } );

                    column.data().unique().sort().each( function ( d, j ) {
                        select.append( '<option value="'+d+'">'+d+'</option>' )
                    } );
                } );
            },
    columns: [
        {
          data: "sites.name"   // the result of a join
        },
        {
          data: "chemical_inv.pname"
        },
        {
          data: "chemical_inv.cname"

If I use column 1 or 2 it works without issue, if I choose 0 I get nothing. I'm not sure of the next step and hope someone can help me to the next step.

As a added issue I am also using colVis so this may become more of an issue if a user moves a column around.

One step at a time I guess....

Thanks

GEH

gehorne

clarification:

When I said: "If I use column 1 or 2 it works without issue, if I choose 0 I get nothing" I should have said, when I choose a filter option from the drop down when I am using col 0, I do not match any records, when records would normally match.

I am additionally using column filters and I can successfully filter on any column.

allan

Hi,

Is the select list in the column 0 footer populated with the sites.name variables as you would expect?

I am additionally using column filters and I can successfully filter on any column.

I'm a little confused by this statement. My understanding of your previous discussion was that the column 0 filter would not work. Have I misunderstood?

Could you run the DataTables debugger on your table so I can take a look at how it is setup?

Thanks,
Allan

gehorne

Allan,

Yes the select list is populated with the correct names of the sites and displays correctly, but if you selected anything other than the blank, no matching records is the result.

Sorry I may have confused the issue with the "additional info" about the column filtering. I have been adding functionality one step at a time and wanted to say that I think I had the data correct because I can filter that same column using the text that is displayed in select list and I get the expected results.

On the running the DataTables debugger (another great tool by the way) on this table, it hangs at uploading data. I see it uploading data in the Chrome Browser footer area, standard percentage uploaded stuff is displayed it goes to 100% and the Chrome info goes away as expected, but the "DataTables debug bookmarklet Uploading data to the server..." remains on the screen. I have let it remain here for for over twenty mins. Have tried this from another computer with same results. Can run the debugger from other tables without issue.

Tested running the debugger on my simple test case for this feature (no database, just simple table data) and it works fine returning the URL http://debug.datatables.net/okidev

gehorne

Also I should say, my simple test case table works as expected, returning the results one would expect. Its only on the more complex tablet with the joined data that I am having the issues.

gehorne

Looking at the select statement in chromes debugger I see:

outerHTML: "<select><option value=""></option><option value="Alabama SuperTree Nursery & Orchard↵">Alabama SuperTree Nursery & Orchard↵</option><option value="Bellamy Seed Orchard↵">Bellamy Seed Orchard↵</option><option value="Bellville Product Development↵">Bellville Product Development↵</option><option value="Bellville SuperTree Nursery↵">Bellville SuperTree Nursery↵</option><option value="Forest Seed Center↵">Forest Seed Center↵</option><option value="Fred C. Gragg SuperTree Nursery↵">Fred C. Gragg SuperTree Nursery↵</option><option value="Livingston↵">Livingston↵</option><option value="Ravenel Seed Orchard↵">Ravenel Seed Orchard↵</option><option value="Richard O. Barham SuperTree Nursery↵">Richard O. Barham SuperTree Nursery↵</option><option value="Ridgeville↵">Ridgeville↵</option><option value="South Carolina SuperTree Nursery↵">South Carolina SuperTree Nursery↵</option></select>"

So my guess at this point is maybe a carriage return needs to be removed off of the values. Will look into that.

GEH

gehorne

Removing the CRLF from the data being returned from the DB resolved the issue. DataTables working as expected now!

Thanks

GEH