PHP Doctrine_Query::getSqlQuery方法代码示例

 public function testQueryCopy()
 {
     $q = new Doctrine_Query();
     $q->from('User u');
     $q2 = $q->copy();
     $this->assertEqual($q->getSqlQuery(), $q2->getSqlQuery());
     $this->assertEqual($q->getSqlQuery(), '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 WHERE (e.type = 0)');
 }

 /**
  * @param Doctrine_Query $query         the base query
  */
 public function __construct($query)
 {
     if (is_string($query)) {
         $this->query = Doctrine_Query::create();
         $this->query->parseDqlQuery($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->getSqlQuery();
 }

 public function testJoinWithConditionAndNotInClause()
 {
     // Related to ticket #1329
     $q = new Doctrine_Query();
     $q->parseDqlQuery("SELECT a.name, b.id FROM User a LEFT JOIN a.Phonenumber b WITH a.id NOT IN (1, 2, 3)");
     $this->assertEqual($q->getSqlQuery(), "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 testApplyInheritance()
 {
     $query = new Doctrine_Query();
     $query->from('InheritanceDeal d, d.Users u');
     $query->where('u.id = 1');
     $sql = 'SELECT i.id AS i__id, i.name AS i__name, i2.id AS i2__id, i2.username AS i2__username FROM inheritance_deal i LEFT JOIN inheritance_entity_user i3 ON (i.id = i3.entity_id) AND i3.type = 1 LEFT JOIN inheritance_user i2 ON i2.id = i3.user_id WHERE i2.id = 1';
     $this->assertEqual($sql, $query->getSqlQuery());
 }

 /**
 public function testShortAliasesWithSingleComponent() {
     $q = new Doctrine_Query();
 
     $q->select('u.name')->from('User u');
 
     $this->assertEqual($q->getSqlQuery(), '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->getSqlQuery(), '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 testDeleteWithLimitAndOffset()
 {
     $q = new Doctrine_Query();
     $q->parseDqlQuery('DELETE FROM Entity LIMIT 10 OFFSET 20');
     $this->assertEqual($q->getSqlQuery(), 'DELETE FROM entity LIMIT 10 OFFSET 20');
     $q = new Doctrine_Query();
     $q->delete()->from('Entity')->limit(10)->offset(20);
     $this->assertEqual($q->getSqlQuery(), 'DELETE FROM entity LIMIT 10 OFFSET 20');
 }

 /**
  * creates this view
  *
  * @throws Doctrine_View_Exception
  * @return void
  */
 public function create()
 {
     $sql = sprintf(self::CREATE, $this->_name, $this->_query->getSqlQuery());
     try {
         $this->_conn->execute($sql, $this->_query->getFlattenedParams());
     } 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->getSqlQuery(), '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());
     }
 }

 /**
  * Searchable keyword search
  * 
  * @param string $string Keyword string to search for
  * @param Doctrine_Query $query Query object to alter. Adds where condition to limit the results using the search index
  * @return array    ids and relevancy
  */
 public function search($string, $query = null)
 {
     $q = new Doctrine_Search_Query($this->_table);
     if ($query instanceof Doctrine_Query) {
         $q->query($string, false);
         $newQuery = $query->copy();
         $query->getSqlQuery();
         $key = (array) $this->getOption('table')->getIdentifier();
         $newQuery->addWhere($query->getRootAlias() . '.' . current($key) . ' IN (SQL:' . $q->getSqlQuery() . ')', $q->getParams());
         return $newQuery;
     } else {
         $q->query($string);
         return $this->_options['connection']->fetchAll($q->getSqlQuery(), $q->getParams());
     }
 }

 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->getSqlQuery(), "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 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->getSqlQuery(), $sql);
     $query->where("(User.name LIKE 'z%') OR (User.name LIKE 's%')");
     $this->assertEqual($query->getSqlQuery(), $sql);
     $query->where("((User.name LIKE 'z%') OR (User.name LIKE 's%'))");
     $this->assertEqual($query->getSqlQuery(), $sql);
     $query->where("(((User.name LIKE 'z%') OR (User.name LIKE 's%')))");
     $this->assertEqual($query->getSqlQuery(), $sql);
     $query->where("(((User.name LIKE 'z%') OR User.name LIKE 's%'))");
     $this->assertEqual($query->getSqlQuery(), $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->getSqlQuery(), "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->getSqlQuery(), $sql);
     $query->where("((((User.name LIKE 'z%') OR User.name LIKE 's%')) AND User.name LIKE 'a%')");
     $this->assertEqual($query->getSqlQuery(), $sql);
     $query->where("(((((User.name LIKE 'z%') OR User.name LIKE 's%')) AND User.name LIKE 'a%'))");
     $this->assertEqual($query->getSqlQuery(), $sql);
 }

 public function testGetLimitSubqueryOrderBy2()
 {
     $q = new Doctrine_Query();
     $q->select('u.name, COUNT(DISTINCT a.id) num_albums');
     $q->from('User u, u.Album a');
     $q->orderby('num_albums');
     $q->groupby('u.id');
     try {
         // this causes getLimitSubquery() to be used, and it fails
         $q->limit(5);
         $users = $q->execute();
         $count = $users->count();
     } catch (Doctrine_Exception $e) {
         $this->fail($e->getMessage());
     }
     $this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id, e.name AS e__name, COUNT(DISTINCT a.id) AS a__0 FROM entity e LEFT JOIN album a ON e.id = a.user_id WHERE e.id IN (SELECT DISTINCT e2.id FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id ORDER BY a__0 LIMIT 5) AND (e.type = 0) GROUP BY e.id ORDER BY a__0');
 }

 public function testTicket()
 {
     Doctrine_Manager::getInstance()->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, true);
     $q = new Doctrine_Query();
     $q->select('s.*')->from('Ticket_1116_User s')->where('s.username = ?', array('test'));
     // to see the error switch dbh to a real db, the next line will trigger the error
     $test = $q->fetchOne();
     //will only fail with "real" mysql
     $this->assertFalse($test);
     $sql = $q->getSqlQuery();
     // just getSql()?!?! and it works ? the params are ok after this call
     $params = $q->getFlattenedParams();
     $this->assertEqual(count($params), 1);
     // now we have array('test',null) very strange .....
     $this->assertEqual($sql, "SELECT u.id AS u__id, u.username AS u__username, u.deleted_at AS u__deleted_at FROM user u WHERE (u.username = ? AND (u.deleted_at IS NULL))");
     $this->assertEqual($params, array('test'));
     //now also this works! (always works witch mock only fails with mysql)
     $test = $q->fetchOne();
     $this->assertFalse($test);
     Doctrine_Manager::getInstance()->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, false);
 }

 public function testQueryDoesNotSeparateNotAndExists()
 {
     $q = new Doctrine_Query();
     $q->select('u.id')->from('User u')->where('NOT EXISTS (SELECT g.id FROM Group g)');
     $this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id FROM entity e WHERE (NOT EXISTS (SELECT e2.id AS e2__id FROM entity e2 WHERE (e2.type = 1)) AND (e.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->getSqlQuery(), "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)");
 }