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