PHP Doctrine_Query::getSql方法代码示例

    public function testQueryCopy()
    {
        $q = new Doctrine_Query();

        $q->from('User u');
        
        $q2 = $q->copy();
        
        $this->assertEqual($q->getSql(), $q2->getSql());

	    $this->assertEqual($q->getSql(), '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)');
    }

    public function testSelectDistinctIsSupported2()
    {
        $q = new Doctrine_Query();
        
        $q->select('DISTINCT u.name')->from('User u');

        $this->assertEqual($q->getSql(), "SELECT DISTINCT e.id AS e__id, e.name AS e__name FROM entity e 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->getSql());
 }

 /**
  * constructor
  *
  * @param Doctrine_Query $query
  */
 public function __construct(Doctrine_Query $query, $viewName)
 {
     $this->_name = $viewName;
     $this->_query = $query;
     $this->_query->setView($this);
     $this->_conn = $query->getConnection();
     $this->_dql = $query->getDql();
     $this->_sql = $query->getSql();
 }

 /**
  * 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 mixed The Doctrine_Collection or array of 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->getSql();
         $newQuery->addWhere($query->getRootAlias() . '.id IN(' . $q->getSql() . ')', $q->getParams());
         return $newQuery;
     } else {
         $q->query($string);
         return $this->_options['connection']->fetchAll($q->getSql(), $q->getParams());
     }
 }

 /**
  * 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 mixed The Doctrine_Collection or array of 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->getSql();
         $key = (array) $this->getOption('table')->getIdentifier();
         $newQuery->addWhere($query->getRootAlias() . '.' . current($key) . ' IN (SQL:' . $q->getSql() . ')', $q->getParams());
         return $newQuery;
     } else {
         $q->query($string);
         return $this->_options['connection']->fetchAll($q->getSql(), $q->getParams());
     }
 }

 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();
     }
     $this->assertEqual($q->getSql(), '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 testDqlUpdate()
    {
        $query = new Doctrine_Query($this->connection);
        $query->update('EnumTest2 u')
            ->set('u.status', '?', 'verified');

        $this->assertEqual($query->getSql(), 'UPDATE enum_test2 SET status = ?');   

        $query->execute();

        try {
            $query = new Doctrine_Query($this->connection);
            $ret = $query->query("FROM EnumTest2 WHERE EnumTest2.status = 'verified'");
            $this->assertEqual(count($ret), 1);
        } catch (Exception $e) {
            $this->fail();
        }
    }

 public function testTicket()
 {
     Doctrine_Manager::getInstance()->setAttribute('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->getSql();
     // 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('use_dql_callbacks', false);
 }

    public function testNonPortableFunctionsAreSupported()
    {
         $query = new Doctrine_Query();
         // we are using stored procedure here, so adjust portability settings
         $this->conn->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL ^ Doctrine::PORTABILITY_EXPR);

         $lat = '13.23';
         $lon = '33.23';
         $radius = '33';

         $query->select("l.*, i18n.*, GeoDistKM(l.lat, l.lon, $lat, $lon) distance")
              ->from('Location l, l.LocationI18n i18n')          
              ->where('l.id <> ? AND i18n.culture = ?', array(1, 'en'))
              ->having("distance < $radius")
              ->orderby('distance ASC')
              ->groupby('l.id')
              ->limit(5);

         $this->assertEqual($query->getSql(), "SELECT l.id AS l__id, l.lat AS l__lat, l.lon AS l__lon, l2.name AS l2__name, l2.id AS l2__id, l2.culture AS l2__culture, GeoDistKM(l.lat, l.lon, 13.23, 33.23) AS l__0 FROM location l LEFT JOIN location_i18n l2 ON l.id = l2.id WHERE l.id IN (SELECT DISTINCT l3.id FROM location l3 LEFT JOIN location_i18n l4 ON l3.id = l4.id WHERE (l3.id <> ? AND l4.culture = ?) GROUP BY l3.id HAVING l__0 < 33 ORDER BY l__0 ASC LIMIT 5) AND (l.id <> ? AND l2.culture = ?) GROUP BY l.id HAVING l__0 < 33 ORDER BY l__0 ASC");

         $this->conn->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL);
    }

    public function testEqualNestRelationsQuerying()
    {
        $this->connection->clear();

        $q = new Doctrine_Query();

        $q->from('NestTest n')->innerJoin('n.Relatives r')->where('n.id = 1');

        $n = $q->execute();

        $this->assertEqual($q->getSql(), 'SELECT n.id AS n__id, n.name AS n__name, n2.id AS n2__id, n2.name AS n2__name FROM nest_test n INNER JOIN nest_reference n3 ON n.id = n3.child_id OR n.id = n3.parent_id INNER JOIN nest_test n2 ON (n2.id = n3.parent_id OR n2.id = n3.child_id) AND n2.id != n.id WHERE n.id = 1');

        $this->assertEqual($n[0]->Relatives->count(), 5);
    }

 /**
 * Ticket #1038
  */
    public function testLimitOffsetLimitSubqueriesForOracleWithGroupByOrderBy()
    {
        $this->dbh = new Doctrine_Adapter_Mock('oracle');
        $conn = $this->manager->openConnection($this->dbh);
        $q = new Doctrine_Query($conn);
        // The orderBy(p.id) will force p.id to be added to the SELECT part of the 
        // SELECT DISTINCT subquery because that is required by oracle. This, however,
        // can result in duplicated primary keys that would cause incorrect ROWNUM calculations,
        // hence an additional subquery used to filter out the primary keys is added. 
        $q->from('User u')->innerJoin('u.Phonenumber p')
                ->groupBy('u.name') // !
                ->orderBy('p.id') // !!
                ->limit(5)->offset(2);
        $correctSql = "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 "
                    . "FROM entity e "
                    . "INNER JOIN phonenumber p ON e.id = p.entity_id "
                    . "WHERE e.id IN ("
                        . "SELECT b.id FROM ("
                            . "SELECT a.*, ROWNUM AS doctrine_rownum "
                                  . "FROM ("
                                      . "SELECT doctrine_subquery_alias.id FROM ("
                                          . "SELECT e2.id, p2.id "
                                          . "FROM entity e2 "
                                          . "INNER JOIN phonenumber p2 ON e2.id = p2.entity_id "
                                          . "WHERE (e2.type = 0) GROUP BY e2.name ORDER BY p2.id"
                                      . ") doctrine_subquery_alias GROUP BY doctrine_subquery_alias.id ORDER BY MIN(ROWNUM)"
                                  . ") a"
                              . " ) b "
                              . "WHERE doctrine_rownum BETWEEN 3 AND 7"
                          . ") AND (e.type = 0) GROUP BY e.name ORDER BY p.id";
                         
              $this->assertEqual($q->getSql(), $correctSql);
          }

 public function testStringColumnInheritance()
 {
     $q = new Doctrine_Query();
     $q->select('g.name')->from('Group g');
     $this->assertEqual($q->getSql(), "SELECT e.id AS e__id, e.name AS e__name FROM entity e WHERE (e.type = 1)");
 }

    public function testReferenfingParentColumnsUsesProperAliases()
    {
        $q = new Doctrine_Query();
        $q->from('CTITest c')->where("c.name = 'Jack'");

        $this->assertEqual($q->getSql(), "SELECT c.id AS c__id, c3.added AS c__added, c2.name AS c__name, c2.verified AS c__verified, c.age AS c__age FROM c_t_i_test_parent4 c LEFT JOIN c_t_i_test_parent2 c2 ON c.id = c2.id LEFT JOIN c_t_i_test_parent3 c3 ON c.id = c3.id WHERE c2.name = 'Jack'");

        $q = new Doctrine_Query();
        $q->from('CTITest c')->where("name = 'Jack'");

        $this->assertEqual($q->getSql(), "SELECT c.id AS c__id, c3.added AS c__added, c2.name AS c__name, c2.verified AS c__verified, c.age AS c__age FROM c_t_i_test_parent4 c LEFT JOIN c_t_i_test_parent2 c2 ON c.id = c2.id LEFT JOIN c_t_i_test_parent3 c3 ON c.id = c3.id WHERE c2.name = 'Jack'");
    }

    public function testAggregateFunctionParser4()
    {
        $q = new Doctrine_Query();
        $q->select('CONCAT(i.price, i.quantity)')->from('QueryTest_Item i');

        $this->assertEqual($q->getSql(), 'SELECT CONCAT(q.price, q.quantity) AS q__0 FROM query_test__item q');
    }