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