Consultez la FAQ sur le ZF avant de poster une question
Vous n'êtes pas identifié.
Bonjour,
j'ai besoin d'aide pour placer 2 parenthésés : ouvrir '(' et fermer ')'.
Bref en apparence rien de bien compliquer.
La requête en SQL :
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
En Zend :
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; }
Le résultat du code zend :
["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)"
Mon problème vient que "AND" doit être suivi de "(" et finir derrière le "where4" par '))'.
Bref si quelqu'un si connait dans les Predicate de Zend, un coup de main n'est pas de refus pour ce #@!.
Merci
Dernière modification par epcgroupe (15-02-2016 11:11:02)
Hors ligne
Sujet dupliqué et fermé à cette adresse. La discussion continue ici.
Hors ligne
Je n'utilises pas personnellement les prédicates de cette façon, mais voici du code qui marche issue d'un autre projet
[lang=php] $request = new Sql($this->dbAdapter); $select = $request->select(['p' => 'program']); $select->where( $select->where ->equalTo('active', true) ->and ->equalTo('initial', true) ->and ->nest ->isNull('age_min') ->or ->lessThanOrEqualTo('age_min', new Expression(':childAge')) ->unnest ->and ->nest ->isNull('age_max') ->or ->greaterThanOrEqualTo('age_max', new Expression(':childAge')) ->unnest );
J'imagine qu'on peut écrire ça dans les prédicates, mais vraiment tout réside dans le nested.
Hors ligne
Peut tu me donner l'équivalent en sql de ta requête pour mieux comprendre.
Hors ligne
J'ai fais cette soluction, elle fonctionne, mais je n'aime pas mon code car il est statique, si quelqu'un a un conseil pour le rendre dynamique je suis preneur :
[lang=php] 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); $compteur = 0; foreach ($valeur as $key => $value) { $compteur = $compteur + 1; $result[$compteur] = $value; } $select = $this->tableGateway->getSql()->select(); switch($compteur) { case '1': $predicate_set = new Predicate\PredicateSet(array(new Predicate\Expression('username = ?', $username)), Predicate\PredicateSet::COMBINED_BY_AND); $predicate_set->andPredicate(new Predicate\Like('shipment_number', '%'.$valeur.'%')); $select->where($predicate_set); $resultSet = $this->tableGateway->selectWith($select); break; case '2': $select->where->equalTo('username', $username) ->nest() ->like('shipment_number', ''.$result[1].'') ->or ->like('shipment_number', ''.$result[2].'') ->unnest(); break; case '3': $select->where->equalTo('username', $username) ->nest() ->like('shipment_number', ''.$result[1].'') ->or ->like('shipment_number', ''.$result[2].'') ->or ->like('shipment_number', ''.$result[3].'') ->unnest(); break; case '4': $select->where->equalTo('username', $username) ->nest() ->like('shipment_number', ''.$result[1].'') ->or ->like('shipment_number', ''.$result[2].'') ->or ->like('shipment_number', ''.$result[3].'') ->or ->like('shipment_number', ''.$result[4].'') ->unnest(); break; case '5': $select->where->equalTo('username', $username) ->nest() ->like('shipment_number', ''.$result[1].'') ->or ->like('shipment_number', ''.$result[2].'') ->or ->like('shipment_number', ''.$result[3].'') ->or ->like('shipment_number', ''.$result[4].'') ->or ->like('shipment_number', ''.$result[5].'') ->unnest(); break; case '6': $select->where->equalTo('username', $username) ->nest() ->like('shipment_number', ''.$result[1].'') ->or ->like('shipment_number', ''.$result[2].'') ->or ->like('shipment_number', ''.$result[3].'') ->or ->like('shipment_number', ''.$result[4].'') ->or ->like('shipment_number', ''.$result[5].'') ->or ->like('shipment_number', ''.$result[6].'') ->unnest(); break; case '7': $select->where->equalTo('username', $username) ->nest() ->like('shipment_number', ''.$result[1].'') ->or ->like('shipment_number', ''.$result[2].'') ->or ->like('shipment_number', ''.$result[3].'') ->or ->like('shipment_number', ''.$result[4].'') ->or ->like('shipment_number', ''.$result[5].'') ->or ->like('shipment_number', ''.$result[6].'') ->or ->like('shipment_number', ''.$result[7].'') ->unnest(); break; case '8': $select->where->equalTo('username', $username) ->nest() ->like('shipment_number', ''.$result[1].'') ->or ->like('shipment_number', ''.$result[2].'') ->or ->like('shipment_number', ''.$result[3].'') ->or ->like('shipment_number', ''.$result[4].'') ->or ->like('shipment_number', ''.$result[5].'') ->or ->like('shipment_number', ''.$result[6].'') ->or ->like('shipment_number', ''.$result[7].'') ->or ->like('shipment_number', ''.$result[8].'') ->unnest(); break; case '9': $select->where->equalTo('username', $username) ->nest() ->like('shipment_number', ''.$result[1].'') ->or ->like('shipment_number', ''.$result[2].'') ->or ->like('shipment_number', ''.$result[3].'') ->or ->like('shipment_number', ''.$result[4].'') ->or ->like('shipment_number', ''.$result[5].'') ->or ->like('shipment_number', ''.$result[6].'') ->or ->like('shipment_number', ''.$result[7].'') ->or ->like('shipment_number', ''.$result[8].'') ->or ->like('shipment_number', ''.$result[9].'') ->unnest(); break; } $resultSet = $this->tableGateway->selectWith($select); } } return $resultSet; }
Hors ligne