Hello, I was looking for a way to implement AND functionality with the search box of jquery datatables and I ended up making some changes to the cakephp datatable component.
I just would like to share it.
I altered the ""// check for WHERE statement in GET request "" if BLOCK to:
// check for WHERE statement in GET request
if(isset($httpGet) && !empty($httpGet['sSearch'])){
// Check for AND terms
$sSearchTerms = explode(" ", $httpGet['sSearch'] );
if (count($sSearchTerms)<2){
$conditions = $this->getWhereConditions($httpGet['sSearch']);
if( !empty($this->controller->paginate['contain']) ){
$this->controller->paginate = array_merge_recursive($this->controller->paginate, array('contain'=>$conditions));
}
else{
$this->controller->paginate = array_merge_recursive($this->controller->paginate, array('conditions'=>array('AND'=> $conditions )));
}
}else{
foreach ($sSearchTerms as $sSearchTerm){
$conditions = $this->getWhereConditions($sSearchTerm);
$this->controller->paginate = array_merge_recursive($this->controller->paginate, array('conditions'=>array('AND'=> array($conditions))));
}
}
$isFiltered = true;
}
And altered the getWhereConditions function so that it can set the exploded sSearch term
private function getWhereConditions($sSearchTerm){
if( $this->mDataProp == false && !isset($this->controller->paginate['fields']) && empty($this->fields) ){
throw new Exception("Field list is not set. Please set the fields so I know how to build where statement.");
}
$conditions = array();
if($this->mDataProp == true){
for($i=0;$i<$this->controller->request->query['iColumns'];$i++){
if(!isset($this->controller->request->query['bSearchable_'.$i]) || $this->controller->request->query['bSearchable_'.$i] == true){
$fields[] = $this->controller->request->query['mDataProp_'.$i];
}
}
}
else if(!empty($this->fields) || !empty($this->controller->paginate['fields']) ){
$fields = !empty($this->fields) ? $this->fields : $this->controller->paginate['fields'];
}
foreach($fields as $x => $column){
// only create conditions on bSearchable fields
if( $this->controller->request->query['bSearchable_'.$x] == 'true' ){
if($this->mDataProp == true){
$conditions['OR'][] = array(
$this->controller->request->query['mDataProp_'.$x].' LIKE' => '%'.$sSearchTerm.'%'
);
}
else{
list($table, $field) = explode('.', $column);
// attempt using definitions in $model->validate to build intelligent conditions
if( $this->conditionsByValidate == 1 && array_key_exists($column,$this->model->validate) ){
if( !empty($this->controller->paginate['contain']) ){
if(array_key_exists($table, $this->controller->paginate['contain']) && in_array($field, $this->controller->paginate['contain'][$table]['fields'])){
$conditions[$table]['conditions'][] = $this->conditionByDataType($column);
}
}
else{
$conditions['OR'][] = $this->conditionByDataType($column);
}
}
else{
if( !empty($this->controller->paginate['contain']) ){
if(array_key_exists($table, $this->controller->paginate['contain']) && in_array($field, $this->controller->paginate['contain'][$table]['fields'])){
$conditions[$table]['conditions'][] = $column.' LIKE "%'.$sSearchTerm.'%"';
}
}
else{
$conditions['OR'][] = array(
$column.' LIKE' => '%'.$sSearchTerm.'%'
);
}
}
}
}
}
return $conditions;
}
Hello, I was looking for a way to implement AND functionality with the search box of jquery datatables and I ended up making some changes to the cakephp datatable component.
I just would like to share it.
I altered the ""// check for WHERE statement in GET request "" if BLOCK to:
And altered the getWhereConditions function so that it can set the exploded sSearch term
I hope it is of any help.