PHP Doctrine_Query::parseQuery方法代码示例

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