PHP QueryBuilder::setParameter方法代码示例

 /**
  * @return array
  */
 public function fetch()
 {
     $data = $this->query->execute()->fetchAll(\PDO::FETCH_KEY_PAIR);
     $keys = array_keys($data);
     $this->query->setParameter(':lastId', array_pop($keys));
     return array_values($data);
 }

 /**
  * {@inheritdoc}
  */
 public function apply($fromAlias, $fromIdentifier, $resourcePrefix, array $requesterIdentifiers, $mask, array $orX = [])
 {
     $this->queryBuilder->leftJoin($fromAlias, $this->getAclSchema()->getPermissionsTableName(), 'acl_p', 'acl_p.resource = ' . $this->connection->getDatabasePlatform()->getConcatExpression(':acl_prefix', $fromAlias . '.' . $fromIdentifier));
     $orX[] = 'acl_p.requester IN (:acl_identifiers) AND :acl_mask = (acl_p.mask & :acl_mask)';
     $this->queryBuilder->andWhere(implode(' OR ', $orX));
     $this->queryBuilder->setParameter('acl_prefix', $resourcePrefix, \PDO::PARAM_STR)->setParameter('acl_identifiers', $requesterIdentifiers, Connection::PARAM_STR_ARRAY)->setParameter('acl_mask', $mask, \PDO::PARAM_INT);
     return $this->queryBuilder;
 }

 /**
  * paramTypes is private, we need to redefine setParameter
  * to access it
  *
  * @param string $key
  * @param string $value
  * @param string $type
  *
  * @return $this
  */
 public function setParameter($key, $value, $type = null)
 {
     if ($type !== null) {
         $this->paramTypes[$key] = $type;
     }
     return parent::setParameter($key, $value, $type);
 }

 public function match(QueryBuilder $qb)
 {
     if ($this->has('username')) {
         $qb->andWhere('u.username = :username');
         $qb->setParameter('username', $this->get('username'));
     }
 }

 public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
 {
     if (!$this->includeAllGroups) {
         $query->andWhere('g.gID > :minGroupID');
         $query->setParameter('minGroupID', REGISTERED_GROUP_ID);
     }
     return $query;
 }

 /**
  * Applies operation to data source and returns modified data source.
  *
  * @param QueryBuilder $src
  * @param OperationInterface|FilterOperation $operation
  * @return QueryBuilder
  */
 public function process($src, OperationInterface $operation)
 {
     $value = $operation->getValue();
     $operator = $operation->getOperator();
     $fieldName = $operation->getField();
     $parameterName = 'p' . md5($fieldName . $operator);
     $src->andWhere("{$fieldName} {$operator} :{$parameterName}");
     $src->setParameter($parameterName, $value);
     return $src;
 }

    /**
     * @see \ComPHPPuebla\Doctrine\TableGateway\Specification\QueryBuilderSpecification::match()
     */
    public function match(QueryBuilder $qb)
    {
        if ($this->has('latitude') && $this->has('longitude')) {
            $qb->addSelect(<<<SELECT
    (6371
    * (2 * ATAN(SQRT(SIN(((:latitude - s.latitude) * (PI()/180))/2) * SIN(((:latitude - s.latitude) * (PI()/180))/2)
    + COS(:latitude * (PI()/180)) * COS(s.latitude * (PI()/180))
    * SIN(((:longitude - s.longitude) * (PI()/180))/2) * SIN(((:longitude - s.longitude) * (PI()/180))/2)),
            SQRT(1-(sin(((:latitude - s.latitude) * (PI()/180))/2) * SIN(((:latitude - s.latitude) * (PI()/180))/2)
    + COS(:latitude * (PI()/180) * COS(s.latitude * (PI()/180)
    * SIN(((:longitude - s.longitude) * (PI()/180))/2) * SIN(((:longitude - s.longitude) * (PI()/180))/2))))))))
    AS distance
SELECT
);
            $qb->orderBy('distance');
            $qb->setParameter('latitude', $this->get('latitude'));
            $qb->setParameter('longitude', $this->get('longitude'));
        }
    }

 public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
 {
     if (!$this->includeInactiveUsers) {
         $query->andWhere('u.uIsActive = :uIsActive');
         $query->setParameter('uIsActive', true);
     }
     if (!$this->includeUnvalidatedUsers) {
         $query->andWhere('u.uIsValidated != 0');
     }
     return $query;
 }

 /**
  * Sets the values and parameters of the upcoming given query according
  * to the entity.
  *
  * @param Entity $entity
  * the entity with its fields and values
  * @param QueryBuilder $queryBuilder
  * the upcoming query
  * @param string $setMethod
  * what method to use on the QueryBuilder: 'setValue' or 'set'
  */
 protected function setValuesAndParameters(Entity $entity, QueryBuilder $queryBuilder, $setMethod)
 {
     $formFields = $this->getFormFields();
     $count = count($formFields);
     for ($i = 0; $i < $count; ++$i) {
         $type = $this->definition->getType($formFields[$i]);
         $value = $entity->get($formFields[$i]);
         if ($type == 'boolean') {
             $value = $value ? 1 : 0;
         }
         $queryBuilder->{$setMethod}('`' . $formFields[$i] . '`', '?');
         $queryBuilder->setParameter($i, $value);
     }
 }

 /**
  * {@inheritdoc}
  */
 public function buildQuery(QueryBuilder $queryBuilder, array $values = array())
 {
     if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
         return;
     }
     $codes = $values[$this->getName()];
     if (in_array('all_but_paris', $codes)) {
         $departements = new Departments();
         $regions = new Regions();
         unset($codes[array_search('all_but_paris', $codes)]);
         $codes = array_merge($codes, array_diff(array_keys($departements->getArrayCopy()), $regions->get(self::ALL_BUT_PARIS_REGION_CODE)->getCodesDepartements()));
     }
     if (count($codes)) {
         $queryBuilder->setParameter('department', $codes, Connection::PARAM_STR_ARRAY)->andWhere('response.companyDepartment IN(:department)');
     }
 }

 /**
  * Sets the values and parameters of the upcoming given query according
  * to the entity.
  *
  * @param CRUDEntity $entity
  * the entity with its fields and values
  * @param QueryBuilder $queryBuilder
  * the upcoming query
  * @param boolean $setValue
  * whether to use QueryBuilder::setValue (true) or QueryBuilder::set (false)
  */
 protected function setValuesAndParameters(CRUDEntity $entity, QueryBuilder $queryBuilder, $setValue)
 {
     $formFields = $this->definition->getEditableFieldNames();
     $count = count($formFields);
     for ($i = 0; $i < $count; ++$i) {
         $value = $entity->get($formFields[$i]);
         $type = $this->definition->getType($formFields[$i]);
         if ($type == 'bool') {
             $value = $value ? 1 : 0;
         }
         if ($type == 'date' || $type == 'datetime' || $type == 'reference') {
             $value = $value == '' ? null : $value;
         }
         if ($setValue) {
             $queryBuilder->setValue('`' . $formFields[$i] . '`', '?');
         } else {
             $queryBuilder->set('`' . $formFields[$i] . '`', '?');
         }
         $queryBuilder->setParameter($i, $value);
     }
 }

 /**
  * @param array        $filters
  * @param QueryBuilder $queryBuilder
  * @param array        $filterDefinitions
  *
  * @return bool
  */
 private function applyFilters(array $filters, QueryBuilder $queryBuilder, array $filterDefinitions)
 {
     $expr = $queryBuilder->expr();
     $filterExpr = $expr->andX();
     if (count($filters)) {
         foreach ($filters as $filter) {
             $exprFunction = isset($filter['expr']) ? $filter['expr'] : $filter['condition'];
             $paramName = InputHelper::alphanum($filter['column']);
             switch ($exprFunction) {
                 case 'notEmpty':
                     $filterExpr->add($expr->isNotNull($filter['column']));
                     $filterExpr->add($expr->neq($filter['column'], $expr->literal('')));
                     break;
                 case 'empty':
                     $filterExpr->add($expr->isNull($filter['column']));
                     $filterExpr->add($expr->eq($filter['column'], $expr->literal('')));
                     break;
                 default:
                     if (trim($filter['value']) == '') {
                         // Ignore empty
                         break;
                     }
                     $columnValue = ":{$paramName}";
                     switch ($filterDefinitions[$filter['column']]['type']) {
                         case 'bool':
                         case 'boolean':
                             if ((int) $filter['value'] > 1) {
                                 // Ignore the "reset" value of "2"
                                 break;
                             }
                             $queryBuilder->setParameter($paramName, $filter['value'], 'boolean');
                             break;
                         case 'float':
                             $columnValue = (double) $filter['value'];
                             break;
                         case 'int':
                         case 'integer':
                             $columnValue = (int) $filter['value'];
                             break;
                         default:
                             $queryBuilder->setParameter($paramName, $filter['value']);
                     }
                     $filterExpr->add($expr->{$exprFunction}($filter['column'], $columnValue));
             }
         }
     }
     if ($filterExpr->count()) {
         $queryBuilder->andWhere($filterExpr);
         return true;
     }
     return false;
 }

 /**
  * @param QueryBuilder $query
  * @return QueryBuilder
  */
 protected function prepareConditions($query)
 {
     $i = 0;
     if (count($this->getConditions()) !== 0) {
         foreach ($this->getConditions() as $condition) {
             $conditionKey = 'NNXGridCondition_' . $i;
             $query->andWhere($condition->getKey() . ' ' . $condition->getCriteria() . ' :' . $conditionKey);
             $query->setParameter($conditionKey, $condition->getValue());
             $i++;
         }
     }
     return $query;
 }

 /**
  * {@inheritdoc}
  */
 public function notEquals($field, $value)
 {
     $this->queryBuilder->setParameter($field, $value);
     return $this->queryBuilder->expr()->neq($field, ':' . $field);
 }

 /**
  * Modify the query to count how many rows is between a range of date diff in seconds
  *
  * @param  QueryBuilder $query
  * @param  string       $dateColumn1
  * @param  string       $dateColumn2
  * @param  integer      $startSecond
  * @param  integer      $endSecond
  * @param  array        $filters will be added to where claues
  * @param  string       $tablePrefix
  */
 public function modifyCountDateDiffQuery(QueryBuilder &$query, $dateColumn1, $dateColumn2, $startSecond = 0, $endSecond = 60, $tablePrefix = 't')
 {
     $query->select('COUNT(' . $tablePrefix . '.' . $dateColumn1 . ') AS count');
     $query->where('TIMESTAMPDIFF(SECOND, ' . $tablePrefix . '.' . $dateColumn1 . ', ' . $tablePrefix . '.' . $dateColumn2 . ') >= :startSecond');
     $query->andWhere('TIMESTAMPDIFF(SECOND, ' . $tablePrefix . '.' . $dateColumn1 . ', ' . $tablePrefix . '.' . $dateColumn2 . ') < :endSecond');
     $query->setParameter('startSecond', $startSecond);
     $query->setParameter('endSecond', $endSecond);
 }