PHP Connection::createQueryBuilder方法代码示例

 public function count()
 {
     $qb = $this->dbal->createQueryBuilder();
     $qb->select('count(*) c')->from('(' . $this->qb->getSQL() . ')', 'xxx');
     $row = $this->dbal->fetchAssoc($qb->getSQL());
     return $this->count = intval($row['c']);
 }

 /**
  * Performs an authentication attempt
  *
  * @return \Zend\Authentication\Result
  * @throws \Zend\Authentication\Adapter\Exception\ExceptionInterface If authentication cannot be performed
  */
 public function authenticate()
 {
     $qb = $this->db->createQueryBuilder();
     if (strpos($this->options['credential_treatment'], '?') === false) {
         $this->options['credential_treatment'] = '?';
     }
     $expression = '(CASE WHEN ? = ' . $this->options['credential_treatment'] . ' THEN 1 ELSE 0 END) AS ?';
     $qb->createPositionalParameter($this->options['credential_column']);
     $qb->createPositionalParameter($this->credential);
     $qb->createPositionalParameter('zend_auth_credential_match');
     $qb->select(['*', $expression])->from($this->options['table_name'])->where($this->options['identity_column'] . ' = ' . $qb->createPositionalParameter($this->identity));
     //TODO check fetch type
     $resultIdentities = $qb->execute()->fetchAll();
     if (count($resultIdentities) < 1) {
         return new Result(Result::FAILURE_IDENTITY_NOT_FOUND, $this->identity, ['A record with the supplied identity could not be found.']);
     } else {
         if (count($resultIdentities) > 1) {
             return new Result(Result::FAILURE_IDENTITY_AMBIGUOUS, $this->identity, ['More than one record matches the supplied identity.']);
         }
     }
     $authResult = new Result(Result::FAILURE, $this->identity);
     foreach ($resultIdentities as $resultIdentity) {
         if ((int) $resultIdentity['zend_auth_credential_match'] !== 1) {
             $authResult = new Result(Result::FAILURE_CREDENTIAL_INVALID, $this->identity, ['Supplied credential is invalid.']);
         } else {
             unset($resultIdentity['zend_auth_credential_match']);
             $this->resultRow = $resultIdentity;
             $authResult = new Result(Result::SUCCESS, $this->identity, ['Authentication successful.']);
         }
         if ($authResult->isValid()) {
             break;
         }
     }
     return $authResult;
 }

 /**
  * Builds the raw query
  *
  * @return void
  */
 protected function buildQuery()
 {
     $this->queryBuilder = $this->connection->createQueryBuilder();
     $this->queryBuilder->select((array) $this->conf->select);
     /*
      * Main table
      */
     $this->queryBuilder->from($this->conf->from->name, $this->conf->from->alias);
     /*
      * Inner join, right join, left join
      */
     $joinTypes = ['innerJoin', 'leftJoin', 'rightJoin'];
     foreach ($joinTypes as $joinType) {
         if (isset($this->conf->{$joinType})) {
             $joins = $this->conf->{$joinType};
             $this->buildJoins($joinType, $joins);
         }
     }
     /*
      * Condition
      */
     if (isset($this->conf->where)) {
         foreach ($this->conf->where as $where) {
             $this->queryBuilder->andWhere($where);
         }
     }
 }

 /**
  * @inheritdoc
  */
 public function getList($products, Struct\ShopContextInterface $context)
 {
     $ids = [];
     foreach ($products as $product) {
         $ids[] = $product->getId();
     }
     $ids = array_unique($ids);
     $query = $this->connection->createQueryBuilder();
     $query->select(['relation.article_id']);
     $query->addSelect($this->fieldHelper->getRelatedProductStreamFields());
     $query->from('s_product_streams_articles', 'relation');
     $query->innerJoin('relation', 's_product_streams', 'stream', 'stream.id = relation.stream_id');
     $this->fieldHelper->addProductStreamTranslation($query, $context);
     $query->where('relation.article_id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_GROUP);
     $related = [];
     foreach ($data as $productId => $data) {
         $related[$productId] = [];
         foreach ($data as $row) {
             $related[$productId][] = $this->hydrator->hydrate($row);
         }
     }
     return $related;
 }

 public function fetchAll()
 {
     $stmt = $this->connection->createQueryBuilder()->select('uri, data')->from($this->table)->execute();
     foreach ($stmt as $item) {
         (yield $item['uri'] => $this->prepareRowForRead($item['uri'], $item['data']));
     }
 }

 /**
  * Sync the filesystem scan and the Bundles table
  * This is a 'dumb' scan - there is no state management here
  *      state management occurs in the module and theme management
  *      and is checked in Bundle/Bootstrap
  *
  * @param $array array of extensions
  *          obtained from filesystem scan
  *          key is bundle name and value an instance of \Zikula\Bundle\CoreBundle\Bundle\MetaData
  */
 private function sync($array)
 {
     // add what is in array but missing from db
     /** @var $metadata MetaData */
     foreach ($array as $name => $metadata) {
         $qb = $this->conn->createQueryBuilder();
         $qb->select('b.id', 'b.bundlename', 'b.bundleclass', 'b.autoload', 'b.bundletype', 'b.bundlestate')->from('bundles', 'b')->where('b.bundlename = :name')->setParameter('name', $name);
         $result = $qb->execute();
         $row = $result->fetch();
         if (!$row) {
             // bundle doesn't exist
             $this->insert($metadata);
         } elseif ($metadata->getClass() != $row['bundleclass'] || serialize($metadata->getAutoload()) != $row['autoload']) {
             // bundle json has been updated
             $updatedMeta = array("bundleclass" => $metadata->getClass(), "autoload" => serialize($metadata->getAutoload()));
             $this->conn->update('bundles', $updatedMeta, array('id' => $row['id']));
         }
     }
     // remove what is in db but missing from array
     $qb = $this->conn->createQueryBuilder();
     $qb->select('b.id', 'b.bundlename', 'b.bundleclass', 'b.autoload', 'b.bundletype', 'b.bundlestate')->from('bundles', 'b');
     $res = $qb->execute();
     foreach ($res->fetchAll() as $row) {
         if (!in_array($row['bundlename'], array_keys($array))) {
             $this->removeById($row['id']);
         }
     }
     // clear the cache
     /** @var $cacheClearer \Zikula\Bundle\CoreBundle\CacheClearer */
     $cacheClearer = \ServiceUtil::getManager()->get('zikula.cache_clearer');
     $cacheClearer->clear('symfony.config');
 }

 /**
  * @return string
  */
 private function getSQLForAllRows()
 {
     static $sql;
     if (!$sql) {
         $sql = $this->connection->createQueryBuilder()->select($this->getQuotedFields())->from('metadatas_structure', 's')->orderBy('sorter')->getSQL();
     }
     return $sql;
 }

 /**
  * @param $categoryId
  * @param null|int $limit
  * @return LastIdQuery
  */
 public function createCategoryQuery($categoryId, $limit = null)
 {
     $query = $this->connection->createQueryBuilder()->select(['categories.articleID', 'categories.articleID'])->from('s_articles_categories_ro', 'categories')->andWhere('categories.articleID > :lastId')->andWhere('categories.categoryID = :categoryId')->setParameter(':categoryId', $categoryId, \PDO::PARAM_INT)->setParameter(':lastId', 0, \PDO::PARAM_INT)->orderBy('categories.articleID');
     if ($limit !== null) {
         $query->setMaxResults($limit);
     }
     return new LastIdQuery($query);
 }

 /**
  * Save a snapshot
  *
  * @param Snapshot $snapshot
  * @return void
  */
 public function save(Snapshot $snapshot)
 {
     $table = $this->getTable($snapshot->aggregateType());
     $this->connection->insert($table, ['aggregate_type' => $snapshot->aggregateType()->toString(), 'aggregate_id' => $snapshot->aggregateId(), 'last_version' => $snapshot->lastVersion(), 'created_at' => $snapshot->createdAt()->format('Y-m-d\\TH:i:s.u'), 'aggregate_root' => serialize($snapshot->aggregateRoot())], ['string', 'string', 'integer', 'string', 'blob']);
     $queryBuilder = $this->connection->createQueryBuilder();
     $table = $this->getTable($snapshot->aggregateType());
     $queryBuilder->delete($table)->where('aggregate_type = :aggregate_type')->andWhere('aggregate_id = :aggregate_id')->andWhere('last_version < :last_version')->setParameter('aggregate_type', $snapshot->aggregateType()->toString())->setParameter('aggregate_id', $snapshot->aggregateId())->setParameter('last_version', $snapshot->lastVersion());
     $queryBuilder->execute();
 }

 /**
  * @param string $version
  * @return boolean
  */
 public function isApplied($version)
 {
     if ($this->isExistTable() == false) {
         return false;
     }
     /* @var $stmt Statement*/
     $stmt = $this->doctrine->createQueryBuilder()->select('version')->from(self::TABLE_NAME, 'M')->where('version = :version')->setParameter('version', $version)->execute();
     return !!$stmt->fetch();
 }

 /**
  * @param MaintenanceEvent $event
  */
 public function onDataCleanup(MaintenanceEvent $event)
 {
     $qb = $this->db->createQueryBuilder()->setParameter('date', $event->getDate()->format('Y-m-d H:i:s'));
     if ($event->isDryRun()) {
         $rows = $qb->select('count(*) as records')->from(MAUTIC_TABLE_PREFIX . 'leads', 'l')->where($qb->expr()->andX($qb->expr()->lte('l.last_active', ':date'), $qb->expr()->isNull('l.date_identified')))->execute()->fetchColumn();
     } else {
         $rows = $qb->delete(MAUTIC_TABLE_PREFIX . 'leads')->where($qb->expr()->andX($qb->expr()->lte('last_active', ':date'), $qb->expr()->isNull('date_identified')))->execute();
     }
     $event->setStat($this->translator->trans('mautic.maintenance.visitors'), $rows, $qb->getSQL(), $qb->getParameters());
 }

 /**
  * {@inheritdoc}
  */
 public function fetchOneById($id)
 {
     $qb = $this->db->createQueryBuilder();
     $qb->select('*')->from('album', 'a')->where('id = :id')->setParameter('id', $id);
     $result = $qb->execute()->fetch();
     if (!$result) {
         throw new \Exception('Row with id: ' . $id . ' not found.');
     }
     return $result;
 }

 /**
  * @param SKU $sku
  * @return Product
  * @throws ProductNotFoundException
  */
 public function getBySku(SKU $sku) : Product
 {
     $queryBuilder = $this->connection->createQueryBuilder();
     $queryBuilder->select('sku', 'is_in_stock', 'price_amount', 'price_currency', 'price_precision')->from('dumplie_inventory_product')->where('sku = :sku')->setParameter('sku', (string) $sku);
     $result = $this->connection->fetchAssoc($queryBuilder->getSQL(), $queryBuilder->getParameters());
     if (!$result) {
         throw ProductNotFoundException::bySku($sku);
     }
     return new Product(new SKU($result['sku']), new Price((int) $result['price_amount'], $result['price_currency'], (int) $result['price_precision']), (bool) $result['is_in_stock']);
 }

 /**
  * @param int $id
  * @return Album
  */
 public function fetchOne($id)
 {
     $qb = $this->db->createQueryBuilder();
     $qb->select('*')->from('album', 'a')->where('id = :id')->setParameter('id', $id);
     $result = $qb->execute()->fetch();
     if (!$result) {
         throw new \Exception("Could not find row {$id}");
     }
     return new Album($result);
 }

 /**
  * @param $isDryRun
  * @param $date
  *
  * @return int
  */
 private function cleanupData(MaintenanceEvent $event, $table)
 {
     $qb = $this->db->createQueryBuilder()->setParameter('date', $event->getDate()->format('Y-m-d H:i:s'));
     if ($event->isDryRun()) {
         $rows = (int) $qb->select('count(*) as records')->from(MAUTIC_TABLE_PREFIX . $table, 'log')->where($qb->expr()->lte('log.date_added', ':date'))->execute()->fetchColumn();
     } else {
         $rows = (int) $qb->delete(MAUTIC_TABLE_PREFIX . $table)->where($qb->expr()->lte('date_added', ':date'))->execute();
     }
     $event->setStat($this->translator->trans('mautic.maintenance.' . $table), $rows, $qb->getSQL(), $qb->getParameters());
 }