PHP QueryBuilder::addSelect方法代码示例

 /**
  * @test
  */
 public function addSelectDoesNotQuoteStarPlaceholder()
 {
     $this->connection->quoteIdentifier('aField')->shouldBeCalled()->willReturnArgument(0);
     $this->connection->quoteIdentifier('*')->shouldNotBeCalled();
     $this->concreteQueryBuilder->addSelect(Argument::exact('aField'), Argument::exact('*'))->shouldBeCalled()->willReturn($this->subject);
     $this->subject->addSelect('aField', '*');
 }

 /**
  * @inheritdoc
  */
 public function load(QueryBuilder $query, ClassMetadata $metadata)
 {
     $field = $this->mapping['fieldname'];
     $target = $this->mapping['target'];
     $boltname = $metadata->getBoltName();
     $query->addSelect($this->getPlatformGroupConcat("{$field}.to_id", $field, $query))->leftJoin('content', $target, $field, "content.id = {$field}.from_id AND {$field}.from_contenttype='{$boltname}' AND {$field}.to_contenttype='{$field}'")->addGroupBy("content.id");
 }

 /**
  * For the taxonomy field the load event modifies the query to fetch taxonomies related
  * to a content record from the join table.
  *
  * It does this via an additional ->addSelect() and ->leftJoin() call on the QueryBuilder
  * which includes then includes the taxonomies in the same query as the content fetch.
  *
  * @param QueryBuilder  $query
  * @param ClassMetadata $metadata
  */
 public function load(QueryBuilder $query, ClassMetadata $metadata)
 {
     $field = $this->mapping['fieldname'];
     $target = $this->mapping['target'];
     $boltname = $metadata->getBoltName();
     if ($this->mapping['data']['has_sortorder']) {
         $order = "{$field}.sortorder";
         $query->addSelect("{$field}.sortorder as " . $field . '_sortorder');
     } else {
         $order = "{$field}.id";
     }
     $from = $query->getQueryPart('from');
     if (isset($from[0]['alias'])) {
         $alias = $from[0]['alias'];
     } else {
         $alias = $from[0]['table'];
     }
     $query->addSelect($this->getPlatformGroupConcat("{$field}.slug", $order, $field . '_slugs', $query))->addSelect($this->getPlatformGroupConcat("{$field}.name", $order, $field, $query))->leftJoin($alias, $target, $field, "{$alias}.id = {$field}.content_id AND {$field}.contenttype='{$boltname}' AND {$field}.taxonomytype='{$field}'")->addGroupBy("{$alias}.id");
 }

 /**
  * @inheritdoc
  */
 public function load(QueryBuilder $query, ClassMetadata $metadata)
 {
     $field = $this->mapping['fieldname'];
     $boltname = $metadata->getBoltName();
     if ($this->mapping['data']['has_sortorder']) {
         $order = "{$field}.sortorder";
     } else {
         $order = "{$field}.id";
     }
     $query->addSelect($this->getPlatformGroupConcat("{$field}.slug", $order, $field, $query))->leftJoin('content', 'bolt_taxonomy', $field, "content.id = {$field}.content_id AND {$field}.contenttype='{$boltname}' AND {$field}.taxonomytype='{$field}'")->addGroupBy("content.id");
 }

 /**
  * For repeating fields, the load method adds extra joins and selects to the query that
  * fetches the related records from the field and field value tables in the same query as the content fetch.
  *
  * @param QueryBuilder  $query
  * @param ClassMetadata $metadata
  *
  * @return void
  */
 public function load(QueryBuilder $query, ClassMetadata $metadata)
 {
     $field = $this->mapping['fieldname'];
     $boltname = $metadata->getBoltName();
     $from = $query->getQueryPart('from');
     if (isset($from[0]['alias'])) {
         $alias = $from[0]['alias'];
     } else {
         $alias = $from[0]['table'];
     }
     $query->addSelect($this->getPlatformGroupConcat('fields', $query))->leftJoin($alias, $this->mapping['tables']['field_value'], 'f', "f.content_id = {$alias}.id AND f.contenttype='{$boltname}' AND f.name='{$field}'");
 }

 /**
  * For relations, the load method adds an extra ->addSelect() and ->leftJoin() to the query that
  * fetches the related records from the join table in the same query as the content fetch.
  *
  * IDs are returned comma-separated which the ->hydrate() method can then turn into pointers
  * to the related entities.
  *
  * @param QueryBuilder  $query
  * @param ClassMetadata $metadata
  */
 public function load(QueryBuilder $query, ClassMetadata $metadata)
 {
     $field = $this->mapping['fieldname'];
     $target = $this->mapping['target'];
     $boltname = $metadata->getBoltName();
     $from = $query->getQueryPart('from');
     if (isset($from[0]['alias'])) {
         $alias = $from[0]['alias'];
     } else {
         $alias = $from[0]['table'];
     }
     $query->addSelect($this->getPlatformGroupConcat("{$field}.to_id", $field, $query))->leftJoin($alias, $target, $field, "{$alias}.id = {$field}.from_id AND {$field}.from_contenttype='{$boltname}' AND {$field}.to_contenttype='{$field}'")->addGroupBy("{$alias}.id");
 }

    /**
     * @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->includeAliases) {
         $query->from('Pages', 'p')->leftJoin('p', 'Pages', 'pa', 'p.cPointerID = pa.cID')->leftJoin('p', 'PagePaths', 'pp', 'p.cID = pp.cID and pp.ppIsCanonical = true')->leftJoin('pa', 'PageSearchIndex', 'psi', 'psi.cID = if(pa.cID is null, p.cID, pa.cID)')->leftJoin('p', 'PageTypes', 'pt', 'pt.ptID = if(pa.cID is null, p.ptID, pa.ptID)')->leftJoin('p', 'CollectionSearchIndexAttributes', 'csi', 'csi.cID = if(pa.cID is null, p.cID, pa.cID)')->innerJoin('p', 'CollectionVersions', 'cv', 'cv.cID = if(pa.cID is null, p.cID, pa.cID)')->innerJoin('p', 'Collections', 'c', 'p.cID = c.cID')->andWhere('p.cIsTemplate = 0 or pa.cIsTemplate = 0');
     } else {
         $query->from('Pages', 'p')->leftJoin('p', 'PagePaths', 'pp', '(p.cID = pp.cID and pp.ppIsCanonical = true)')->leftJoin('p', 'PageSearchIndex', 'psi', 'p.cID = psi.cID')->leftJoin('p', 'PageTypes', 'pt', 'p.ptID = pt.ptID')->leftJoin('c', 'CollectionSearchIndexAttributes', 'csi', 'c.cID = csi.cID')->innerJoin('p', 'Collections', 'c', 'p.cID = c.cID')->innerJoin('p', 'CollectionVersions', 'cv', 'p.cID = cv.cID')->andWhere('p.cPointerID < 1')->andWhere('p.cIsTemplate = 0');
     }
     switch ($this->pageVersionToRetrieve) {
         case self::PAGE_VERSION_RECENT:
             $query->andWhere('cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)');
             break;
         case self::PAGE_VERSION_RECENT_UNAPPROVED:
             $query->andWhere('cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)')->andWhere('cvIsApproved = 0');
             break;
         case self::PAGE_VERSION_ACTIVE:
         default:
             $query->andWhere('cvIsApproved = 1');
             break;
     }
     if ($this->isFulltextSearch) {
         $query->addSelect('match(psi.cName, psi.cDescription, psi.content) against (:fulltext) as cIndexScore');
     }
     if (!$this->includeInactivePages) {
         $query->andWhere('p.cIsActive = :cIsActive');
         $query->setParameter('cIsActive', true);
     }
     if (is_object($this->siteTree)) {
         $tree = $this->siteTree;
     } else {
         $site = \Core::make("site")->getSite();
         $tree = $site->getSiteTreeObject();
     }
     // Note, we might not use this. We have to set the parameter even if we don't use it because
     // StackList (which extends PageList) needs to have it available.
     $query->setParameter('siteTreeID', $tree->getSiteTreeID());
     if ($this->query->getParameter('cParentID') < 1) {
         if (!$this->includeSystemPages) {
             $query->andWhere('p.siteTreeID = :siteTreeID');
             $query->andWhere('p.cIsSystemPage = :cIsSystemPage');
             $query->setParameter('cIsSystemPage', false);
         } else {
             $query->andWhere('(p.siteTreeID = :siteTreeID or p.siteTreeID = 0)');
         }
     }
     return $query;
 }

 public function testEmptyAddSelect()
 {
     $qb = new QueryBuilder($this->conn);
     $qb2 = $qb->addSelect();
     $this->assertSame($qb, $qb2);
     $this->assertEquals(QueryBuilder::SELECT, $qb->getType());
 }

 /**
  * Adds an item that is to be returned in the query result.
  *
  * <code>
  *     $qb = $conn->getQueryBuilder()
  *         ->select('u.id')
  *         ->addSelect('p.id')
  *         ->from('users', 'u')
  *         ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');
  * </code>
  *
  * @param mixed $select The selection expression.
  *
  * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
  */
 public function addSelect($select = null)
 {
     $selects = is_array($select) ? $select : func_get_args();
     $this->queryBuilder->addSelect($this->helper->quoteColumnNames($selects));
     return $this;
 }

 /**
  * @param QueryBuilder $query
  * @param string $suffix
  */
 private function addProductStreamTranslationWithSuffix(QueryBuilder $query, $suffix = '')
 {
     $selectSuffix = !empty($suffix) ? '_' . strtolower($suffix) : '';
     $query->leftJoin('stream', 's_core_translations', 'streamTranslation' . $suffix, 'streamTranslation' . $suffix . '.objecttype = :streamType AND
          streamTranslation' . $suffix . '.objectkey = stream.id AND
          streamTranslation' . $suffix . '.objectlanguage = :language' . $suffix);
     $query->addSelect(['streamTranslation' . $suffix . '.objectdata as __stream_translation' . $selectSuffix])->setParameter(':streamType', 'productStream');
 }

 /**
  * Load the requested resources into RBAC.
  *
  * @param Rbac $rbac
  * @param string $role
  * @param string|null $permission
  * @return \Doctrine\DBAL\Query\QueryBuilder
  */
 protected function load($rbac, $role, $permission = null)
 {
     $options = $this->options;
     $builder = new QueryBuilder($this->connection);
     // Role always present
     $builder->select('node.name')->from($options->getRoleTable(), 'node')->from($options->getRoleTable(), 'parent')->where('node.lft BETWEEN parent.lft AND parent.rgt')->andWhere('parent.name = :role')->orderBy('node.lft');
     $builder->setParameter('role', $role);
     // Permission optional
     if ($permission) {
         $builder->addSelect('permission.name AS permission')->leftJoin('node', 'role_permission', 'rp', 'node.id = rp.role_id')->leftJoin('node', 'permission', 'permission', 'rp.permission_id = permission.id')->andWhere('(permission.name = :permission OR permission.name IS NULL)');
         $builder->setParameter('permission', $permission);
     }
     $parent = null;
     foreach ($builder->execute() as $row) {
         if ($parent) {
             if (!$rbac->hasRole($row['name'])) {
                 $rbac->getRole($parent)->addChild($row['name']);
             }
         } elseif (!$rbac->hasRole($row['name'])) {
             $rbac->addRole($row['name']);
         }
         if ($permission) {
             if ($row['permission']) {
                 $rbac->getRole($row['name'])->addPermission($row['permission']);
             }
         }
         $parent = $row['name'];
     }
     return $builder;
 }

 /**
  * checks if the given result set matches all search terms
  *
  * @param QueryBuilder $query
  * @param string $term
  * @param Keyword[] $keywords
  */
 private function addAndSearchLogic($query, $term, $keywords)
 {
     $searchTerms = $this->termHelper->splitTerm($term);
     $searchTermMatchQueries = $this->createSearchTermMatchQueries($keywords, $searchTerms);
     $totalSearchTermMatchesQuery = $this->connection->createQueryBuilder();
     $totalSearchTermMatchesQuery->select('sum(matches)')->from('(' . $searchTermMatchQueries . ')', 'termMatches')->where('termMatches.elementID = product_id');
     $query->addSelect('(' . $totalSearchTermMatchesQuery->getSQL() . ') AS searchTermMatches');
     $query->having('searchTermMatches >= ' . count($searchTerms));
 }

 /**
  * Sort products for current category by passed sort type
  *
  * @param QueryBuilder $builder
  * @param integer $orderBy
  */
 private function sortUnsortedByDefault($builder, $orderBy)
 {
     switch ($orderBy) {
         case 1:
             $builder->addOrderBy('product.datum', 'DESC')->addOrderBy('product.changetime', 'DESC');
             break;
         case 2:
             $builder->leftJoin('product', 's_articles_top_seller_ro', 'topSeller', 'topSeller.article_id = product.id')->addOrderBy('topSeller.sales', 'DESC')->addOrderBy('topSeller.article_id', 'DESC');
             break;
         case 3:
             $builder->addSelect('MIN(ROUND(defaultPrice.price * priceVariant.minpurchase * 1, 2)) as cheapest_price')->leftJoin('product', 's_articles_prices', 'defaultPrice', 'defaultPrice.articleID = product.id')->innerJoin('defaultPrice', 's_articles_details', 'priceVariant', 'priceVariant.id = defaultPrice.articledetailsID')->addOrderBy('cheapest_price', 'ASC')->addOrderBy('product.id', 'DESC');
             break;
         case 4:
             $builder->addSelect('MIN(ROUND(defaultPrice.price * priceVariant.minpurchase * 1, 2)) as cheapest_price')->leftJoin('product', 's_articles_prices', 'defaultPrice', 'defaultPrice.articleID = product.id')->innerJoin('defaultPrice', 's_articles_details', 'priceVariant', 'priceVariant.id = defaultPrice.articledetailsID')->addOrderBy('cheapest_price', 'DESC')->addOrderBy('product.id', 'DESC');
             break;
         case 5:
             $builder->addOrderBy('product.name', 'ASC');
             break;
         case 6:
             $builder->addOrderBy('product.name', 'DESC');
             break;
         case 7:
             $builder->addSelect('(SUM(vote.points) / COUNT(vote.id)) as votes')->leftJoin('product', 's_articles_vote', 'vote', 'product.id = vote.articleID')->addOrderBy('votes', 'DESC')->addOrderBy('product.id', 'DESC')->groupBy('product.id');
             break;
         case 9:
             $builder->innerJoin('product', 's_articles_details', 'variant', 'variant.id = product.main_detail_id')->addOrderBy('variant.instock', 'ASC')->addOrderBy('product.id', 'DESC');
             break;
         case 10:
             $builder->innerJoin('product', 's_articles_details', 'variant', 'variant.id = product.main_detail_id')->addOrderBy('variant.instock', 'DESC')->addOrderBy('product.id', 'DESC');
             break;
     }
 }

 /**
  * Adds an item that is to be returned in the query result.
  *
  * @param mixed $select The selection expression.
  *
  * @return self
  */
 public function addSelect($select = null)
 {
     $this->qb->addSelect($select);
     return $this;
 }