PHP Query\QueryBuilder類代碼示例

 /**
  * @param QueryBuilder $queryBuilder
  * @param $callback
  * @param int|null $limit
  * @param int $maxAttempts
  * @throws DBALWalkerException
  * @throws DBALException
  */
 public function run(QueryBuilder $queryBuilder, $callback, $limit = null, $maxAttempts = 10)
 {
     if (!is_callable($callback)) {
         throw new DBALWalkerException('$callback is not callable');
     }
     if (!is_null($limit)) {
         $queryBuilder->setMaxResults($limit);
     } else {
         $limit = $queryBuilder->getMaxResults();
     }
     if (is_null($limit)) {
         $limit = PHP_INT_MAX;
     }
     $offset = 0;
     do {
         $rows = [];
         $errors = $maxAttempts;
         while (true) {
             try {
                 $rows = $queryBuilder->setFirstResult($offset)->execute()->fetchAll();
                 break;
             } catch (DBALException $e) {
                 if ($errors-- == 0) {
                     throw $e;
                 }
                 $queryBuilder->getConnection()->connect();
             }
         }
         call_user_func($callback, $rows, $offset, $limit);
         $offset += $limit;
     } while (count($rows) >= $limit);
 }

 /**
  * {@inheritdoc}
  */
 public function buildQuery(QueryBuilder $queryBuilder, array $values = array())
 {
     if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
         return;
     }
     $queryBuilder->andWhere($queryBuilder->expr()->in('response.companyType', $values[$this->getName()]));
 }

 /**
  * @param QueryBuilder  $qb
  * @param callable|null $countQueryBuilderModifier
  */
 public function __construct(QueryBuilder $qb, callable $countQueryBuilderModifier = null)
 {
     $this->qb = $qb;
     $this->countQueryBuilderModifier = $countQueryBuilderModifier ?: function (QueryBuilder $qb) {
         return $qb->select('COUNT(*)');
     };
 }

 /**
  * @param $tableName
  * @param $conditions
  *
  * @return array
  *
  * @throws LookupError
  *
  * @since 1.1.0
  *
  * @author Eddilbert Macharia (http://eddmash.com) <edd.cowan@gmail.com>
  */
 public static function filters(QueryBuilder $queryBuilder, $conditions)
 {
     // default lookup is equal
     $lookup = 'eq';
     // we add the or conditions afterwards to avoid them being mistaken for "and" conditions when they come first
     $or_combine = [];
     $and_combine = [];
     // create where clause from the conditions given
     foreach ($conditions as $condition) {
         foreach ($condition as $key => $value) {
             $column = self::getLookupColumn($key);
             $lookup = self::getLookUP($key);
             $value = self::prepareValue($value, $lookup);
             echo self::$lookuOptions[$lookup] . '<br>';
             echo $queryBuilder->createNamedParameter($value) . '<br>';
             echo $value . '<br>';
             $lookupCondition = sprintf(self::$lookuOptions[$lookup], $queryBuilder->createNamedParameter($value));
             $queryString = sprintf('%s %s', $column, $lookupCondition);
             if (self::combine($key) === self::$or) {
                 $queryBuilder->orWhere($queryString);
             } else {
                 $queryBuilder->andWhere($queryString);
             }
         }
     }
 }

 /**
  * @param QueryBuilder $builder
  */
 public static function logBuilder($builder)
 {
     $sql = $builder->getSQL();
     $params = $builder->getParameters();
     self::log("Running Query: " . $sql . " with params:");
     self::logObject($params);
 }

 /**
  * Applies operation to data source and returns modified data source.
  *
  * @param QueryBuilder $src
  * @param OperationInterface|SortOperation $operation
  * @return QueryBuilder
  */
 public function process($src, OperationInterface $operation)
 {
     $field = $operation->getField();
     $order = $operation->getOrder();
     $src->orderBy($field, $order);
     return $src;
 }

 /**
  * @param QueryBuilder $builder
  * @param $column
  * @param $value
  * @throws Exception
  * @return string
  */
 public function __invoke(QueryBuilder $builder, $column, $value)
 {
     if (!is_array($value)) {
         throw new Exception("Use of IN operator expects value to be array. Got " . gettype($value) . ".");
     }
     return $column . ' IN (' . $builder->createPositionalParameter($value, Connection::PARAM_STR_ARRAY) . ')';
 }

 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)
 {
     $paramcount = 0;
     if (!empty($this->gIDs)) {
         $validgids = array();
         foreach ($this->gIDs as $gID) {
             if ($gID > 0) {
                 $validgids[] = $gID;
             }
         }
         if (!empty($validgids)) {
             $query->innerJoin('p', 'VividStoreProductGroups', 'g', 'p.pID = g.pID and g.gID in (' . implode(',', $validgids) . ')');
             if (!$this->groupMatchAny) {
                 $query->having('count(g.gID) = ' . count($validgids));
             }
         }
     }
     switch ($this->sortBy) {
         case "alpha":
             $query->orderBy('pName', 'ASC');
             break;
         case "date":
             $query->orderBy('pDateAdded', 'DESC');
             break;
         case "popular":
             $pr = new StoreProductReport();
             $pr->sortByPopularity();
             $products = $pr->getProducts();
             $pIDs = array();
             foreach ($products as $product) {
                 $pIDs[] = $product['pID'];
             }
             foreach ($pIDs as $pID) {
                 $query->addOrderBy("pID = ?", 'DESC')->setParameter($paramcount++, $pID);
             }
             break;
     }
     switch ($this->featured) {
         case "featured":
             $query->andWhere("pFeatured = 1");
             break;
         case "nonfeatured":
             $query->andWhere("pFeatured = 0");
             break;
     }
     if (!$this->showOutOfStock) {
         $query->andWhere("pQty > 0 OR pQtyUnlim = 1");
     }
     if ($this->activeOnly) {
         $query->andWhere("pActive = 1");
     }
     if (is_array($this->cIDs) && !empty($this->cIDs)) {
         $query->innerJoin('p', 'VividStoreProductLocations', 'l', 'p.pID = l.pID and l.cID in (' . implode(',', $this->cIDs) . ')');
     }
     $query->groupBy('p.pID');
     if ($this->search) {
         $query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
     }
     return $query;
 }

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

 public function testSetQueryBuilder02()
 {
     $contain = $this->testConstruct02();
     $queryBuilder = new QueryBuilder($this->getConnection());
     $queryBuilder->select('*')->from('table', 't');
     $contain->setQueryBuilder($queryBuilder);
     return $contain;
 }

 /**
  * @param QueryBuilder  $queryBuilder
  * @param SortInterface $sort
  * @param Mapping       $mapping
  */
 public static function sort(QueryBuilder $queryBuilder, SortInterface $sort, Mapping $mapping)
 {
     $columns = $mapping->map();
     foreach ($sort->orders() as $propertyName => $order) {
         self::guardColumnExists($columns, $propertyName);
         $queryBuilder->orderBy($columns[$propertyName], $order->isAscending() ? Order::ASCENDING : Order::DESCENDING);
     }
 }

 /**
  * Add a query from a query builder
  *
  * @param QueryBuilder $qb
  * @return $this
  */
 public function addQueryFromBuilder(QueryBuilder $qb)
 {
     /**
      * @TODO : How to manage query params ?
      */
     $this->queries[] = new Query($qb->getSQL());
     return $this;
 }

 /**
  * @see \ComPHPPuebla\Doctrine\Query\Specification::match()
  */
 public function match(QueryBuilder $qb)
 {
     if ($this->has('page')) {
         $this->page = $this->get('page');
         $this->initPageSize();
         $qb->setFirstResult($this->calculateOffset())->setMaxResults($this->pageSize);
     }
 }

 public function testItShouldWorksWithParametersInTheOrX()
 {
     $qb = new QueryBuilder($this->connection);
     $qb->select('p.id')->from('posts', 'p')->setParameter('status', 'odd');
     $aclFilter = new DoctrineDbalAclFilter($qb);
     $aclFilter->setAclSchema($this->aclSchema);
     $aclFilter->apply('p', 'id', 'post-', ['user-1'], 1, ['status = :status']);
     $this->assertEquals(['status' => 'odd', 'acl_prefix' => 'post-', 'acl_identifiers' => ['user-1'], 'acl_mask' => 1], $qb->getParameters());
 }