/**
* @param Doctrine_Query $query the base query
*/
public function __construct($query)
{
if (is_string($query)) {
$this->query = new Doctrine_Query();
$this->query->parseQuery($query);
} elseif ($query instanceof Doctrine_Query) {
$this->query = $query;
} else {
throw new Doctrine_Exception('Constructor argument should be either Doctrine_Query object or valid DQL query');
}
$this->query->getQuery();
}
public function testSubqueryInSelectPart2()
{
// ticket #307
$q = new Doctrine_Query();
$q->parseQuery("SELECT u.name, (SELECT COUNT(w.id) FROM User w WHERE w.id = u.id) pcount FROM User u WHERE u.name = 'zYne' LIMIT 1");
$this->assertNotEqual($q->getQuery(), "SELECT e.id AS e__id, e.name AS e__name, (SELECT COUNT(e.id) AS e__0 FROM entity e WHERE e.id = e.id AND (e.type = 0)) AS e__0 FROM entity e WHERE e.name = 'zYne' AND (e.type = 0) LIMIT 1");
}
public function testJoinWithConditionAndNotInClause()
{
// Related to ticket #1329
$q = new Doctrine_Query();
$q->parseQuery("SELECT a.name, b.id FROM User a LEFT JOIN a.Phonenumber b WITH a.id NOT IN (1, 2, 3)");
$this->assertEqual($q->getQuery(), "SELECT e.id AS e__id, e.name AS e__name, p.id AS p__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id AND (e.id NOT IN (1, 2, 3)) WHERE (e.type = 0)");
}
public function load($condition)
{
$condition = trim($condition);
$e = Doctrine_Tokenizer::sqlExplode($condition);
if (count($e) > 2) {
$a = explode('.', $e[0]);
$field = array_pop($a);
$reference = implode('.', $a);
$operator = $e[1];
$value = $e[2];
$alias = $this->query->getTableAlias($reference);
$map = $this->query->getAliasDeclaration($reference);
$table = $map['table'];
// check if value is enumerated value
$enumIndex = $table->enumIndex($field, trim($value, "'"));
if (substr($value, 0, 1) == '(') {
// trim brackets
$trimmed = Doctrine_Tokenizer::bracketTrim($value);
if (substr($trimmed, 0, 4) == 'FROM' || substr($trimmed, 0, 6) == 'SELECT') {
// subquery found
$q = new Doctrine_Query();
$value = '(' . $q->parseQuery($trimmed)->getQuery() . ')';
} elseif (substr($trimmed, 0, 4) == 'SQL:') {
$value = '(' . substr($trimmed, 4) . ')';
} else {
// simple in expression found
$e = Doctrine_Tokenizer::sqlExplode($trimmed, ',');
$value = array();
foreach ($e as $part) {
$index = $table->enumIndex($field, trim($part, "'"));
if ($index !== false) {
$value[] = $index;
} else {
$value[] = $this->parseLiteralValue($part);
}
}
$value = '(' . implode(', ', $value) . ')';
}
} else {
if ($enumIndex !== false) {
$value = $enumIndex;
} else {
$value = $this->parseLiteralValue($value);
}
}
switch ($operator) {
case '<':
case '>':
case '=':
case '!=':
if ($enumIndex !== false) {
$value = $enumIndex;
}
default:
$condition = $alias . '.' . $field . ' ' . $operator . ' ' . $value;
}
}
return $condition;
}
public function testConcatInSelectClauseSupportsMoreThanTwoArgs()
{
$q = new Doctrine_Query();
$q->parseQuery("SELECT u.id, CONCAT(u.name, 'The Man', u.loginname) FROM User u");
$this->assertEqual($q->getQuery(), "SELECT e.id AS e__id, CONCAT(e.name, 'The Man', e.loginname) AS e__0 FROM entity e WHERE (e.type = 0)");
}
public function testUpdateSupportsConditions()
{
$q = new Doctrine_Query();
$q->parseQuery("UPDATE User u SET u.name = 'someone' WHERE u.id = 5");
$this->assertEqual($q->getQuery(), "UPDATE entity SET name = 'someone' WHERE id = 5 AND (type = 0)");
}
public function testAggregateFunctionsInHavingReturnValidSql()
{
$q = new Doctrine_Query();
$q->parseQuery('SELECT u.name, COUNT(p.id) count FROM User u LEFT JOIN u.Phonenumber p GROUP BY count');
$this->assertEqual($q->getQuery(), 'SELECT e.id AS e__id, e.name AS e__name, COUNT(p.id) AS p__0 FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE (e.type = 0) GROUP BY p__0');
}
public function testDeleteWithLimitAndOffset()
{
$q = new Doctrine_Query();
$q->parseQuery('DELETE FROM Entity LIMIT 10 OFFSET 20');
$this->assertEqual($q->getQuery(), 'DELETE FROM entity LIMIT 10 OFFSET 20');
$q = new Doctrine_Query();
$q->delete()->from('Entity')->limit(10)->offset(20);
$this->assertEqual($q->getQuery(), 'DELETE FROM entity LIMIT 10 OFFSET 20');
}
public function testCountQuerySupportsIdentifierQuoting()
{
$q = new Doctrine_Query();
$q->parseQuery('SELECT u.name FROM User u INNER JOIN u.Phonenumber p');
$this->assertEqual($q->getCountQuery(), 'SELECT COUNT(DISTINCT "e"."id") AS num_results FROM "entity" "e" INNER JOIN "phonenumber" "p" ON "e"."id" = "p"."entity_id" WHERE "e"."type" = 0 GROUP BY "e"."id"');
$this->conn->setAttribute(Doctrine::ATTR_QUOTE_IDENTIFIER, false);
}
public function testParameterConversionInCount()
{
try {
$query = new Doctrine_Query($this->connection);
$ret = $query->parseQuery("FROM EnumTest WHERE EnumTest.status = 'open'")
->count();
$this->assertEqual($ret, 1);
} catch (Exception $e) {
$this->fail();
}
try {
$query = new Doctrine_Query($this->connection);
$ret = $query->parseQuery("FROM EnumTest WHERE EnumTest.status = ?")
->count(array('open'));
$this->assertEqual($ret, 1);
} catch (Exception $e) {
$this->fail($e->getMessage());
}
}
public function testAggregateFunctionsInHavingSupportMultipleParameters()
{
$q = new Doctrine_Query();
$q->parseQuery("SELECT CONCAT(u.name, u.loginname) name FROM User u LEFT JOIN u.Phonenumber p HAVING name = 'xx'");
}
public function testMultipleAggregateFunctionValueHydrationWithAliasesAndCleanRecords()
{
$this->connection->clear();
$q = new Doctrine_Query();
$q->parseQuery('SELECT u.id, COUNT(p.id) count, MAX(p.phonenumber) max FROM User u, u.Phonenumber p GROUP BY u.id');
$this->assertEqual($q->getQuery(), 'SELECT e.id AS e__id, COUNT(p.id) AS p__0, MAX(p.phonenumber) AS p__1 FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE (e.type = 0) GROUP BY e.id');
$users = $q->execute();
$this->assertEqual($users[0]->Phonenumber[0]->state(), Doctrine_Record::STATE_TDIRTY);
$this->assertEqual($users[0]->Phonenumber[0]->count, 1);
$this->assertEqual($users[1]->Phonenumber[0]->count, 3);
$this->assertEqual($users[2]->Phonenumber[0]->count, 1);
$this->assertEqual($users[3]->Phonenumber[0]->count, 1);
$this->assertEqual($users[4]->Phonenumber[0]->count, 3);
$this->assertEqual($users[0]->Phonenumber[0]->max, '123 123');
$this->assertEqual($users[1]->Phonenumber[0]->max, '789 789');
$this->assertEqual($users[2]->Phonenumber[0]->max, '123 123');
$this->assertEqual($users[3]->Phonenumber[0]->max, '111 222 333');
$this->assertEqual($users[4]->Phonenumber[0]->max, '444 555');
}