Author: guilhermeblanco
Date: 2008-08-27 09:40:39 +0100 (Wed, 27 Aug 2008)
New Revision: 4848
Modified:
branches/1.0/lib/Doctrine/Query.php
branches/1.0/lib/Doctrine/Query/Abstract.php
branches/1.0/tests/QueryTestCase.php
Log:
fixes #1331. Added andWhere, andWhereIn, andWhereNotIn, orWhere, orWhereIn,
orWhereNotIn support to 1.0. Added some coverage to this support.
Modified: branches/1.0/lib/Doctrine/Query/Abstract.php
===================================================================
--- branches/1.0/lib/Doctrine/Query/Abstract.php 2008-08-27 06:46:28 UTC
(rev 4847)
+++ branches/1.0/lib/Doctrine/Query/Abstract.php 2008-08-27 08:40:39 UTC
(rev 4848)
@@ -338,7 +338,7 @@
$q = '';
$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']) : '';
$q .= ( ! empty($this->_dqlParts['orderby']))? ' ORDER BY ' .
implode(', ', $this->_dqlParts['orderby']) : '';
@@ -1190,14 +1190,42 @@
*/
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);
} else {
$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);
+ }
+
+
/**
* whereIn
* adds IN condition to the query WHERE part
@@ -1209,6 +1237,51 @@
*/
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;
// if there's no params, return (else we'll get a WHERE IN (), invalid
SQL)
@@ -1229,11 +1302,10 @@
$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) . ')';
}
+
/**
* whereNotIn
* adds NOT IN condition to the query WHERE part
@@ -1246,8 +1318,34 @@
{
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);
+ }
+
+ /**
* addGroupBy
* adds fields to the GROUP BY part of the query
*
@@ -1450,6 +1548,7 @@
public function where($where, $params = array())
{
$this->_params['where'] = array();
+
if (is_array($params)) {
$this->_params['where'] = $params;
} else {
@@ -1813,7 +1912,19 @@
{
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;
+ }
+
/**
* Adds a DQL part to the internal parts collection.
*
Modified: branches/1.0/lib/Doctrine/Query.php
===================================================================
--- branches/1.0/lib/Doctrine/Query.php 2008-08-27 06:46:28 UTC (rev 4847)
+++ branches/1.0/lib/Doctrine/Query.php 2008-08-27 08:40:39 UTC (rev 4848)
@@ -1102,6 +1102,10 @@
// 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;
} else {
@@ -1132,13 +1136,17 @@
// 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 . ')');
}
$modifyLimit = false;
}
- $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']): '';
$q .= ( ! empty($this->_sqlParts['orderby']))? ' ORDER BY ' .
implode(', ', $this->_sqlParts['orderby']) : '';
@@ -1151,11 +1159,23 @@
// 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;
return $q;
@@ -1240,7 +1260,7 @@
}
// 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']) : '';
$subquery .= ( ! empty($this->_sqlParts['orderby']))? ' ORDER BY ' .
implode(', ', $this->_sqlParts['orderby']) : '';
@@ -1797,11 +1817,15 @@
$string = $this->getInheritanceCondition($this->getRootAlias());
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)) {
// Maintain existing groupby
Modified: branches/1.0/tests/QueryTestCase.php
===================================================================
--- branches/1.0/tests/QueryTestCase.php 2008-08-27 06:46:28 UTC (rev
4847)
+++ branches/1.0/tests/QueryTestCase.php 2008-08-27 08:40:39 UTC (rev
4848)
@@ -175,6 +175,103 @@
$this->pass();
}
}
+
+
+ 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();
+ }
}
class MyQuery extends Doctrine_Query
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
