Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

QueryBuilder orWhere inside andWhere

Hi,

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

The where condition not working for the global search. Here is my code :

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,login')
                          ->from('Example\Models\User');
                          ->where("age = :age:", array("age" => 30))

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

I noticed a problem inside the library in this class -> Github line 21-23

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

For the global search the orWhere condition is used and I have a problem with that because this will ignore my first where condition ->where("age = :age:", array("age" => 30))

I want to make this query with QueryBuilder to modify this class:

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

How can I edit the bind function to make it work ?



12.7k
Accepted
answer

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


    // 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.



2.1k

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

// In the getResponse() function I added this code $this->bind('globalsearch', function($column, $search) { // Add OR if isn't the first condition $this->condition .= $this->firstsearch ? "{$column} LIKE :key{$column}:" : " OR {$column} LIKE :key{$column}:"; $this->binding["key{$column}"] = "%{$search}%"; $this->firstsearch = false; });

// Add andWhere condition for globalsearch if needed if( !empty($this->condition) && !empty($this->binding) && !$this->firstsearch ){ $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.

suggest it to add to the master code!!!