Doctrine

Timestamp:

08/27/08 08:40:39 (10 months ago)

Author:

guilhermeblanco

Message:

fixes #1331. Added andWhere, andWhereIn, andWhereNotIn, orWhere, orWhereIn, orWhereNotIn support to 1.0. Added some coverage to this support.

Location:

branches/1.0

Files:

• lib/Doctrine/Query.php (modified) (6 diffs)
• lib/Doctrine/Query/Abstract.php (modified) (8 diffs)
• tests/QueryTestCase.php (modified) (1 diff)

branches/1.0/lib/Doctrine/Query.php

@@ -1103 +1103 @@
         // apply inheritance to WHERE part
         if ( ! empty($string)) {
+            if (count($this->_sqlParts['where']) > 0) {
+                $this->_sqlParts['where'][] = 'AND';
+            }
+
             if (substr($string, 0, 1) === '(' && substr($string, -1) === ')') {
                 $this->_sqlParts['where'][] = $string;
@@ -1133 +1137 @@
                 // only append the subquery if it actually contains something
                 if ($subquery !== '') {
+                    if (count($this->_sqlParts['where']) > 0) {
+                        array_unshift($this->_sqlParts['where'], 'AND');
+                    }
+
                     array_unshift($this->_sqlParts['where'], $this->_conn->quoteIdentifier($field) . ' IN (' . $subquery . ')');
                 }
@@ -1139 +1147 @@
         }
 
-        $q .= ( ! empty($this->_sqlParts['where']))?   ' WHERE '    . implode(' AND ', $this->_sqlParts['where']) : '';
+        $q .= ( ! empty($this->_sqlParts['where']))?   ' WHERE '    . implode(' ', $this->_sqlParts['where']) : '';
         $q .= ( ! empty($this->_sqlParts['groupby']))? ' GROUP BY ' . implode(', ', $this->_sqlParts['groupby'])  : '';
         $q .= ( ! empty($this->_sqlParts['having']))?  ' HAVING '   . implode(' AND ', $this->_sqlParts['having']): '';
@@ -1152 +1160 @@
         // return to the previous state
         if ( ! empty($string)) {
+            // We need to double pop if > 2
+            if (count($this->_sqlParts['where']) > 2) {
+                array_pop($this->_sqlParts['where']);
+            }
+
             array_pop($this->_sqlParts['where']);
         }
+
         if ($needsSubQuery) {
+            // We need to double shift if > 2
+            if (count($this->_sqlParts['where']) > 2) {
+                array_shift($this->_sqlParts['where']);
+            }
+
             array_shift($this->_sqlParts['where']);
         }
+
         $this->_sql = $q;
 
@@ -1241 +1261 @@
 
         // all conditions must be preserved in subquery
-        $subquery .= ( ! empty($this->_sqlParts['where']))?   ' WHERE '    . implode(' AND ', $this->_sqlParts['where'])  : '';
+        $subquery .= ( ! empty($this->_sqlParts['where']))?   ' WHERE '    . implode(' ', $this->_sqlParts['where'])  : '';
         $subquery .= ( ! empty($this->_sqlParts['groupby']))? ' GROUP BY ' . implode(', ', $this->_sqlParts['groupby'])   : '';
         $subquery .= ( ! empty($this->_sqlParts['having']))?  ' HAVING '   . implode(' AND ', $this->_sqlParts['having']) : '';
@@ -1798 +1818 @@
 
         if ( ! empty($string)) {
+            if (count($where) > 0) {
+                $where[] = 'AND';
+            }
+            
             $where[] = $string;
         }
 
         // append conditions
-        $q .= ( ! empty($where)) ?  ' WHERE '  . implode(' AND ', $where) : '';
+        $q .= ( ! empty($where)) ?  ' WHERE '  . implode(' ', $where) : '';
 
         if ( ! empty($groupby)) {

branches/1.0/lib/Doctrine/Query/Abstract.php

@@ -339 +339 @@
         $q .= ( ! empty($this->_dqlParts['select']))?  'SELECT '    . implode(', ', $this->_dqlParts['select']) : '';
         $q .= ( ! empty($this->_dqlParts['from']))?    ' FROM '     . implode(' ', $this->_dqlParts['from']) : '';
-        $q .= ( ! empty($this->_dqlParts['where']))?   ' WHERE '    . implode(' AND ', $this->_dqlParts['where']) : '';
+        $q .= ( ! empty($this->_dqlParts['where']))?   ' WHERE '    . implode(' ', $this->_dqlParts['where']) : '';
         $q .= ( ! empty($this->_dqlParts['groupby']))? ' GROUP BY ' . implode(', ', $this->_dqlParts['groupby']) : '';
         $q .= ( ! empty($this->_dqlParts['having']))?  ' HAVING '   . implode(' AND ', $this->_dqlParts['having']) : '';
@@ -1191 +1191 @@
     public function addWhere($where, $params = array())
     {
+        return $this->andWhere($where, $params);
+    }
+    
+    
+    public function andWhere($where, $params = array())
+    {
         if (is_array($params)) {
             $this->_params['where'] = array_merge($this->_params['where'], $params);
@@ -1196 +1202 @@
             $this->_params['where'][] = $params;
         }
+        
+        if ($this->_hasDqlQueryPart('where')) {
+            $this->_addDqlQueryPart('where', 'AND', true);
+        }
+
         return $this->_addDqlQueryPart('where', $where, true);
     }
+    
+    
+    public function orWhere($where, $params = array())
+    {
+        if (is_array($params)) {
+            $this->_params['where'] = array_merge($this->_params['where'], $params);
+        } else {
+            $this->_params['where'][] = $params;
+        }
+        
+        if ($this->_hasDqlQueryPart('where')) {
+            $this->_addDqlQueryPart('where', 'OR', true);
+        }
+
+        return $this->_addDqlQueryPart('where', $where, true);
+    }
+
 
     /**
@@ -1209 +1237 @@
      */
     public function whereIn($expr, $params = array(), $not = false)
+    {
+        return $this->andWhereIn($expr, $params, $not);
+    }
+
+
+    /**
+     * Adds IN condition to the query WHERE part
+     *
+     * @param string $expr The operand of the IN
+     * @param mixed $params An array of parameters or a simple scalar
+     * @param boolean $not Whether or not to use NOT in front of IN 
+     * @return Doctrine_Query
+     */
+    public function andWhereIn($expr, $params = array(), $not = false)
+    {
+        if ($this->_hasDqlQueryPart('where')) {
+            $this->_addDqlQueryPart('where', 'AND', true);
+        }
+
+        return $this->_addDqlQueryPart('where', $this->_processWhereIn($expr, $params, $not), true);
+    }
+
+
+    /**
+     * Adds IN condition to the query WHERE part
+     *
+     * @param string $expr The operand of the IN
+     * @param mixed $params An array of parameters or a simple scalar
+     * @param boolean $not Whether or not to use NOT in front of IN
+     * @return Doctrine_Query
+     */
+    public function orWhereIn($expr, $params = array(), $not = false)
+    {
+        if ($this->_hasDqlQueryPart('where')) {
+            $this->_addDqlQueryPart('where', 'OR', true);
+        }
+
+        return $this->_addDqlQueryPart('where', $this->_processWhereIn($expr, $params, $not), true);
+    }
+    
+
+    /**
+     * @nodoc
+     */
+    protected function _processWhereIn($expr, $params = array(), $not = false)
     {
         $params = (array) $params;
@@ -1230 +1303 @@
         $this->_params['where'] = array_merge($this->_params['where'], $params);
 
-        $where = $expr . ($not === true ? ' NOT ':'') . ' IN (' . implode(', ', $a) . ')';
-
-        return $this->_addDqlQueryPart('where', $where, true);
-    }
+        return $expr . ($not === true ? ' NOT ':'') . ' IN (' . implode(', ', $a) . ')';
+    }
+
 
     /**
@@ -1246 +1318 @@
     {
         return $this->whereIn($expr, $params, true);
+    }
+    
+
+    /**
+     * Adds NOT IN condition to the query WHERE part
+     *
+     * @param string $expr The operand of the NOT IN
+     * @param mixed $params An array of parameters or a simple scalar
+     * @return Doctrine_Query
+     */
+    public function andWhereNotIn($expr, $params = array())
+    {
+        return $this->andWhereIn($expr, $params, true);
+    }
+    
+
+    /**
+     * Adds NOT IN condition to the query WHERE part
+     *
+     * @param string $expr The operand of the NOT IN
+     * @param mixed $params An array of parameters or a simple scalar
+     * @return Doctrine_Query
+     */
+    public function orWhereNotIn($expr, $params = array())
+    {
+        return $this->orWhereIn($expr, $params, true);
     }
 
@@ -1451 +1549 @@
     {
         $this->_params['where'] = array();
+
         if (is_array($params)) {
             $this->_params['where'] = $params;
@@ -1814 +1913 @@
         return $this->_conn;
     }
+    
+    /**
+     * Checks if there's at least one DQL part defined to the internal parts collection.
+     *
+     * @param string $queryPartName  The name of the query part.
+     * @return boolean
+     */
+    protected function _hasDqlQueryPart($queryPartName)
+    {
+        return count($this->_dqlParts[$queryPartName]) > 0;
+    }
+
 
     /**

branches/1.0/tests/QueryTestCase.php

@@ -176 +176 @@
         }
     }
+    
+    
+    public function testOrQuerySupport()
+    {
+        $q1 = Doctrine_Query::create()
+            ->select('u.id')
+            ->from('User u')
+            ->leftJoin('u.Phonenumber p')
+            ->where('u.name = ?')
+            ->orWhere('u.loginname = ?');
+            
+        $q2 = Doctrine_Query::create()
+            ->select('u.id')
+            ->from('User u')
+            ->leftJoin('u.Phonenumber p')
+            ->where('u.name = ? OR u.loginname = ?');
+
+        $this->assertEqual(
+            $q1->getSqlQuery(),
+            'SELECT e.id AS e__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id ' .
+            'WHERE e.name = ? OR e.loginname = ? AND (e.type = 0)'
+        );
+        
+        $items1 = $q1->execute(array('zYne', 'jwage'), Doctrine::HYDRATE_ARRAY);
+        $items2 = $q2->execute(array('zYne', 'jwage'), Doctrine::HYDRATE_ARRAY);
+
+        $this->assertEqual(count($items1), count($items2));
+        
+        $q1->free();
+        $q2->free();
+    }
+
+
+    public function testOrQuerySupport2()
+    {
+        $q1 = Doctrine_Query::create()
+            ->select('u.id')
+            ->from('User u')
+            ->leftJoin('u.Phonenumber p')
+            ->where('u.name = ?')
+            ->andWhere('u.loginname = ?')
+            ->orWhere('u.id = ?');
+            
+        $q2 = Doctrine_Query::create()
+            ->select('u.id')
+            ->from('User u')
+            ->leftJoin('u.Phonenumber p')
+            ->where('(u.name = ? AND u.loginname = ?) OR (u.id = ?)');
+
+        $this->assertEqual(
+            $q1->getSqlQuery(),
+            'SELECT e.id AS e__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id ' .
+            'WHERE e.name = ? AND e.loginname = ? OR e.id = ? AND (e.type = 0)'
+        );
+        
+        $items1 = $q1->execute(array('jon', 'jwage', 4), Doctrine::HYDRATE_ARRAY);
+        $items2 = $q2->execute(array('jon', 'jwage', 4), Doctrine::HYDRATE_ARRAY);
+
+        $this->assertEqual(count($items1), count($items2));
+
+        $q1->free();
+        $q2->free();
+    }
+    
+    
+    public function testOrQuerySupport3()
+    {
+        $q1 = Doctrine_Query::create()
+            ->select('u.id')
+            ->from('User u')
+            ->leftJoin('u.Phonenumber p')
+            ->where("u.name = 'jon'")
+            ->andWhere("u.loginname = 'jwage'")
+            ->orWhere("u.id = 4")
+            ->orWhere("u.id = 5")
+            ->andWhere("u.name LIKE 'Arnold%'");
+            
+        $q2 = Doctrine_Query::create()
+            ->select('u.id')
+            ->from('User u')
+            ->leftJoin('u.Phonenumber p')
+            ->where("((u.name = 'jon' AND u.loginname = 'jwage') OR (u.id = 4 OR (u.id = 5 AND u.name LIKE 'Arnold%')))");
+
+        $this->assertEqual(
+            $q1->getSqlQuery(),
+            "SELECT e.id AS e__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id " .
+            "WHERE e.name = 'jon' AND e.loginname = 'jwage' OR e.id = 4 OR e.id = 5 AND e.name LIKE 'Arnold%' AND (e.type = 0)"
+        );
+        
+        $items1 = $q1->execute(array(), Doctrine::HYDRATE_ARRAY);
+        $items2 = $q2->execute(array(), Doctrine::HYDRATE_ARRAY);
+
+        $this->assertEqual(count($items1), count($items2));
+
+        $q1->free();
+        $q2->free();
+    }
 }