phpzend-frameworkzend-framework2zend-db

Zend Predicate or Predicatset


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

Solution

  • 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
    }