[SOLVED] Searchable columns with hidden columns index confusion
[SOLVED] Searchable columns with hidden columns index confusion
I was struggling with this for a couple of hours yesterday and I figured out a solution to it this morning that I thought might be helpful if anyone runs into something similar.
The problem - I have a table with 17 columns. When the table is first rendered, only six of them are visible. I wanted to be able to filter on any of those six columns individually. I followed the example at http://datatables.net/release-datatables/examples/api/multi_filter.html, but I kept getting the wrong index for the columns, since that uses the index of the inputs to determine which column to search under, and my first column was hidden, throwing off the indexes. In addition, I'm using a heavily modified version of http://datatables.net/development/server-side/perl_mvc for the server side processing.
To make this work, I switched the javascript that calls fnFilter to use the column's tabindex, instead of its index. Then, I modified the inputs in the footer to have tabindex set to the column index of the column that each goes under. This has the added benefit of making sure that tabbing through the fields goes in the order that I display them in.
The mods - Here are the sections that I modified to make this work:
[code]
-
...
[/code]
The "=" is there just so a '-' shows up in the footer for the hidden field if it gets displayed. The class dtFullFoot is there because I have multiple datatables on the page and I wanted a way to make the sample code work specifically for a single datatable. The table itself is named "dtFull" so that's where that part of the name comes from.
[code]
$("input.dtFoot").keyup( function () {
/* Filter on the column (the tabindex) of this element */
oTable2.fnFilter( this.value, $(this).attr("tabindex"));
});
[/code]
This is the modified keyup function which filters based on the tabindex, rather than the input field's index.
[code]
sub _generate_where_clause {
my $self = shift;
my %where = ();
if ( defined ($self->param('sSearch') ) ){
for ( my $j = 0; $j < $self->param('iColumns'); $j++) {
my $search_string = $self->param('sSearch');
my $searchable_ident = 'bSearchable_'.$j;
if ($self->param($searchable_ident) and $self->param($searchable_ident) eq 'true') {
my $column = _fnColumnToField($j, $command);
my $csearch = 'sSearch_'.$j;
if ( defined ( $self->param($csearch) ) and length($self->param($csearch)) > 0 ) {
$search_string = $self->param($csearch);
push @{$where{'-and'}},{ $column => { like => '%'.$search_string.'%'}};
} else {
push @{$where{'-or'}},{ $column => { like => '%'.$search_string.'%'}};
}
}
}
}
return %where;
}
[/code]
This is the modified _generate_where_clause that powers the back-end for column specific filtering. The nice thing about this is that it coexists with the full table filtering that was there before. My version is also modified to work with libapreq2 and DBIx::Class, so the syntax is a little different than the example, but modifying the example to incorporate this shouldn't be much work.
Hope this is helpful...
-steve j
The problem - I have a table with 17 columns. When the table is first rendered, only six of them are visible. I wanted to be able to filter on any of those six columns individually. I followed the example at http://datatables.net/release-datatables/examples/api/multi_filter.html, but I kept getting the wrong index for the columns, since that uses the index of the inputs to determine which column to search under, and my first column was hidden, throwing off the indexes. In addition, I'm using a heavily modified version of http://datatables.net/development/server-side/perl_mvc for the server side processing.
To make this work, I switched the javascript that calls fnFilter to use the column's tabindex, instead of its index. Then, I modified the inputs in the footer to have tabindex set to the column index of the column that each goes under. This has the added benefit of making sure that tabbing through the fields goes in the order that I display them in.
The mods - Here are the sections that I modified to make this work:
[code]
-
...
[/code]
The "=" is there just so a '-' shows up in the footer for the hidden field if it gets displayed. The class dtFullFoot is there because I have multiple datatables on the page and I wanted a way to make the sample code work specifically for a single datatable. The table itself is named "dtFull" so that's where that part of the name comes from.
[code]
$("input.dtFoot").keyup( function () {
/* Filter on the column (the tabindex) of this element */
oTable2.fnFilter( this.value, $(this).attr("tabindex"));
});
[/code]
This is the modified keyup function which filters based on the tabindex, rather than the input field's index.
[code]
sub _generate_where_clause {
my $self = shift;
my %where = ();
if ( defined ($self->param('sSearch') ) ){
for ( my $j = 0; $j < $self->param('iColumns'); $j++) {
my $search_string = $self->param('sSearch');
my $searchable_ident = 'bSearchable_'.$j;
if ($self->param($searchable_ident) and $self->param($searchable_ident) eq 'true') {
my $column = _fnColumnToField($j, $command);
my $csearch = 'sSearch_'.$j;
if ( defined ( $self->param($csearch) ) and length($self->param($csearch)) > 0 ) {
$search_string = $self->param($csearch);
push @{$where{'-and'}},{ $column => { like => '%'.$search_string.'%'}};
} else {
push @{$where{'-or'}},{ $column => { like => '%'.$search_string.'%'}};
}
}
}
}
return %where;
}
[/code]
This is the modified _generate_where_clause that powers the back-end for column specific filtering. The nice thing about this is that it coexists with the full table filtering that was there before. My version is also modified to work with libapreq2 and DBIx::Class, so the syntax is a little different than the example, but modifying the example to incorporate this shouldn't be much work.
Hope this is helpful...
-steve j
This discussion has been closed.