phpzend-framework

Zend 2 : Predicate and '(' & ')'


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 zend query :

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) {
               $predicate_set->orPredicate(new Predicate\Like('shipment_number', '%'.$value.'%'));
           }
        }
        else {
           $predicate_set->andPredicate(new Predicate\Like('shipment_number', '%'.$valeur.'%'));
        }
    }

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

    return $resultSet;
}

It lacks the parenthesis between the username and apès and the end of the where clause.

["queryString"] => string(181) "SELECT vw_my_asn_header FROM vw_my_asn_header WHERE (username = :where1 AND shipment_number LIKE :where2 OR shipment_number LIKE :where3 OR shipment_number LIKE :where4)"

How to add them ?


Solution

  • Try using methods nest() and unnest() around the loop :

           $predicate_set->nest();
           foreach ($valeur as $key => $value) {
               $predicate_set->orPredicate(new Predicate\Like('shipment_number', '%'.$value.'%'));
           }
           $predicate_set->unnest();