Adding Where in controller then search and sorting does not work

[suhardiyan]

Hi i got dificulties with this plugin, here is my controller code : $data = $this->DataTables->find('Students', 'all', [ 'order' => ['Students.id' => 'desc'], ])->where(['current_state' => 'interview_complete'])->orWhere(['current_state' => 'test_pass'])->orWhere(['current_state' => 'test_not_pass']);

u can check it here : https://prnt.sc/h1bb1x

When i open the page for first time its working (the content is appropriate with paging), but if I do searching and filtering : its become : https://prnt.sc/h1bbr0 the searching or filtering ignore my where() in controller .

How can I fix that ?

Its really confusing.

[ypnos-web]

DataTables->find() calls count() on the query object. Even if altering the query afterwards would work, DataTables would get confused with the mismatched count.

The solution is that you have to the query ready before the DataTablesComponent processes it. There are two ways to do so:

1. Use a custom finder method which attaches the WHERE statements. This is why you can pass the name of the finder to DataTables->find().
2. Set the query options beforehand, e.g.:

        $options = [
            'contain' => ['Departments', 'Systemgroups'],
            'conditions' => ['Users.active' => $showActive],
            'order' => ['username' => 'asc']
        ];

        $data = $this->DataTables->find('Users', 'all', $options);

Same for the ORDER clause. You need to set it before DataTables-find(). The user might change the ordering in DataTables. This will overwrite the previously set order.

[suhardiyan]

Dude its was awesome, u did a great job.

Need time to understand cakePHP and this plugins.

thanks, also now my datatable().ajax.reload() is working again.