PHP QueryBuilder::setParameter方法代码示例

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('alerts', $criteria)) {
         $ids = is_array($criteria['alerts']) ? $criteria['alerts'] : [$criteria['alerts']];
         $qb->join('x.alerts', 'al');
         $qb->andWhere($qb->expr()->in('al.id', ':alerts'));
         $qb->setParameter(':alerts', $ids);
     }
     //cleanup all the possible relationship filters
     unset($criteria['alerts']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("x.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('x.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  * @param QueryBuilder $qb
  * @param string $field
  * @param string $operator
  * @param string $value
  * @param int $i
  *
  * @throws WebServerLogException
  *
  * @return \Doctrine\ORM\Query\Expr\Andx|\Doctrine\ORM\Query\Expr\Comparison|\Doctrine\ORM\Query\Expr\Func
  */
 private function matchOperator(QueryBuilder $qb, $field, $operator, $value, $i = 0)
 {
     // TODO expose operators logic into methods, or use appropriate design pattern
     switch ($operator) {
         case 'eq':
         case 'gt':
         case 'lt':
             $placeholder = ':' . $this->fieldName . '_' . $i;
             $qb->setParameter($placeholder, $value);
             return $qb->expr()->{$operator}($field, $placeholder);
         case 'regex':
             $placeholder = ':regexp_' . $this->fieldName . '_' . $i;
             $qb->setParameter($placeholder, $value);
             return $qb->expr()->andX("REGEXP({$field}, {$placeholder}) = true");
         case 'like':
             $placeholder = ':like_' . $this->fieldName . '_' . $i;
             $qb->setParameter($placeholder, '%' . $value . '%');
             return $qb->expr()->like($field, $placeholder);
         case 'between':
             list($from, $to) = explode(',', $value);
             $qb->setParameter('from_' . $i, $from);
             $qb->setParameter('to_' . $i, $to);
             return $qb->expr()->between($field, ':from_' . $i, ':to_' . $i);
         default:
             throw WebServerLogException::unknownFilterOperator($operator);
     }
 }

 public function filters(QueryBuilder $qb, $key, $val)
 {
     switch ($key) {
         case 'history':
             if ($val) {
                 $orx = $qb->expr()->orX();
                 $orx->add('s.fk = :fk');
                 $orx->add('t.fk = :fk');
                 $qb->andWhere($orx);
                 $qb->setParameter('fk', intval($val));
             }
             break;
         case 'class':
             $orx = $qb->expr()->orX();
             $orx->add('s.class = :class');
             $orx->add('t.class = :class');
             $qb->andWhere($orx);
             $qb->setParameter('class', $val);
             break;
         case 'blamed':
             if ($val === 'null') {
                 $qb->andWhere($qb->expr()->isNull('a.blame'));
             } else {
                 // this allows us to safely ignore empty values
                 // otherwise if $qb is not changed, it would add where the string is empty statement.
                 $qb->andWhere($qb->expr()->eq('b.fk', ':blame'));
                 $qb->setParameter('blame', $val);
             }
             break;
         default:
             // if user attemps to filter by other fields, we restrict it
             throw new \Exception("filter not allowed");
     }
 }

 public function alterQueryBuilder(QueryBuilder $queryBuilder)
 {
     if ($this->year !== null) {
         $queryBuilder->andWhere('YEAR(transaction.date) = :year');
         $queryBuilder->setParameter(':year', $this->year);
     }
     if ($this->month && in_array($this->month, array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))) {
         $queryBuilder->andWhere('MONTH(transaction.date) = :month');
         $queryBuilder->setParameter(':month', $this->month);
     }
     if ($this->processed != null && in_array($this->processed, array(0, 1))) {
         $queryBuilder->andWhere('transaction.is_processed = :processed');
         $queryBuilder->setParameter(':processed', $this->processed ? true : false);
     }
     if ($this->tag != null) {
         $queryBuilder->leftJoin('transaction.tags', 'tag');
         $queryBuilder->andWhere('tag.slug = :tag');
         $queryBuilder->setParameter(':tag', $this->tag);
     }
     if ($this->category !== null) {
         if ($this->category == 'empty') {
             $queryBuilder->andWhere('transaction.category IS NULL');
         } else {
             $queryBuilder->leftJoin('transaction.category', 'category');
             $queryBuilder->leftJoin('category.parent', 'parent');
             $queryBuilder->andWhere('category.slug = :category or parent.slug = :category');
             $queryBuilder->setParameter(':category', $this->category);
         }
     }
     return $queryBuilder;
 }

 /**
  * @param FilterInterface $filter
  * @param FormInterface $form
  * @param QueryBuilder $qb
  * @param string $alias
  */
 public function handleForm(FilterInterface $filter, FormInterface $form, QueryBuilder $qb, $alias)
 {
     $data = $form->getData();
     if (!$data) {
         return;
     }
     $columns = $filter->getFullAttributeReferences($alias);
     if (!empty($data[DateRangeType::START_NAME])) {
         $startDate = $data[DateRangeType::START_NAME];
         $dql = [];
         foreach ($columns as $column) {
             $uid = uniqid('fromDate');
             $dql[] = "{$column} >= :{$uid}";
             $qb->setParameter($uid, $startDate);
         }
         $qb->andWhere(implode(' OR ', $dql));
     }
     if (!empty($data[DateRangeType::END_NAME])) {
         $endDate = $data[DateRangeType::END_NAME];
         $dql = [];
         foreach ($columns as $column) {
             $uid = uniqid('endDate');
             $dql[] = "{$column} <= :{$uid}";
             $qb->setParameter($uid, $endDate);
         }
         $qb->andWhere(implode(' OR ', $dql));
     }
 }

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('courses', $criteria)) {
         $ids = is_array($criteria['courses']) ? $criteria['courses'] : [$criteria['courses']];
         $qb->join('p.programYears', 'c_programYear');
         $qb->join('c_programYear.cohort', 'c_cohort');
         $qb->join('c_cohort.courses', 'c_course');
         $qb->andWhere($qb->expr()->in('c_course.id', ':courses'));
         $qb->setParameter(':courses', $ids);
     }
     if (array_key_exists('sessions', $criteria)) {
         $ids = is_array($criteria['sessions']) ? $criteria['sessions'] : [$criteria['sessions']];
         $qb->join('p.programYears', 'se_programYear');
         $qb->join('se_programYear.cohort', 'se_cohort');
         $qb->join('se_cohort.courses', 'se_course');
         $qb->join('se_course.sessions', 'se_session');
         $qb->andWhere($qb->expr()->in('se_session.id', ':sessions'));
         $qb->setParameter(':sessions', $ids);
     }
     if (array_key_exists('terms', $criteria)) {
         $ids = is_array($criteria['terms']) ? $criteria['terms'] : [$criteria['terms']];
         $qb->join('p.programYears', 't_programYear');
         $qb->join('t_programYear.terms', 't_term');
         $qb->andWhere($qb->expr()->in('t_term.id', ':terms'));
         $qb->setParameter(':terms', $ids);
     }
     if (array_key_exists('schools', $criteria)) {
         $ids = is_array($criteria['schools']) ? $criteria['schools'] : [$criteria['schools']];
         $qb->join('p.school', 'sc_school');
         $qb->andWhere($qb->expr()->in('sc_school.id', ':schools'));
         $qb->setParameter(':schools', $ids);
     }
     unset($criteria['schools']);
     unset($criteria['courses']);
     unset($criteria['sessions']);
     unset($criteria['terms']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("p.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('p.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  *  Returns a statement for an expression.
  *
  * @param QueryBuilder $queryBuilder
  *
  * @return string
  */
 public function getStatement(QueryBuilder $queryBuilder)
 {
     $paramName1 = $this->getFieldName() . $this->getUniqueId();
     $paramName2 = $this->getFieldName() . $this->getUniqueId();
     $queryBuilder->setParameter($paramName1, $this->getStart());
     $queryBuilder->setParameter($paramName2, $this->getEnd());
     return $this->field->getSelect() . ' BETWEEN :' . $paramName1 . ' AND :' . $paramName2;
 }

 private function whereToPublish(QueryBuilder $builder)
 {
     $builder->where('r.published = :published');
     $builder->setParameter('published', false);
     $builder->orWhere('p.modified = :modified');
     $builder->setParameter('modified', true);
     return $this;
 }

 /**
  * 
  * @param \Doctrine\ORM\QueryBuilder $qb
  * @return \Doctrine\ORM\QueryBuilder
  * 
  * filtre les résultats pour l'année en cours
  */
 public function whereAnneeEnCours(\Doctrine\ORM\QueryBuilder $qb)
 {
     $debut = new \DateTime(date('Y') . '-01-01');
     $fin = new \DateTime(date('Y') . '-12-31');
     $qb->andWhere('a.date BETWEEN :debut AND :fin');
     $qb->setParameter('debut', $debut);
     $qb->setParameter('fin', $fin);
     return $qb;
 }

 /**
  * Fonction applicant les filtres de type text
  * à notre querybuilder
  *
  * @param object $qb -- Doctrine\ORM\QueryBuilder
  * @param string $tableAlias -- l'alias de la table
  * @param string $field -- le champ cible
  * @param mixed $value -- la valeur a testé
  */
 public function matchFilter(QueryBuilder $qb, $tableAlias, $field, $operator, $value)
 {
     $qb->andWhere($tableAlias . '.' . $field . ' ' . $operator . ' :' . $tableAlias . '_' . $field);
     switch ($operator) {
         case 'LIKE':
             $qb->setParameter($tableAlias . '_' . $field, '%' . $value . '%');
             break;
         case '=':
             $qb->setParameter($tableAlias . '_' . $field, $value);
     }
 }

 public function buildQuery(\Doctrine\ORM\QueryBuilder $qb, $field, $parameter, $value)
 {
     if (null !== $value) {
         if (true === $this->options['multiple']) {
             $qb->andWhere($field . ' in (' . $parameter . ')');
             $qb->setParameter($parameter, explode(',', $value));
         } else {
             $qb->andWhere($field . ' = ' . $parameter);
             $qb->setParameter($parameter, $value);
         }
     }
 }

 /**
  * {@inheritdoc}
  */
 public function addAndExpression(Andx $andExpr, QueryBuilder $pivot, $searchField, $searchValue, &$i)
 {
     switch ($this->getSearchType()) {
         case 'like':
             $andExpr->add($pivot->expr()->like($searchField, '?' . $i));
             $pivot->setParameter($i, '%' . $searchValue . '%');
             break;
         case 'notLike':
             $andExpr->add($pivot->expr()->notLike($searchField, '?' . $i));
             $pivot->setParameter($i, '%' . $searchValue . '%');
             break;
         case 'eq':
             $andExpr->add($pivot->expr()->eq($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'neq':
             $andExpr->add($pivot->expr()->neq($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'lt':
             $andExpr->add($pivot->expr()->lt($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'lte':
             $andExpr->add($pivot->expr()->lte($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'gt':
             $andExpr->add($pivot->expr()->gt($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'gte':
             $andExpr->add($pivot->expr()->gte($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'in':
             $andExpr->add($pivot->expr()->in($searchField, '?' . $i));
             $pivot->setParameter($i, explode(',', $searchValue));
             break;
         case 'notIn':
             $andExpr->add($pivot->expr()->notIn($searchField, '?' . $i));
             $pivot->setParameter($i, explode(",", $searchValue));
             break;
         case 'isNull':
             $andExpr->add($pivot->expr()->isNull($searchField));
             break;
         case 'isNotNull':
             $andExpr->add($pivot->expr()->isNotNull($searchField));
             break;
     }
     $i++;
     return $andExpr;
 }

 /**
  * {@inheritdoc}
  */
 public function getStatement(QueryBuilder $queryBuilder)
 {
     $paramName = $this->getFieldName() . $this->getUniqueId();
     if ($this->getValue() === null) {
         return $this->field->getSelect() . ' ' . $this->convertNullComparator($this->getComparator());
     } elseif ($this->getComparator() === 'LIKE') {
         $queryBuilder->setParameter($paramName, '%' . $this->getValue() . '%');
     } else {
         $queryBuilder->setParameter($paramName, $this->getValue());
     }
     return $this->field->getSelect() . ' ' . $this->getComparator() . ' :' . $paramName;
 }

 /**
  * @param $filters
  * @throws \Exception
  */
 public function process(QueryBuilder $qb, Filters $filters)
 {
     $this->qb = $qb;
     foreach ($filters->toArray() as $key => $data) {
         if (array_key_exists($key, $this->accepted)) {
             $this->processFilter($key, $data);
         }
     }
     foreach ($this->getParams() as $key => $value) {
         $this->qb->setParameter($key, $value);
     }
     return $this->qb;
 }

 /**
  * @param QueryBuilder $queryBuilder
  */
 public function configureResultQueryBuilder(QueryBuilder $queryBuilder)
 {
     $title = $this->getTitle();
     if ($title !== null) {
         $queryBuilder->andWhere('entity.title LIKE :like_title');
         $queryBuilder->setParameter('like_title', '%' . $title . '%');
     }
     $createdAfter = $this->getCreatedAfter();
     if ($createdAfter !== null) {
         $queryBuilder->andWhere('entity.created >= :created_after');
         $queryBuilder->setParameter('created_after', $createdAfter);
     }
 }