/**
* @test
*/
public function getSQLDelegatesToConcreteQueryBuilder()
{
$this->concreteQueryBuilder->getSQL()->shouldBeCalled()->willReturn('UPDATE aTable SET pid = 7');
$this->concreteQueryBuilder->getType()->willReturn(2);
// Update Type
$this->subject->getSQL();
}
/**
* @param QueryBuilder $builder
*/
public static function logBuilder($builder)
{
$sql = $builder->getSQL();
$params = $builder->getParameters();
self::log("Running Query: " . $sql . " with params:");
self::logObject($params);
}
/**
* Add a query from a query builder
*
* @param QueryBuilder $qb
* @return $this
*/
public function addQueryFromBuilder(QueryBuilder $qb)
{
/**
* @TODO : How to manage query params ?
*/
$this->queries[] = new Query($qb->getSQL());
return $this;
}
/**
* @param QueryBuilder $query The query builder
*
* @return string
*/
private function getNextAlias($query)
{
if (!preg_match_all('~\\b(\\w+)\\.~', $query->getSQL(), $matches)) {
return 'b';
}
$aliases = array_unique($matches[1]);
for ($alias = 'b'; in_array($alias, $aliases); $alias++) {
continue;
}
return $alias;
}
/**
* Gets the complete SQL string formed by the current specifications of this QueryBuilder.
*
* If the statement is a SELECT TYPE query restrictions based on TCA settings will
* automatically be applied based on the current QuerySettings.
*
* @return string The SQL query string.
*/
public function getSQL() : string
{
if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
return $this->concreteQueryBuilder->getSQL();
}
// Set additional query restrictions
$originalWhereConditions = $this->addAdditionalWhereConditions();
$sql = $this->concreteQueryBuilder->getSQL();
// Restore the original query conditions in case the user keeps
// on modifying the state.
$this->concreteQueryBuilder->add('where', $originalWhereConditions, false);
return $sql;
}
/**
* {@inheritdoc}
*/
public function getSQL()
{
return $this->qb->getSQL();
}
public function current()
{
return $this->dbal->fetchAll($this->qb->getSQL());
}
/**
* @param QueryBuilder $qb
*
* @return NativeQuery
*/
private function createQuery(QueryBuilder $qb)
{
$query = $this->em->createNativeQuery($qb->getSQL(), $this->rsm);
$query->setParameters($this->qb->getParameters());
return $query;
}
public function testGetState()
{
$qb = new QueryBuilder($this->conn);
$this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
$qb->select('u.*')->from('users', 'u');
$this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
$sql1 = $qb->getSQL();
$this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
$this->assertEquals($sql1, $qb->getSQL());
}
/**
* @group DBAL-172
*/
public function testSelectFromMasterWithWhereOnJoinedTables()
{
$qb = new QueryBuilder($this->conn);
$qb->select('COUNT(DISTINCT news.id)')->from('newspages', 'news')->innerJoin('news', 'nodeversion', 'nv', "nv.refId = news.id AND nv.refEntityname='Entity\\News'")->innerJoin('nv', 'nodetranslation', 'nt', 'nv.nodetranslation = nt.id')->innerJoin('nt', 'node', 'n', 'nt.node = n.id')->where('nt.lang = ?')->andWhere('n.deleted = 0');
$this->assertEquals("SELECT COUNT(DISTINCT news.id) FROM newspages news INNER JOIN nodeversion nv ON nv.refId = news.id AND nv.refEntityname='Entity\\News' INNER JOIN nodetranslation nt ON nv.nodetranslation = nt.id INNER JOIN node n ON nt.node = n.id WHERE (nt.lang = ?) AND (n.deleted = 0)", $qb->getSQL());
}
/**
* @group DBAL-1137
*/
public function testJoinWithNonUniqueAliasThrowsException()
{
$qb = new QueryBuilder($this->conn);
$qb->select('a.id')->from('table_a', 'a')->join('a', 'table_b', 'a', 'a.fk_b = a.id');
$this->setExpectedException('Doctrine\\DBAL\\Query\\QueryException', "The given alias 'a' is not unique in FROM and JOIN clause table. The currently registered aliases are: a.");
$qb->getSQL();
}
/**
* Gets the complete SQL string formed by the current specifications of this QueryBuilder.
*
* <code>
* $qb = $conn->getQueryBuilder()
* ->select('u')
* ->from('User', 'u')
* echo $qb->getSQL(); // SELECT u FROM User u
* </code>
*
* @return string The SQL query string.
*/
public function getSQL()
{
return $this->queryBuilder->getSQL();
}
/**
* @group DBAL-172
*/
public function testReferenceJoinFromJoin()
{
$qb = new QueryBuilder($this->conn);
$qb->select("l.id", "mdsh.xcode", "mdso.xcode")->from("location_tree", "l")->join("l", "location_tree_pos", "p", "l.id = p.tree_id")->rightJoin("l", "hotel", "h", "h.location_id = l.id")->leftJoin("l", "offer_location", "ol", "l.id=ol.location_id")->leftJoin("ol", "mds_offer", "mdso", "ol.offer_id = mdso.offer_id")->leftJoin("h", "mds_hotel", "mdsh", "h.id = mdsh.hotel_id")->where("p.parent_id IN (:ids)")->andWhere("(mdso.xcode IS NOT NULL OR mdsh.xcode IS NOT NULL)");
$this->setExpectedException('Doctrine\\DBAL\\Query\\QueryException', "The given alias 'ol' is not part of any FROM clause table. The currently registered FROM-clause aliases are: l");
$this->assertEquals('', $qb->getSQL());
}
/**
* @group DBAL-442
*/
public function testSelectWithMultipleFromAndJoins()
{
$qb = new QueryBuilder($this->conn);
$qb->select('DISTINCT u.id')->from('users', 'u')->from('articles', 'a')->innerJoin('u', 'permissions', 'p', 'p.user_id = u.id')->innerJoin('a', 'comments', 'c', 'c.article_id = a.id')->where('u.id = a.user_id')->andWhere('p.read = 1');
$this->assertEquals('SELECT DISTINCT u.id FROM users u INNER JOIN permissions p ON p.user_id = u.id, articles a INNER JOIN comments c ON c.article_id = a.id WHERE (u.id = a.user_id) AND (p.read = 1)', $qb->getSQL());
}
public function testComplexSelectWithSomeTableAliases()
{
$qb = new QueryBuilder($this->conn);
$qb->select('u.id')->from('users', 'u')->from('articles')->innerJoin('u', 'permissions', 'p', 'p.user_id = u.id')->innerJoin('articles', 'comments', 'c', 'c.article_id = articles.id');
$this->assertEquals('SELECT u.id FROM users u INNER JOIN permissions p ON p.user_id = u.id, articles INNER JOIN comments c ON c.article_id = articles.id', $qb->getSQL());
}
/**
* Format SQL query by replacing {x} and {x.y} tokens.
*
* @staticvar string $re
*
* @param \Doctrine\DBAL\Query\QueryBuilder $qb
* @param array $tables
*
* @throws \Lokhman\Silex\ARM\Exception\RepositoryException
* @return string
*/
private function formatQuery(QueryBuilder $qb, array $tables)
{
static $re = '/("|\').*?\\1(*SKIP)(*FAIL)|\\{(?:[^{}]|(?R))*\\}/';
return preg_replace_callback($re, function ($matches) use($tables) {
if (false !== ($pos = strpos($token = substr($matches[0], 1, -1), '.'))) {
if (!isset($tables[$alias = substr($token, 0, $pos)])) {
self::raise('No repository registered for "' . $alias . '".');
}
return $tables[$alias]->getTable() . self::UNIQUE . substr($token, $pos + 1);
} elseif (in_array($token, $this->metadata->getColumns())) {
return $this->table . self::UNIQUE . $token;
} else {
return $matches[0];
}
}, $qb->getSQL());
}
