Phalcon datatables adapter where condition not working

Question

I'm using phalcon-datatables this is a datatables adapter for Phalcon PHP.

I have a where condition in my QueryBuilder, e.g :

use \DataTables\DataTable;

class TestController extends \Phalcon\Mvc\Controller {
    public function indexAction() {
        if ($this->request->isAjax()) {
          $builder = $this->modelsManager->createBuilder()
                          ->columns('id, name, email, age')
                          ->from('Example\Models\User');
                          ->where("age = :age:", array("age" => 30))

          $dataTables = new DataTable();
          $dataTables->fromBuilder($builder)->sendResponse();
        }
    }
}

But I have a problem when I'm using the global search. The where condition is ignored then it will show me all record in my database with the search condition.

When I'm using the column search it works well.

I think the problem is in this class -> Github line 21-23

For the global search we need to get all column and this library use orWhere for the global search.

    $this->bind('global_search', function($column, $search) {
      $this->builder->orWhere("{$column} LIKE :key_{$column}:",   ["key_{$column}" => "%{$search}%"]);
    });

And the column search use andWhere.

$this->bind('column_search', function($column, $search) {
  $this->builder->andWhere("{$column} LIKE :key_{$column}:", ["key_{$column}" => "%{$search}%"]);
});

So I think this is why the column search works with a where condition in the QueryBuilder. And this is why the global search doesn't work.

Do you have any idea to fix this problem inside the library ?

EDIT :

I think I have a solution. I have to put brackets on the orWhere.

But how can I do to make this query with phalcon ?

SELECT *
FROM `users` 
WHERE age = 30 
AND (name like '%d%' 
OR email like '%d%'
OR login like '%d%')

You could extend `QueryBuilder` with your own class `MyQueryBuilder extends QueryBuilder`. And then just overwrite the `getResponse()` method with your custom fix. — Timothy, Jul 21 '16 at 10:50
It's not phalcon official extension. Just write issue on this repository and/or do PR to it. — Juri, Jul 21 '16 at 10:54

score 1 · Accepted Answer · answered Jul 21 '16 at 14:26

I solved my problem. I this can help anyone. I edited the class with this :

I declared 3 members inside the class :

  private $condition = '';      // member condition for the builder
  private $binding = array();   // member bind for the builder
  private $first_search = true; // flag to detect first search

And in the getResponse() function I added this code :

$this->bind('global_search', function($column, $search) {
  // Add OR if isn't the first condition
  $this->condition .= $this->first_search ? "{$column} LIKE :key_{$column}:" : " OR {$column} LIKE :key_{$column}:";
  $this->binding["key_{$column}"] = "%{$search}%";
  $this->first_search = false;
});

// Add andWhere condition for global_search if needed
if( !empty($this->condition) && !empty($this->binding) && !$this->first_search ){
  $this->builder->andWhere($this->condition, $this->binding);
  $this->first_search = true;
}

I totally removed the orWhere condition and I concat the condition and the bind inside 2 variables and I wrap them inside the andWhere condition to total of the OR condition.

I think it is better to extend the class rather than making edits. This to avoid future problems when updating the library. Another **even better solution** would be to create a pull request and apply your _fix_ there. This way, other users of the library can benefit from your _improvements_ — Timothy, Jul 21 '16 at 16:57
Yes I m agree with you but this the developer of this lib doesnt update anymore — John, Jul 21 '16 at 17:09

Phalcon datatables adapter where condition not working

1 Answers1