Zend Framework 2: DB queries with multiple or/and conditions

Database queries usually are quite simple, but sometimes we have to build more complex queries.

Let’s have an example

We do have a search form where user can select one or many colours of product and one or many sizes.
In database we have product attributes stored in the following way:

product_attributes: {product_id: 1, colour: "yellow, red, black, blue, green", size: "M, L, XL"}

So attributes like colours or sizes are stored as the comma separated values in single columns.
Note: I don’t want to discuss about such data storage solution – I just want to use it to show the way of building queries.

Raw sql query

For this example we can assume that user have choosen colors: red and blue and sizes: L and XL.

SELECT product_id FROM product_attributes WHERE (colour LIKE '%red%' OR colour LIKE '%blue%') AND (size LIKE '% L%' or size LIKE '% XL%');

Build a query in ZF2

The main key of this task is to proper set of AND/OR combinations and group it in brackets. So we have to use class Zend\Db\Sql\Where to build the conditions but also we have to manipulate Zend\Db\Sql\Where->nest() method properly.

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Where;

$oSelect = new Select($this->table);
$oSelect->quantifier('DISTINCT');

$oWhere = new Where();

//build condition for colours
$firstIteration = true;
$oWhere = $oWhere->nest();
foreach($choosenColours as $colour) {
  if(!$firstIteration) {
    $oWhere = $oWhere->or->like('colour', '%' . $colour . '%');
  } else {
    $oWhere = $oWhere->like('colour', '%' . $colour . '%');
    $firstIteration = false;
  }
}
$oWhere = $oWhere->unnest();

//join next group of conditions with AND
$oWhere->and;

//build condition for sizes
$firstIteration = true;
$oWhere = $oWhere->nest();
foreach($choosenSizes as $size) {
  if(!$firstIteration) {
    $oWhere = $oWhere->or->like('size', '%' . $size. '%');
  } else {
    $oWhere = $oWhere->like('size', '%' . $size. '%');
    $firstIteration = false;
  }
}
$oWhere = $oWhere->unnest();

$oSelect->where($oWhere);

$resultSet = $this->selectWith($oSelect);

And that’s it. The main case is to call $oWhere->nest() on the begining of group which should be in brackets (which opens the bracket) and call $oWhere->unnest() at the end of group (which closes the bracket).