QueryBuilder orWhere inside andWhere

Question

QueryBuilder orWhere inside andWhere

Jonathan Vandal Jul '16

Created Jul '16	Last Reply Oct '16	Replies 1	Views 1954	Votes 0

Jonathan Vandal 41.3k

Jul '16

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 ?

Jonathan Vandal
41.3k

Accepted
answer

Jul '16

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.

alimo2 · Answer 1 · 2016-10-23T02:23:23-07:00

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.

suggest it to add to the master code!!!