search

cnizzardini / cakephp-datatable

CakePHP Component for interoperability between CakePHP 2.x and jQuery DataTables plugin.
62 stars 49 forks source link

SQL error when searching or filtering on GROUP_CONCAT field #8

Closed walliby closed 10 years ago

walliby commented 10 years ago

I am not able to filter or sort on a field created with GROUP_CONCAT as shown below. I get a database error (Column not found: 1054 Unknown column '0.usergroups' in 'where clause'). Are you aware of any workaround for this issue?

$this->paginate = array( 'fields' => array('User.id','PersonUser.sort_name_classes','PersonUser.user_name'), 'order' => 'PersonUser.sort_name_classes', 'group' => array('User.id','PersonUser.sort_name_classes'), 'link' => array('UserGroup' => array('fields' => 'GROUP_CONCAT(UserGroup.group) as usergroups'),'PersonUser'));

$this->DataTable->fields = array('User.id','PersonUser.sort_name_classes','PersonUser.user_name','0.usergroups');

cnizzardini commented 10 years ago

Please use virtualFields instead. http://book.cakephp.org/2.0/en/models/virtual-fields.html

cristianfierro commented 10 years ago

I'm using virtual fields but when I use the search field of the datatable, the query brokes since it cannot do OR ((GROUP_CONCAT(DISTINCT Pharmacy.name SEPARATOR ', ')) LIKE '%Sa%')

Is there a way to tell the component not to filter on that field?

cnizzardini commented 10 years ago

Yes you do this in the dataTables options in your JS code. Set {bSearchable: false} in your aoColumns for the fields that you should not be searching on.