Global Search not work.

Hi, I have the default example working using my Users MVC, the pagination and sorting are working well, but when I make a search this not return the correct records.

There are some key code lines in my implementation.

bootstrap.php CakePlugin::load('DataTable');

//----------------------------------------------------------------------------------------- Layout.ctp echo $this->Html->script('http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js'); echo $this->Html->css('//cdn.datatables.net/1.10.2/css/jquery.dataTables.min.css'); echo $this->Html->script('//cdn.datatables.net/1.10.2/js/jquery.dataTables.min.js'); echo $this->fetch('dataTableSettings'); echo $this->fetch('script') //----------------------------------------------------------------------------------------- UsersController.php public $components = array('Paginator', 'Session', 'DataTable.DataTable' => array( 'triggerAction' => array('index'), // use if your action is not 'index' 'columns' => array( 'id' => true, // bSearchable and bSortable will be false 'username' => 'Usuario', // bSearchable and bSortable will be true, with a custom label Nombre 'name' => array( 'bSearchable' => 'customSearch',// will use model callback to add search conditions ), 'email' => 'email', 'Acciones' => null, // tells DataTable that this column is not tied to a field ), ), );

public $helpers = array(
    'DataTable.DataTable',
);

public function index() { $this->User->recursive = 0; $this->DataTable->paginate = array('User'); }

//----------------------------------------------------------------------------------------- index.ctp echo $this->DataTable->render(); //----------------------------------------------------------------------------------------- datatable/index.ctp <?php foreach($dtResults as $result) { $this->dtResponse['aaData'][] = array( $result['User']['id'], $result['User']['username'], $result['User']['name'], $result['User']['email'], ... 'actions', ); } ?> //----------------------------------------------------------------------------------------- Model User.php

public function customSearch($field, $searchTerm, $columnSearchTerm, $conditions) { if ($searchTerm) { $conditions[] = array("$field LIKE" => '%' . $searchTerm); // only do left search } if ($columnSearchTerm) { $conditions[] = array($field => $columnSearchTerm); // only do exact match } return $conditions; }

I'm using CakePHP 2.5.3 & DT 1.10.2. Thanks.

Hi @Sesshomario, did you get any luck on fixing your issue? And if so, can you advice how you did it?

You probably don't need the custom search stuff. They were given as an example. So, remove customSearch from User.php model file and see if the returned results are what you expect.

@fukie If you've done the same, try what I said above.

Hi @tigrang, thanks first for an amazing plugin, really simple and easy to use! I am not using custom search. I am about to retrieve all results, but when I perform a search, incorrect results are returned. And it affects one class. The other classes using datatables do not have such an issue.

Here are my codes for your perusal.

[index.ctp]

<script type="text/javascript">
    $(document).ready(function() {
        $('#myexample').dataTable( {
            "order": [[ 0, "desc" ]],
            "bStateSave": false,
            "iDisplayLength": 25,
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": '<?php echo $this->Html->url(array("action" => "index", "?" => array("model" => "AExpense"))); ?>',
        } );
    } );
</script>

<table id="myexample" class="display" cellspacing="0" width="100%">
        <thead> 
            <tr>

                <th>Date</th>
                <th>Category 1</th>
                <th>Category 2</th>
                <th>Payee</th>
                <th>Description</th>
                <th>Remarks</th>
                <th>Amount</th>
                <th>Tax Deductible</th>
                <th>Status</th>
                <th class="actions">Actions</th>
            </tr>
        </thead>

        <tbody>

        </tbody>
    </table>

[controller]

public $components = array('Session','RequestHandler',
        'DataTable.DataTable' => array(         
            //'triggerAction' => array('index'),  // use if your action is not 'index'
            'columns' => array(
                //'id' => false,                   // bSearchable and bSortable will be false
                'date' => 'Date',               // bSearchable and bSortable will be true, with a custom label `Name`                                
                'category_1' => 'Main Category',        // by default, the label with be a Inflector::humanize() version of the key
                'category_2' => 'Sub Category',
                // 'category_3' => 'Sub-sub Category',
                'payee' => 'Payee',
                'description' => 'Description',
                'remarks' => 'Remarks',
                'amount' => 'Amount',
                'tax_deductible' => 'Tax Deductible',
                'status' => 'Status',

                ),
                'Actions' => true,                  // tells DataTable that this column is tied to a field (set NULL if not tied)
            ),

    );

    public function index() {   
        $category = $this->AExpense->ACategory->find('list');
        $this->set(compact('category'));

        $this->DataTable->paginate = array('AExpense');
    }

[datatables\index.ctp]

<?php

    foreach($dtResults as $result) {

        $category_1 = "";
        $category_2 = "";

        if($result['AExpense']['remarks'] != '') {
            $remarks = "Remarks";
        } else {
            $remarks = "";
        }

        if($result['AExpense']['tax_deductible'] == 1) {
            $tax_deductible = "Yes";
        } else {
            $tax_deductible = "No";
        }

        foreach ($category as $key => $value) {
            if ($result['AExpense']['category_1'] == $key) {
                $category_1 = $value;
            }
            if ($result['AExpense']['category_2'] == $key) {
                $category_2 = $value;
            }       
        }

        $actionButtons =  $this->Html->link($this->Html->div('btn-group btn-group-xs btn btn-default',$this->Html->tag('span', '', array('class' => 'glyphicon glyphicon-search'))), array('action' => 'view', $result['AExpense']['id']), array('escapeTitle' => false, 'title' => 'View'))." ".
                $this->Html->link($this->Html->div('btn-group btn-group-xs btn btn-default',$this->Html->tag('span', '', array('class' => 'glyphicon glyphicon-pencil'))), array('action' => 'edit', $result['AExpense']['id']), array('escapeTitle' => false, 'title' => 'Edit'));

        $this->dtResponse['aaData'][] = array(
            $result['AExpense']['date'],
            $category_1,
            $category_2,
            $result['AExpense']['payee'],
            $result['AExpense']['description'],
            $remarks,
            "$".$result['AExpense']['amount'],          
            $tax_deductible,
            $result['AExpense']['status'],
            $actionButtons,

        );
    }
?>

I noticed that if I turned off SSP, it will return only the 1st page (which is expected) and the search would work fine on that page. In addition, I noticed that on this class, the datatables seem to search slower. My JSONs validate good.

I'm on dataTables 1.10.0, jQuery . Not sure about the version for your plugin though, but I downloaded it about 1 year back.

Hope you can assist me as I've been on this for about 2-3 weeks and its killing me... Thanks mate!

What's SSP? Do you have a link where I can check it out? Message me on freenode: tigrang

SSP server side processing.

I'll try to get it up later tonight? But if you can shoot some possible solutions in the meantime, that would be great! (:

Best Regards Fu Keong

Sent from my iPad

On 3 Feb 2015, at 9:04 am, Tigran Gabrielyan notifications@github.com wrote:

What's SSP? Do you have a link where I can check it out? Message me on freenode: tigrang

— Reply to this email directly or view it on GitHub.

@fukie I'm not sure what exactly isn't working so if I can see it it will help me help you. I am on IRC for the next hour.

Can you post your model as well? Also, what search term do you enter, what do you expect to be returned, and what is returned?

Hi @tigrang, here is my model.

<?php
App::uses('AppModel', 'Model');
/**
 * AExpense Model
 *
 */
class AExpense extends AppModel {

/**
 * Display field
 *
 * @var string
 */
    public $displayField = 'description';

/**
 * Validation rules
 *
 * @var array
 */
    public $validate = array(
        'date' => array(
            'date' => array(
                'rule' => array('date'),
                //'message' => 'Your custom message here',
                //'allowEmpty' => false,
                //'required' => false,
                //'last' => false, // Stop validation after this rule
                //'on' => 'create', // Limit validation to 'create' or 'update' operations
            ),
        ),
        'category_1' => array(
            'notEmpty' => array(
                'rule' => array('notEmpty'),
                //'message' => 'Your custom message here',
                //'allowEmpty' => false,
                //'required' => false,
                //'last' => false, // Stop validation after this rule
                //'on' => 'create', // Limit validation to 'create' or 'update' operations
            ),
        ),
        'payee' => array(
            'notEmpty' => array(
                'rule' => array('notEmpty'),
                //'message' => 'Your custom message here',
                //'allowEmpty' => false,
                //'required' => false,
                //'last' => false, // Stop validation after this rule
                //'on' => 'create', // Limit validation to 'create' or 'update' operations
            ),
        ),
        'description' => array(
            'notEmpty' => array(
                'rule' => array('notEmpty'),
                //'message' => 'Your custom message here',
                //'allowEmpty' => false,
                //'required' => false,
                //'last' => false, // Stop validation after this rule
                //'on' => 'create', // Limit validation to 'create' or 'update' operations
            ),
        ),
        'amount' => array(
            'numeric' => array(
                'rule' => array('numeric'),
                //'message' => 'Your custom message here',
                //'allowEmpty' => false,
                //'required' => false,
                //'last' => false, // Stop validation after this rule
                //'on' => 'create', // Limit validation to 'create' or 'update' operations
            ),
        ),
        'tax_deductible' => array(
            'boolean' => array(
                'rule' => array('boolean'),
                //'message' => 'Your custom message here',
                //'allowEmpty' => false,
                //'required' => false,
                //'last' => false, // Stop validation after this rule
                //'on' => 'create', // Limit validation to 'create' or 'update' operations
            ),
        ),
        'status' => array(
            'notEmpty' => array(
                'rule' => array('notEmpty'),
                //'message' => 'Your custom message here',
                //'allowEmpty' => false,
                //'required' => false,
                //'last' => false, // Stop validation after this rule
                //'on' => 'create', // Limit validation to 'create' or 'update' operations
            ),
        ),
    );

    public $hasMany = array(
        'ACategory' => array(
            'className' => 'ACategory',
            'foreignKey' => 'id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        ),
        'Status' => array(
            'className' => 'Status',
            'foreignKey' => 'id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        ),
    );
}

So for example if I enter 'Linda' it should only show me the records with 'Linda' in it. But its retrieving a ton of other results that do not have 'Linda' in it... Any e-mail / platform that I can privately PM you the login details?

For example, when I search for "Irene" (sSearch:Irene), the return JSON is

{
    "iTotalRecords": 300,
    "iTotalDisplayRecords": 146,
    "sEcho": 2,
    "aaData": [
        [
            "2016-02-02",
            "Other Income",
            "Travel/Transport",
            "Irene Rodriquez",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor.",
            "",
            "6.27",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/105\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/105\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2016-02-01",
            "Other Income",
            "Other Income",
            "Carissa Crawford",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed",
            "Remarks",
            "6.23",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/313\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/313\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2016-01-26",
            "Other Income",
            "Business Expense",
            "Rae Brown",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing lacus. Ut nec urna",
            "Remarks",
            "8.65",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/224\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/224\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2016-01-19",
            "Other Income",
            "Business Expense",
            "Travis M. Higgins",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing lacus. Ut nec urna",
            "",
            "0.50",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/365\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/365\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2016-01-14",
            "Insurance",
            "Operation Expense",
            "Cole F. Mills",
            "Lorem ipsum dolor sit",
            "",
            "3.99",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/396\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/396\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2016-01-14",
            "Business Expense",
            "Wages",
            "Faith Burris",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing lacus. Ut nec",
            "",
            "9.19",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/247\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/247\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2016-01-12",
            "Operation Expense",
            "Meal & Entertainment",
            "Hollee Frederick",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor.",
            "Remarks",
            "6.80",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/203\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/203\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2016-01-08",
            "Other Income",
            "Interest",
            "Farrah Gibbs",
            "Lorem ipsum dolor sit amet,",
            "Remarks",
            "0.45",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/323\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/323\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-12-12",
            "Tax Rebate/Refund",
            "Travel/Transport",
            "Rama Browning",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing lacus. Ut",
            "Remarks",
            "9.37",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/264\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/264\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-12-10",
            "Gov Grant ",
            "Supplies",
            "Whilemina T. Mathews",
            "Lorem",
            "",
            "4.78",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/308\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/308\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-12-09",
            "Other Income",
            "Supplies",
            "Cameran V. Guerrero",
            "Lorem ipsum dolor sit amet, consectetuer",
            "Remarks",
            "3.20",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/141\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/141\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-12-05",
            "Other Income",
            "Meal & Entertainment",
            "Neville Mercer",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing",
            "",
            "9.51",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/190\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/190\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-11-23",
            "Business Expense",
            "Gov Grant ",
            "Margaret R. Bradshaw",
            "Lorem",
            "",
            "0.68",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/129\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/129\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-11-22",
            "Insurance",
            "Wages",
            "Stuart Walker",
            "Lorem ipsum",
            "Remarks",
            "8.84",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/162\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/162\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-11-15",
            "Debit Note(From Supplier)",
            "Other Income",
            "Shaeleigh Hester",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing",
            "",
            "7.03",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/343\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/343\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-11-15",
            "Insurance",
            "Other Income",
            "Frances David",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur",
            "",
            "2.10",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/250\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/250\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-11-14",
            "Business Expense",
            "Other Income",
            "Shoshana L. Suarez",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing lacus.",
            "Remarks",
            "8.70",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/191\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/191\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-11-09",
            "Fees",
            "Training",
            "Dale Q. Christian",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam",
            "Remarks",
            "6.52",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/363\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/363\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-11-08",
            "Debit Note(From Supplier)",
            "Business Expense",
            "Xavier G. Shields",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing lacus. Ut nec urna",
            "",
            "7.99",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/293\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/293\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-10-29",
            "Tax Rebate/Refund",
            "Training",
            "Brennan P. Hardy",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit.",
            "Remarks",
            "9.70",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/297\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/297\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-10-27",
            "Other Income",
            "Travel/Transport",
            "Quemby Stevens",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing lacus.",
            "",
            "8.40",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/271\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/271\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-10-25",
            "Fees",
            "Fees",
            "Joshua Y. Herrera",
            "Lorem",
            "",
            "7.98",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/359\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/359\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-10-22",
            "Debit Note(From Supplier)",
            "Training",
            "Samuel T. Rowe",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed tortor. Integer aliquam adipiscing lacus. Ut",
            "Remarks",
            "8.14",
            "Yes",
            "Unpaid",
            "<a href=\"/wis/a_expenses/view/255\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/255\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-10-21",
            "Operation Expense",
            "Gov Grant ",
            "Yasir G. Garza",
            "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur",
            "Remarks",
            "7.87",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/237\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/237\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
        [
            "2015-10-20",
            "Fees",
            "Travel/Transport",
            "Bree C. Frye",
            "Lorem ipsum dolor sit amet,",
            "Remarks",
            "4.36",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/231\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/231\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ]
    ]
}

It will validate fine but it shows a lot of incorrectly matched records...

Hi fukie,

Long ago I found the solution, but today I don't remember well because I had to abandon my cakephp App. I have the next code in the archives: View/Layouts/layout.ctp

<link rel="stylesheet" href="//cdn.datatables.net/1.10.2/css/jquery.dataTables.min.css">
 <script src="//cdn.datatables.net/1.10.2/js/jquery.dataTables.min.js"></script>
        echo $this->fetch('meta');
        echo $this->fetch('css');
        echo $this->fetch('dataTableSettings');
        echo $this->fetch('script');

Controller

public $components = array('Paginator', 'Session',
    'DataTable.DataTable' => array(
            'triggerAction' => array('index'),  // use if your action is not 'index'
            'columns' => array(
                'User.id' => array(
                'label' => 'Id',
                'bSearchable' => false,
                'bSortable' => false,
                ),                      // bSearchable and bSortable will be false
                'User.username' => 'Usuario',               // bSearchable and bSortable will be true, with a custom label `Nombre`
                'User.name' => array( 
                'label' => 'Nombre',
                'bSearchable' => 'customSearch',
                'bSortable' => true
            ),
                'User.email' => 'email',
                'Group.name' => array(
                'label' => 'Grupo',
                'bSearchable' => 'customSearch',
                'bSortable' => true
                ),
                'User.created' => array(
                'label' => 'Creado',
                'bSearchable' => false,
                'bSortable' => true,
                ),
                'User.modified' => array(
                'label' => 'Modificado',
                'bSearchable' => false,
                'bSortable' => true,
                ),
                'User.status' => array(
                'label' => 'Status',
                'bSearchable' => false,
                'bSortable' => false,
                ),
                'Acciones' => null,                  // tells DataTable that this column is not tied to a field
                'User.group_id' => array(
                'label' => 'idgrupo',
                'bSearchable' => false,
                'bSortable' => false,
                ),
            ), 
        ),
    );

    public $helpers = array(
        'DataTable.DataTable' //=> ['scriptBlock' => false],
    );

public function index() {
        $this->User->recursive = 0;
        if ($this->request->is('ajax')) {
        $this->DataTable->paginate = array('User');
        }
    }

Model

public function customSearch($field, $searchTerm, $columnSearchTerm, $conditions) {
        if ($searchTerm) {
            $conditions[] = array("$field LIKE" => '%' . $searchTerm);  // only do left search
        }
        if ($columnSearchTerm) {
            $conditions[] = array($field => $columnSearchTerm);         // only do exact match
        }
        return $conditions;
    }

datatable/index.ctp

<?php
foreach($dtResults as $result) {
$this->dtResponse['aaData'][] = array(
$result['User']['id'], 
$result['User']['username'],
$result['User']['name'],
$result['User']['email'],
$this->Html->link($result['Group']['name'], array('controller' => 'groups', 'action' => 'view', $result['User']['group_id'])),
$result['User']['created'],
$result['User']['modified'],
$result['User']['status'],
$this->Html->link('<span class="glyphicon glyphicon-search"></span>', array('action' => 'view', $result['User']['id']), array('escape' => false)).' '.$this->Html->link('<span class="glyphicon glyphicon-edit"></span>', array('action' => 'edit', $result['User']['id']), array('escape' => false)).' '.$this->Form->postLink('<span class="glyphicon glyphicon-remove"></span>', array('action' => 'delete', $result['User']['id']), array('escape' => false), __('Esta seguro de querer eliminar el registro # %s?', $result['User']['id'])),
$result['User']['group_id'],
);
}
?>

index.ctp

<div class="users index">

    <div class="row">
        <div class="col-sm-12">
            <div class="page-header">
                <h1><?php echo __('Usuarios'); ?></h1>
            </div>
        </div><!-- end col md 12 -->
    </div><!-- end row -->

    <div class="row">
        <div class="col-sm-2">
            <div class="actions">
                <div class="panel panel-default">
                    <div class="panel-heading">Acciones</div>
                        <div class="panel-body">
                            <ul class="list-group">
                                <li class="list-group-item"><?php echo $this->Html->link(__('<span class="glyphicon glyphicon-plus"></span>&nbsp;&nbsp;Nuevo Usuario'), array('action' => 'add'), array('escape' => false)); ?></li>
                            </ul>
                        </div><!-- end body -->
                </div><!-- end panel -->
            </div><!-- end actions -->
        </div><!-- end col md 3 -->

        <div class="col-sm-9">
<?php 

            echo $this->DataTable->render(); 
             ?>

        </div> <!-- end col md 9 -->
    </div><!-- end row -->
</div>

My Json Filtered by "Mario"

{"iTotalRecords":11,"iTotalDisplayRecords":1,"sEcho":16,"aaData":[["1","Mario","Mario Mars","mario@hotmail.com","<a href=\"\/comunica_cake\/groups\/view\/1\">Administrador<\/a>","2014-08-08 22:29:11","2014-08-11 18:00:38",true,"<a href=\"\/comunica_cake\/users\/view\/1\"><span class=\"glyphicon glyphicon-search\"><\/span><\/a> <a href=\"\/comunica_cake\/users\/edit\/1\"><span class=\"glyphicon glyphicon-edit\"><\/span><\/a> <form action=\"\/comunica_cake\/users\/delete\/1\" name=\"post_54d0f546b5893947502621\" id=\"post_54d0f546b5893947502621\" style=\"display:none;\" method=\"post\"><input type=\"hidden\" name=\"_method\" value=\"POST\"\/><\/form><a href=\"#\" onclick=\"if (confirm(&quot;Esta seguro de querer eliminar el registro # 1?&quot;)) { document.post_54d0f546b5893947502621.submit(); } event.returnValue = false; return false;\"><span class=\"glyphicon glyphicon-remove\"><\/span><\/a>","1"]]}

Only the fields with custom search are filtered I hope this help you.

@fukie Before this line https://github.com/tigrang/cakephp-datatable/blob/master/Controller/Component/DataTableComponent.php#L176 add 'sql_log' => ConnectionManager::getDataSource('default')->getLog(),

Then use firebug or similar to view the returned data after a search and paste the queries here.

@tigrang as requested. FYI, I trimmed out most of the aaData.

{
    "iTotalRecords": 300,
    "iTotalDisplayRecords": 146,
    "sEcho": 3,
    "aaData": [
        [
            "2015-10-20",
            "Fees",
            "Travel/Transport",
            "Bree C. Frye",
            "Lorem ipsum dolor sit amet,",
            "Remarks",
            "4.36",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/231\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/231\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ]
    ],
    "sql_log": {
        "log": [
            {
                "query": "SELECT `UserAccessLevel`.`id`, `UserAccessLevel`.`user_type_id`, `UserAccessLevel`.`class`, `UserAccessLevel`.`allow`, `UserType`.`id`, `UserType`.`user_type` FROM `hengkee`.`user_access_levels` AS `UserAccessLevel` LEFT JOIN `hengkee`.`user_types` AS `UserType` ON (`UserAccessLevel`.`user_type_id` = `UserType`.`id`)  WHERE `user_type_id` = 1",
                "params": [],
                "affected": 58,
                "numRows": 58,
                "took": 0
            },
            {
                "query": "SELECT `ACategory`.`id`, `ACategory`.`category` FROM `hengkee`.`a_categories` AS `ACategory`   WHERE 1 = 1",
                "params": [],
                "affected": 20,
                "numRows": 20,
                "took": 0
            },
            {
                "query": "SELECT COUNT(*) AS `count` FROM `hengkee`.`a_expenses` AS `AExpense`   WHERE 1 = 1    LIMIT 20",
                "params": [],
                "affected": 1,
                "numRows": 1,
                "took": 0
            },
            {
                "query": "SELECT `AExpense`.`date`, `AExpense`.`category_1`, `AExpense`.`category_2`, `AExpense`.`category_3`, `AExpense`.`payee`, `AExpense`.`description`, `AExpense`.`remarks`, `AExpense`.`amount`, `AExpense`.`tax_deductible`, `AExpense`.`status`, `AExpense`.`id` FROM `hengkee`.`a_expenses` AS `AExpense`   WHERE ((`AExpense`.`date` LIKE '%irene%') OR (`AExpense`.`category_1` LIKE '%irene%') OR (`AExpense`.`category_2` LIKE '%irene%') OR (`AExpense`.`category_3` LIKE '%irene%') OR (`AExpense`.`payee` LIKE '%irene%') OR (`AExpense`.`description` LIKE '%irene%') OR (`AExpense`.`remarks` LIKE '%irene%') OR (`AExpense`.`amount` LIKE '%irene%') OR (`AExpense`.`tax_deductible` LIKE '1') OR (`AExpense`.`status` LIKE '%irene%'))   ORDER BY `AExpense`.`date` desc  LIMIT 25",
                "params": [],
                "affected": 25,
                "numRows": 25,
                "took": 1
            },
            {
                "query": "SELECT `ACategory`.`id`, `ACategory`.`category_type`, `ACategory`.`category_level`, `ACategory`.`category`, `ACategory`.`parent_category`, `ACategory`.`tax_deductible`, `ACategory`.`tax_payable` FROM `hengkee`.`a_categories` AS `ACategory`   WHERE `ACategory`.`id` IN (105, 313, 224, 365, 396, 247, 203, 323, 264, 308, 141, 190, 129, 162, 343, 250, 191, 363, 293, 297, 271, 359, 255, 237, 231)",
                "params": [],
                "affected": 0,
                "numRows": 0,
                "took": 0
            },
            {
                "query": "SELECT `Status`.`id`, `Status`.`type`, `Status`.`status` FROM `hengkee`.`statuses` AS `Status`   WHERE `Status`.`id` IN (105, 313, 224, 365, 396, 247, 203, 323, 264, 308, 141, 190, 129, 162, 343, 250, 191, 363, 293, 297, 271, 359, 255, 237, 231)",
                "params": [],
                "affected": 0,
                "numRows": 0,
                "took": 0
            },
            {
                "query": "SELECT COUNT(*) AS `count` FROM `hengkee`.`a_expenses` AS `AExpense`   WHERE ((`AExpense`.`date` LIKE '%irene%') OR (`AExpense`.`category_1` LIKE '%irene%') OR (`AExpense`.`category_2` LIKE '%irene%') OR (`AExpense`.`category_3` LIKE '%irene%') OR (`AExpense`.`payee` LIKE '%irene%') OR (`AExpense`.`description` LIKE '%irene%') OR (`AExpense`.`remarks` LIKE '%irene%') OR (`AExpense`.`amount` LIKE '%irene%') OR (`AExpense`.`tax_deductible` LIKE '1') OR (`AExpense`.`status` LIKE '%irene%'))",
                "params": [],
                "affected": 1,
                "numRows": 1,
                "took": 1
            }
        ],
        "count": 7,
        "time": 2
    }
}

@fukie Here's the issue: AExpense.tax_deductible LIKE '1' seems like cake is casting the search term to a bool '1'. So in the component configuration change 'tax_deductible' => 'Tax Deductible', to

'tax_deductible' => array(
    'label' => 'Tax Deductible',
    'bSearchable' => false,
),

Or provide a custom search for that so if the enter in say "tax_deductible:yes" then you can add the condition

Wow @tigrang that did the job! OMG. I have verified it against my live data too. Really appreciate the help! Is there a way to modify the logic behind so that it can search for such columns that are stored with TINYINT(1)?

Because with the above change, the search works, but I can't search under 'Tax Deductible'. Although its insignificant in my current scenario, but I'm concern if this repeats again for something more critical. Unless I modify the table to another datatype?

How would you expect it to work for a boolean? If they search text, it will always be a truethy value. What you can do is add a select box under that column, with 'Yes', 'No', and an empty option. When the value of it changes, call in javascript dataTable.fnFilter(THE_SELECTED_OPTION, INDEX_OF_TAX_COLUMN) and use a customSearch server side to only add the condition if its a '1' or '0' rather than a word search term.

Edit: What I think I can do is add an option to exclude a column from global search, so then you wouldn't need the customSearch.

Ah okay. The idea of the filter is possible option. I can consider to explore that route. I guess what I was trying to achieve was to search based on the outputs rather than the actual stored table values. For example, I'm storing 0, 1 for No and Yes. So I guess, I would like to check if its possible to search for No and Yes even though its stored as 0 and 1 in the tables.

While typing the above, I'm also wondering whether is it possible to use bSearchable to create a custom search to recognize that 0 and 1 are actually No and Yes, and filter from there? But still retaining the default search ability for other columns?

Just bouncing around for some ideas.

Sure you can. Just use a custom search and do something like:

switch (strtolower($searchTerm)) {
    case 'yes':
        $conditions[] = array('tax_deductable' => true);
        break;
    // etc
}

If there are any issues or you're unclear how to do a custom search for a column let me know.

Ah, spot on, on my question (: Your instructions are good but seems like I can't get correct search results for 'no' conditions though. I understand if any part of the record has 'no', it will return the record, but seems like a few records that does not contain 'no' is returned. Search results for 'Yes' returns well. When I wrote the whole chunk and pasted the code below, I realised it was in "Remarks" that contained the 'no'. No further issues (:

I will take a shot at the filter option sometime soon hopefully and hope all goes well!

Thanks for the help @tigrang, really much appreciated and great plugin. I hope to use it in a more 'advanced' manner in future rather than just a simple search all (:

Part of datatable in controller

'tax_deductible' => array(
                    'label' => 'Tax Deductible',
                    'bSearchable' => 'customSearchTax',
                ),

Model for bSearchable class

    public function customSearchTax($field, $searchTerm, $columnSearchTerm, $conditions) {
        switch (strtolower($searchTerm)) {
            case 'yes':
                $conditions[] = array('tax_deductible' => 1);
                break;
            case 'no':
                $conditions[] = array('tax_deductible' => 0);
                break;
            }
        return $conditions;
    }

JSON results

{
    "iTotalRecords": 75,
    "iTotalDisplayRecords": 44,
    "sEcho": 3,
    "aaData": [
        [
            "2015-01-22",
            "Operation Expense",
            "Credit Note(To Customer)",
            "",
            "Issued credit note to xxx",
            "",
            "37.17",
            "No",
            "Paid",
            "<a href=\"/wis/a_expenses/view/75\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/75\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ]
        [
            "2015-01-15",
            "Operation Expense",
            "Supplies",
            "xxx",
            "A4 80g Business $3.30/unit 148.50\r\nGST: $10.40",
            "Remarks",
            "158.90",
            "Yes",
            "Paid",
            "<a href=\"/wis/a_expenses/view/55\" title=\"View\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-search\"></span></div></a> <a href=\"/wis/a_expenses/edit/55\" title=\"Edit\"><div class=\"btn-group btn-group-xs btn btn-default\"><span class=\"glyphicon glyphicon-pencil\"></span></div></a>"
        ],
    ]
}

Cool. Marking this as closed.

tigrang / cakephp-datatable

Global Search not work. #55