How to Use dt.init.columnSearch

[dividedbynothing]

Hi,

I have a well working Cake datatables out of the box here. It works great! I would like to add column-specific search fields to each column in the footer (as in this. I'm assuming the callback function dt.init.columnSearch will provide that. My question is how and where to call it and use it. I have put it into my $options but do not get any result as expected.

My template code using the plugin is this (setting the callback as the first option):

<?php
    $this->Html->script([
    'jquery-2.2.0.min.js',
    'jquery.dataTables.min.js',
    'dataTables.bootstrap.min.js',
    'DataTables.cakephp.dataTables.js',
], ['block' => true]'     
                   );

$this->Html->css('dataTables.bootstrap.css', ['block' => true]);  

$options = [
        'init' => [
            $this->DataTables->callback('dt.init.columnSearch', [3])],
    'ajax' => [
        'url' => $this->Url->build() // current controller, action, params
    ],
    'data' => $data,
    'deferLoading' => $data->count(), // https://datatables.net/reference/option/deferLoading

    'columns' => [
        [
            'name' => 'Items.part_number',
            'data' => 'part_number',
            'title' => __('Part Number'), // or '' 
        ],
        [
            'name' => 'Items.description',
            'data' => 'description',
            'title' => __('Description'),
        ],
        [      
        'title' => __('View'), // or ''
        'data' => 'id',
        'sortable' => false,
        'searchable' => false,
        'render' => $this->DataTables->callback('myLinkBuilder', [__('View'), $this->Url->build(['action' => 'view'])]),
        ],
      ],
    'order' => [3, 'asc'], 
];`

echo $this->DataTables->table('users-table', $options, ['class' => 'table table-striped']);
?>  
</table>

Should I be seeing the search fields at the footer now? If not, what am I missing? Sorry if it's something obvious...

Thanks in advance for any advice... DBZ

[dividedbynothing]

Update to this. I found that if I use the default search box (which searches all searchable fields),

I get the error, "DataTables warning: table id=users-table - Invalid JSON response." followed by "DataTables warning: table id=users-table - Invalid JSON response.

When I remove the code 'init' => [$this->DataTables->callback('dt.init.columnSearch', [3])], This error goes away...

[ypnos-web]

Hello,

1. DataTables does not provide the functionality to create a header/footer with search inputs, so we also do not provide it. You need to write it yourself, e.g.

$id = 'users-table';

$footer = '';
$input = $this->Html->tag('input', '', [
    'type' => 'text',
    'class' => 'form-control input-sm',
    'style' => ' min-width: 0; width: auto; display: inline;',
]);
foreach ($options['columns'] as $c) {
    if (!($options['visible'] ?? true))
        continue;
    $control = ($options['searchable'] ?? true) ? $input : '';
    $footer .= $this->Html->tag('td', $control);
}
$footer = $this->Html->tag('tfoot', $this->Html->tag('tr', $footer));

echo $this->Html->tag('table', $footer, [
    'id' => $id,
    'class' => 'table table-striped dataTable'
]);

echo $this->Html->scriptBlock($this->DataTables->draw("#$id", $options));

2. The method dt.init.columnSearch() is an example for wiring the input fields with DataTables to trigger column-based searches. If it doesn't fit your needs you can write your own customized version of it. I believe that the method only works correctly if you actually have the footer, if you don't have it, it might mangle with the global search as you experienced.

[dividedbynothing]

Hi Ypnos,

Thanks very much for your reply and explanation, and also the e.g. I will play with it further based on this. I also looked at Issue #20 which is also helpful.

I'll let you know how it goes.

Thx again for your reply and patience. D

[dividedbynothing]

Hi Again, Yep! That works! Here's my whole code:

<?php
$this->Html->script([
    'jquery-2.2.0.min.js',
    'jquery.dataTables.min.js',
    'dataTables.bootstrap.min.js',
    'DataTables.cakephp.dataTables.js',
], ['block' => true]          
                   );
$this->Html->css('dataTables.bootstrap.css', ['block' => true]);   

$options = [
    'init' => [
            $this->DataTables->callback('dt.init.columnSearch', [3])],
    'ajax' => [
        'url' => $this->Url->build() // current controller, action, params
    ],
    'data' => $data,
    'deferLoading' => $data->count(), // https://datatables.net/reference/option/deferLoading
    'columns' => [
        [
            'name' => 'Items.part_number',
            'data' => 'part_number',
            'title' => __('Part Number'), // or ''            
        ],
        [
            'name' => 'Items.description',
            'data' => 'description',
            'title' => __('Description'),
        ],
        [
        'title' => __('View'), // or ''
        'data' => 'id',
        'sortable' => false,
        'searchable' => false,
        'render' => $this->DataTables->callback('myLinkBuilder', [__('View'), $this->Url->build(['action' => 'view'])]),
        ],
    ],
    'order' => [3, 'asc'], // order by username

];

$id = 'items-table';

$footer = '';
$input = $this->Html->tag('input', '', [
    'type' => 'text',
    'class' => 'form-control input-sm',
    'style' => ' min-width: 0; width: auto; display: inline;',
]);
foreach ($options['columns'] as $c) {
    if (!($options['visible'] ?? true))
        continue;
    $control = ($options['searchable'] ?? true) ? $input : '';
    $footer .= $this->Html->tag('td', $control);
}
$footer = $this->Html->tag('tfoot', $this->Html->tag('tr', $footer));

echo $this->Html->tag('table', $footer, [
    'id' => $id,
    'class' => 'table table-striped dataTable'
]);

 echo $this->Html->scriptBlock($this->DataTables->draw("#$id", $options));

  ?>  

I've still got some tweaking to do but the functionality is there. Thanks a lot for your help, and most of all for this awesome plugin. D