$qb = $conn->createQueryBuilder() ->select('u.id') ->from('users', 'u')
/** * 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; }