/**
* @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;
}
