/**
* 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);
}
}
}
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;
}
/**
* {@inheritdoc}
* @return NativeQueryBuilder
*/
public function from($from, $alias = NULL)
{
return parent::from($this->addTableResultMapping($from, $alias), $alias);
}
/**
* 加载数据库结果集
*
* @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 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;
}
/**
* @test
* @todo: Test with alias
*/
public function fromQuotesIdentifierAndDelegatesToConcreteQueryBuilder()
{
$this->connection->quoteIdentifier('aTable')->shouldBeCalled()->willReturnArgument(0);
$this->concreteQueryBuilder->from(Argument::exact('aTable'), Argument::cetera())->shouldBeCalled()->willReturn($this->subject);
$this->subject->from('aTable');
}
/**
* Creates and adds a query root corresponding to the table identified by the
* given alias, forming a cartesian product with any existing query roots.
*
* <code>
* $qb = $conn->getQueryBuilder()
* ->select('u.id')
* ->from('users', 'u')
* </code>
*
* @param string $from The table.
* @param string|null $alias The alias of the table.
*
* @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
*/
public function from($from, $alias = null)
{
$this->queryBuilder->from($this->getTableName($from), $alias);
return $this;
}
/**
* Creates and adds a query root corresponding to the table identified by the
* given alias, forming a cartesian product with any existing query roots.
*
* @param string $from The table. Will be quoted according to database platform automatically.
* @param string $alias The alias of the table. Will be quoted according to database platform automatically.
*
* @return QueryBuilder This QueryBuilder instance.
*/
public function from(string $from, string $alias = null) : QueryBuilder
{
$this->concreteQueryBuilder->from($this->quoteIdentifier($from), empty($alias) ? $alias : $this->quoteIdentifier($alias));
return $this;
}
/**
* Creates and adds a query root corresponding to the table identified by the
* given alias, forming a cartesian product with any existing query roots.
*
* @param string $from The table.
* @param string $alias The alias of the table.
*
* @return self
*/
public function from($from, $alias)
{
$this->qb->from($from, $alias);
return $this;
}
/**
* Creates and adds a query root corresponding to the table identified by the
* given alias, forming a cartesian product with any existing query roots.
*
* <code>
* $qb = $conn->getQueryBuilder()
* ->select('u.id')
* ->from('users', 'u')
* </code>
*
* @param string $from The table.
* @param string|null $alias The alias of the table.
*
* @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
*/
public function from($from, $alias = null)
{
$this->queryBuilder->from($this->helper->quoteColumnName($from), $alias);
return $this;
}