/**
* @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 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 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 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 testAggregateFunctionsInHavingReturnValidSql2()
{
$q = new Doctrine_Query();
$q->parseQuery("SELECT u.name FROM User u LEFT JOIN u.Phonenumber p HAVING MAX(u.name) = 'zYne'");
$this->assertEqual($q->getQuery(), "SELECT e.id AS e__id, e.name AS e__name FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE (e.type = 0) HAVING MAX(e.name) = 'zYne'");
}
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');
}
/**
* create
* creates this view
*
* @throws Doctrine_View_Exception
* @return void
*/
public function create()
{
$sql = sprintf(self::CREATE, $this->name, $this->query->getQuery());
try {
$this->conn->execute($sql);
} catch (Doctrine_Exception $e) {
throw new Doctrine_View_Exception($e->__toString());
}
}
public function testOrderByWithDifferentOrderning()
{
try {
$q = new Doctrine_Query();
$q->select('u.name')->from('User u')->orderby('u.id ASC, u.name DESC');
// nonesese results expected, but query is syntatically ok.
$this->assertEqual($q->getQuery(), 'SELECT e.id AS e__id, e.name AS e__name FROM entity e WHERE (e.type = 0) ORDER BY e.id ASC, e.name DESC');
$this->pass();
} catch (Exception $e) {
$this->fail($e->getMessage());
}
}
/**
public function testShortAliasesWithSingleComponent() {
$q = new Doctrine_Query();
$q->select('u.name')->from('User u');
$this->assertEqual($q->getQuery(), 'SELECT e.id AS e__id, e.name AS e__name FROM entity e WHERE (e.type = 0)');
}
*/
public function testShortAliasesWithOneToManyLeftJoin() {
$q = new Doctrine_Query();
$q->select('u.name, p.id')->from('User u LEFT JOIN u.Phonenumber p');
$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 WHERE (e.type = 0)');
$users = $q->execute();
$this->assertEqual($users->count(), 8);
}
public function testConditionParser2()
{
$query = new Doctrine_Query($this->connection);
$query->select('User.id')->from("User")->where("User.name LIKE 'z%' OR User.name LIKE 's%'");
$sql = "SELECT e.id AS e__id FROM entity e WHERE (e.name LIKE 'z%' OR e.name LIKE 's%') AND (e.type = 0)";
$this->assertEqual($query->getQuery(), $sql);
$query->where("(User.name LIKE 'z%') OR (User.name LIKE 's%')");
$this->assertEqual($query->getQuery(), $sql);
$query->where("((User.name LIKE 'z%') OR (User.name LIKE 's%'))");
$this->assertEqual($query->getQuery(), $sql);
$query->where("(((User.name LIKE 'z%') OR (User.name LIKE 's%')))");
$this->assertEqual($query->getQuery(), $sql);
$query->where("(((User.name LIKE 'z%') OR User.name LIKE 's%'))");
$this->assertEqual($query->getQuery(), $sql);
$query->where("(User.name LIKE 'z%') OR User.name LIKE 's%' AND User.name LIKE 'a%'");
$sql = "SELECT e.id AS e__id FROM entity e WHERE ((e.name LIKE 'z%' OR e.name LIKE 's%') AND e.name LIKE 'a%') AND (e.type = 0)";
$this->assertEqual($query->getQuery(), "SELECT e.id AS e__id FROM entity e WHERE (e.name LIKE 'z%' OR (e.name LIKE 's%' AND e.name LIKE 'a%')) AND (e.type = 0)");
$query->where("(((User.name LIKE 'z%') OR User.name LIKE 's%')) AND User.name LIKE 'a%'");
$this->assertEqual($query->getQuery(), $sql);
$query->where("((((User.name LIKE 'z%') OR User.name LIKE 's%')) AND User.name LIKE 'a%')");
$this->assertEqual($query->getQuery(), $sql);
$query->where("(((((User.name LIKE 'z%') OR User.name LIKE 's%')) AND User.name LIKE 'a%'))");
$this->assertEqual($query->getQuery(), $sql);
}
public function testQueryWithMultipleNestedAliases()
{
$this->connection->clear();
$q = new Doctrine_Query();
$q->from('User u, u.Phonenumber, u.Group g, g.Phonenumber')->where('u.id IN (5,6)');
$users = $q->execute();
$count = count($this->conn);
$this->assertTrue($users[0]->Phonenumber instanceof Doctrine_Collection);
$this->assertEqual($q->getQuery(), "SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id, p.id AS p__id, p.phonenumber AS p__phonenumber, p.entity_id AS p__entity_id, e2.id AS e2__id, e2.name AS e2__name, e2.loginname AS e2__loginname, e2.password AS e2__password, e2.type AS e2__type, e2.created AS e2__created, e2.updated AS e2__updated, e2.email_id AS e2__email_id, p2.id AS p2__id, p2.phonenumber AS p2__phonenumber, p2.entity_id AS p2__entity_id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id LEFT JOIN groupuser g ON (e.id = g.user_id) LEFT JOIN entity e2 ON e2.id = g.group_id AND e2.type = 1 LEFT JOIN phonenumber p2 ON e2.id = p2.entity_id WHERE e.id IN (5, 6) AND (e.type = 0)");
$this->assertEqual(count($users), 2);
$this->assertEqual(count($users[0]['Group']), 1);
$this->assertEqual(count($users[0]['Group'][0]['Phonenumber']), 1);
$this->assertEqual(count($users[1]['Group']), 0);
$this->assertEqual($count, count($this->conn));
}
public function testAggregateFunctionParsingSupportsMultipleComponentReferences()
{
$q = new Doctrine_Query();
$q->select('SUM(i.price * i.quantity)')
->from('QueryTest_Item i');
$this->assertEqual($q->getQuery(), "SELECT SUM(q.price * q.quantity) AS q__0 FROM query_test__item q");
}
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');
}
public function testLimitAttribute()
{
$this->manager->setAttribute(Doctrine::ATTR_QUERY_LIMIT, Doctrine::LIMIT_ROWS);
$this->connection->clear();
$q = new Doctrine_Query();
$q->from('User')->where('User.Group.name = ?')->orderby('User.id DESC')->limit(5);
$users = $q->execute(array('Tough guys inc.'));
$this->assertEqual($users->count(), 3);
$this->assertEqual($q->getQuery(), "SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e LEFT JOIN groupuser g ON (e.id = g.user_id) LEFT JOIN entity e2 ON e2.id = g.group_id AND e2.type = 1 WHERE e2.name = ? AND (e.type = 0) ORDER BY e.id DESC LIMIT 5");
$this->manager->setAttribute(Doctrine::ATTR_QUERY_LIMIT, Doctrine::LIMIT_RECORDS);
}
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 testLimitSubqueryAlgorithmSupportsIdentifierQuoting()
{
$q = new Doctrine_Query();
$q->parseQuery('SELECT u.name FROM User u INNER JOIN u.Phonenumber p')->limit(5);
$this->assertEqual($q->getQuery(), 'SELECT "e"."id" AS "e__id", "e"."name" AS "e__name" FROM "entity" "e" INNER JOIN "phonenumber" "p" ON "e"."id" = "p"."entity_id" WHERE "e"."id" IN (SELECT DISTINCT "e2"."id" FROM "entity" "e2" INNER JOIN "phonenumber" "p2" ON "e2"."id" = "p2"."entity_id" WHERE ("e2"."type" = 0) LIMIT 5) AND ("e"."type" = 0)');
}
public function testLiteralEnumValueConversionSupportsJoins()
{
$q = new Doctrine_Query($this->connection);
$q->addSelect('e.*')
->addSelect('e3.*')
->from('EnumTest e')
->leftJoin('e.Enum3 e3')
->where("e.status = 'verified'")
->execute();
$this->assertEqual($q->getQuery(), "SELECT e.id AS e__id, e.status AS e__status, e.text AS e__text, e2.text AS e2__text FROM enum_test e LEFT JOIN enum_test3 e2 ON e.text = e2.text WHERE e.status = 'verified'");
}
public function testMultipleJoins2()
{
$q = new Doctrine_Query();
$q->select('u.id, g.id, e.id')->from('Group g')->leftJoin('g.User u')->leftJoin('u.Account a');
$this->assertEqual($q->getQuery(), 'SELECT e.id AS e__id, e2.id AS e2__id FROM entity e LEFT JOIN groupuser g ON (e.id = g.group_id) LEFT JOIN entity e2 ON e2.id = g.user_id AND e2.type = 0 LEFT JOIN account a ON e2.id = a.entity_id WHERE (e.type = 1)');
try {
$q->execute();
$this->pass();
} catch (Doctrine_Exception $e) {
$this->fail();
}
}
public function testDeepComponentReferencingIsSupported2()
{
$q = new Doctrine_Query();
$q->select('u.id')->from('User u')->addWhere("u.Group.name ='some group'");
$this->assertEqual($q->getQuery(), "SELECT e.id AS e__id FROM entity e LEFT JOIN groupuser g ON e.id = g.user_id LEFT JOIN entity e2 ON e2.id = g.group_id AND e2.type = 1 WHERE e2.name = 'some group' AND (e.type = 0)");
}
public function testUpdateSupportsColumnReferencing()
{
$q = new Doctrine_Query();
$q->update('User u')->set('u.id', 'u.id + 1');
$this->assertEqual($q->getQuery(), "UPDATE entity SET id = id + 1 WHERE (type = 0)");
}
public function testUpdateSupportsComplexExpressions()
{
$q = new Doctrine_Query();
$q->update('User u')->set('u.name', "CONCAT(?, CONCAT(':', SUBSTRING(u.name, LOCATE(':', u.name)+1, LENGTH(u.name) - LOCATE(':', u.name)+1)))", array('gblanco'))->where('u.id IN (SELECT u2.id FROM User u2 WHERE u2.name = ?) AND u.email_id = ?', array('guilhermeblanco', 5));
$this->assertEqual($q->getQuery(), "UPDATE entity SET name = CONCAT(?, CONCAT(':', SUBSTRING(name, LOCATE(':', name)+1, LENGTH(name) - LOCATE(':', name)+1))) WHERE (id IN (SELECT e2.id AS e2__id FROM entity e2 WHERE e2.name = ? AND (e2.type = 0)) AND email_id = ?) AND (type = 0)");
}
public function testMixingOfJoins2()
{
$q = new Doctrine_Query();
$q->select('u.name, g.name, p.phonenumber')->from('User u INNER JOIN u.Group g LEFT JOIN g.Phonenumber p');
$this->assertEqual($q->getQuery(), "SELECT e.id AS e__id, e.name AS e__name, e2.id AS e2__id, e2.name AS e2__name, p.id AS p__id, p.phonenumber AS p__phonenumber FROM entity e INNER JOIN groupuser g ON (e.id = g.user_id) INNER JOIN entity e2 ON e2.id = g.group_id AND e2.type = 1 LEFT JOIN phonenumber p ON e2.id = p.entity_id WHERE (e.type = 0)");
}
public function testParseClauseSupportsComponentReferences()
{
$q = new Doctrine_Query();
$q->from('User u')->leftJoin('u.Phonenumber p');
$q->getQuery();
//Doctrine::dump($q->getCachedForm(array('foo' => 'bar')));
$this->assertEqual($q->parseClause("CONCAT('u.name', u.name)"), "CONCAT('u.name', e.name)");
}
