Zend Predicate or Predicatset

Question

I want to add parenthesis my request but I do not know how. Can you help me ? My SQL query :

SELECT * 
FROM vw_my_asn_header 
WHERE username = 'toto' 
  AND (shipment_number LIKE '20151106052811' OR 
       shipment_number LIKE '20151110053250' OR 
       shipment_number LIKE '20151116054359') 
ORDER BY message_id ASC

My fonction :

public function searchSitesDeliveries($username, Search $search)
{
     $select = $this->tableGateway->getSql()->select();
     array(new Predicate\Expression('username = ?', $username)),Predicate\PredicateSet::COMBINED_BY_AND);

    if (!empty($search->get_shipment_number()))
    {
      $valeur = $search->get_shipment_number();
      if(is_array($valeur)) 
      {
          $valeur = array_unique($valeur);
          foreach ($valeur as $key => $value) 
          {
              if($key == 0)
              {
                $predicate_set->andPredicate(new Predicate\Expression('shipment_number IN', '%'.$value.'%'));
                $predicate_set->nest();
                $bool = true;
              }
              else
              {
                  $predicate_set->orPredicate(new Predicate\Like('shipment_number', '%'.$value.'%'));     
              }
           }
           if($bool == true)   
           {
               $predicate_set->unnest();                             
           }
        }
        else {
           $predicate_set->andPredicate(new Predicate\Like('shipment_number', '%'.$valeur.'%'));
        }
    }

    $select->where($predicate_set);
    $resultSet = $this->tableGateway->selectWith($select);

    return $resultSet;
}

The $predicate_set->nest; brings back to me a next error :

`Call to undefined method Zend\Db\Sql\Predicate\PredicateSet::nest()`

Outside , at the beginning of the file , I :

namespace Front\Model;

use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;

use Zend\Db;
use Zend\Db\Sql;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Expression;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Metadata\Metadata;
use Zend\Db\Sql\Predicate;
use Zend\Db\Sql\Predicate\PredicateSet;
use Zend\Db\ResultSet\ResultSet;

use Front\Model\MAH;

What is the problem ?

Thank you

I have write a test:

public function searchSitesDeliveries($username, Search $search)
{
        if (!empty($search->get_shipment_number()))
        {
            $valeur = $search->get_shipment_number();
            if(is_array($valeur))
            {
                $toto = array();
                $valeur = array_unique($valeur);
                $select = $this->tableGateway->getSql()->select(); 
                $titi = "$" ;
                $compteur = 0;
                foreach ($valeur as $key => $value) 
                {                    
                    $toto[$compteur] = $value;
                    $compteur = $compteur + 1;
                }
                for ($i=0; $i < ($compteur); $i++) { 
                    \Zend\Debug\Debug::dump($toto[$i]);
                    if($i== 0 || $i == $compteur )
                    {
                        $titi = $titi . "select->where->equalTo('user_name', '".$username."')->next->like('shipment_number','".$toto[$i]."')";    
                    }
                    else
                    {                     
                        $titi = $titi . "->or->like('shipment_number','".$toto[$i]."')->unnest;";
                    }
                }   
                \Zend\Debug\Debug::dump($titi);             
                $resultSet = $this->tableGateway->selectWith(eval($titi));
            }
        }
        return $resultSet;
}

But :

string(173) "$select->where->equalTo('user_name', 'toto')->next->like('shipment_number','20151125056269')->or->like('shipment_number','20151014048501')->unnest;"

and :

Message error : Not nested

Answer 1

This is a dynamic solution:

use Zend\Db\Sql\Where;

public function searchSitesDeliveries($username, Search $search)
{
    if (!empty($search->get_shipment_number()))
    {
        $valeur = $search->get_shipment_number();
        if(is_array($valeur))
        {
            $result = array();
            $valeur = array_unique($valeur);
            $where = new Where();
            $where->equalTo('username', $username);
            if (!empty($valeur) {
                  $where->nest();
                  $or = false;
                foreach ($valeur as $value) 
                {   
                      if ($or) $where->or;
                    $where->like('shipment_number', $value)
                    $or = true;
                }
                $where->unnest();
            }
            $select = $this->tableGateway->getSql()->select();             
            $select->where($where);
            $resultSet = $this->tableGateway->selectWith($select);
            return $resultSet;
        }
        // error processing ... when $valeur isn't an array
    }
    // error processing ... when get_shipment_number is empty
}