PHP Doctrine_Query::getQuery方法代码示例

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