$qb = $conn->createQueryBuilder() ->select('u.id') ->addSelect('p.id') ->from('users', 'u') ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');
/** * @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; }
/** * 加载数据库结果集 * * @param bool $multiple 是否加载多行数据 * @return $this|mixed */ protected function _loadResult($multiple = false) { $this->_dbBuilder->from($this->db()->quoteIdentifier($this->_tableName), $this->db()->quoteIdentifier($this->_objectName)); // 只获取单条记录 if (false === $multiple) { $this->_dbBuilder->setMaxResults(1); } // 默认选择所有字段 $this->_dbBuilder->addSelect($this->_buildSelect()); // 处理排序问题 if (!isset($this->_dbApplied['orderBy']) && !empty($this->_sorting)) { foreach ($this->_sorting as $column => $direction) { if (false === strpos($column, '.')) { // Sorting column for use in JOINs $column = $this->_objectName . '.' . $column; } $this->_dbBuilder->orderBy($column, $direction); } } if (true === $multiple) { $result = $this->_dbBuilder->execute(); $result->setFetchMode(PDO::FETCH_CLASS, $this->_loadMultiResultFetcherClass(), $this->_loadMultiResultFetcherConstructor()); $this->reset(); return $result->fetchAll(); } else { $result = $this->_dbBuilder->execute()->fetch(); $this->reset(); if ($result) { $this->_loadValues($result); } else { $this->clear(); } return $this; } }
public function addSelect($select = NULL) { $selects = is_array($select) ? $select : func_get_args(); foreach ($selects as &$arg) { if ($arg instanceof Doctrine\DBAL\Query\QueryBuilder) { $arg = '(' . $arg->getSQL() . ')'; } } return parent::addSelect($selects); }
/** * Adds an item that is to be returned in the query result. * * @param string[] $selects The selection expression. * * @return QueryBuilder This QueryBuilder instance. */ public function addSelect(string ...$selects) : QueryBuilder { $this->concreteQueryBuilder->addSelect(...$this->quoteIdentifiersForSelect($selects)); return $this; }
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'); } if ($this->pageVersionToRetrieve == self::PAGE_VERSION_RECENT) { $query->andWhere('cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)'); } else { $query->andWhere('cvIsApproved = 1'); } 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 (!$this->includeSystemPages) { $query->andWhere('p.cIsSystemPage = :cIsSystemPage'); $query->setParameter('cIsSystemPage', false); } return $query; }
/** * Adds an item that is to be returned in the query result. This should * only be used for literal SQL expressions as no quoting/escaping of * any kind will be performed on the items. * * @param string[] $selects Literal SQL expressions to be selected. * @return QueryBuilder This QueryBuilder instance. */ public function addSelectLiteral(string ...$selects) : QueryBuilder { $this->concreteQueryBuilder->addSelect(...$selects); return $this; }