Merge pull request #5017 from tianhe1986/develop_having_in

DB_query_builder: Adding having_in(), or_having_in(), having_not_in() and or_having_not_in()

3 files changed, 210 insertions, 15 deletions

diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php
index fe4cf6993..1daee99a8 100644
--- a/system/database/DB_query_builder.php
+++ b/system/database/DB_query_builder.php
@@ -725,7 +725,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 */
 	public function where_in($key = NULL, $values = NULL, $escape = NULL)
 	{
-		return $this->_where_in($key, $values, FALSE, 'AND ', $escape);
+		return $this->_wh_in('qb_where', $key, $values, FALSE, 'AND ', $escape);
 	}
 
 	// --------------------------------------------------------------------
@@ -743,7 +743,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 */
 	public function or_where_in($key = NULL, $values = NULL, $escape = NULL)
 	{
-		return $this->_where_in($key, $values, FALSE, 'OR ', $escape);
+		return $this->_wh_in('qb_where', $key, $values, FALSE, 'OR ', $escape);
 	}
 
 	// --------------------------------------------------------------------
@@ -761,7 +761,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 */
 	public function where_not_in($key = NULL, $values = NULL, $escape = NULL)
 	{
-		return $this->_where_in($key, $values, TRUE, 'AND ', $escape);
+		return $this->_wh_in('qb_where', $key, $values, TRUE, 'AND ', $escape);
 	}
 
 	// --------------------------------------------------------------------
@@ -779,19 +779,96 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 */
 	public function or_where_not_in($key = NULL, $values = NULL, $escape = NULL)
 	{
-		return $this->_where_in($key, $values, TRUE, 'OR ', $escape);
+		return $this->_wh_in('qb_where', $key, $values, TRUE, 'OR ', $escape);
 	}
 
 	// --------------------------------------------------------------------
 
 	/**
-	 * Internal WHERE IN
+	 * HAVING IN
+	 *
+	 * Generates a HAVING field IN('item', 'item') SQL query,
+	 * joined with 'AND' if appropriate.
+	 *
+	 * @param	string	$key	The field to search
+	 * @param	array	$values	The values searched on
+	 * @param	bool	$escape
+	 * @return	CI_DB_query_builder
+	 */
+	public function having_in($key = NULL, $values = NULL, $escape = NULL)
+	{
+		return $this->_wh_in('qb_having', $key, $values, FALSE, 'AND ', $escape);
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * OR HAVING IN
+	 *
+	 * Generates a HAVING field IN('item', 'item') SQL query,
+	 * joined with 'OR' if appropriate.
+	 *
+	 * @param	string	$key	The field to search
+	 * @param	array	$values	The values searched on
+	 * @param	bool	$escape
+	 * @return	CI_DB_query_builder
+	 */
+	public function or_having_in($key = NULL, $values = NULL, $escape = NULL)
+	{
+		return $this->_wh_in('qb_having', $key, $values, FALSE, 'OR ', $escape);
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * HAVING NOT IN
+	 *
+	 * Generates a HAVING field NOT IN('item', 'item') SQL query,
+	 * joined with 'AND' if appropriate.
+	 *
+	 * @param	string	$key	The field to search
+	 * @param	array	$values	The values searched on
+	 * @param	bool	$escape
+	 * @return	CI_DB_query_builder
+	 */
+	public function having_not_in($key = NULL, $values = NULL, $escape = NULL)
+	{
+		return $this->_wh_in('qb_having', $key, $values, TRUE, 'AND ', $escape);
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * OR HAVING NOT IN
+	 *
+	 * Generates a HAVING field NOT IN('item', 'item') SQL query,
+	 * joined with 'OR' if appropriate.
+	 *
+	 * @param	string	$key	The field to search
+	 * @param	array	$values	The values searched on
+	 * @param	bool	$escape
+	 * @return	CI_DB_query_builder
+	 */
+	public function or_having_not_in($key = NULL, $values = NULL, $escape = NULL)
+	{
+		return $this->_wh_in('qb_having', $key, $values, TRUE, 'OR ', $escape);
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * Internal WHERE/HAVING IN
 	 *
 	 * @used-by	where_in()
 	 * @used-by	or_where_in()
 	 * @used-by	where_not_in()
 	 * @used-by	or_where_not_in()
+	 * @used-by	having_in()
+	 * @used-by	or_having_in()
+	 * @used-by	having_not_in()
+	 * @used-by	or_having_not_in()
 	 *
+	 * @param	string	$qb_key	'qb_where' or 'qb_having'
 	 * @param	string	$key	The field to search
 	 * @param	array	$values	The values searched on
 	 * @param	bool	$not	If the statement would be IN or NOT IN
@@ -799,8 +876,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 * @param	bool	$escape
 	 * @return	CI_DB_query_builder
 	 */
-	protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL)
+	protected function _wh_in($qb_key, $key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL)
 	{
+		$qb_cache_key = ($qb_key === 'qb_having') ? 'qb_cache_having' : 'qb_cache_where';
+
 		if ($key === NULL OR $values === NULL)
 		{
 			return $this;
@@ -817,31 +896,31 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 
 		if ($escape === TRUE)
 		{
-			$where_in = array();
+			$wh_in = array();
 			foreach ($values as $value)
 			{
-				$where_in[] = $this->escape($value);
+				$wh_in[] = $this->escape($value);
 			}
 		}
 		else
 		{
-			$where_in = array_values($values);
+			$wh_in = array_values($values);
 		}
 
-		$prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0)
+		$prefix = (count($this->$qb_key) === 0 && count($this->$qb_cache_key) === 0)
 			? $this->_group_get_type('')
 			: $this->_group_get_type($type);
 
-		$where_in = array(
-			'condition' => $prefix.$key.$not.' IN('.implode(', ', $where_in).')',
+		$wh_in = array(
+			'condition' => $prefix.$key.$not.' IN('.implode(', ', $wh_in).')',
 			'escape' => $escape
 		);
 
-		$this->qb_where[] = $where_in;
+		$this->{$qb_key}[] = $wh_in;
 		if ($this->qb_caching === TRUE)
 		{
-			$this->qb_cache_where[] = $where_in;
-			$this->qb_cache_exists[] = 'where';
+			$this->{$qb_cache_key}[] = $wh_in;
+			$this->qb_cache_exists[] = substr($qb_key, 3);
 		}
 
 		return $this;
diff --git a/tests/codeigniter/database/query_builder/group_test.php b/tests/codeigniter/database/query_builder/group_test.php
index 5249f7c87..51266e858 100644
--- a/tests/codeigniter/database/query_builder/group_test.php
+++ b/tests/codeigniter/database/query_builder/group_test.php
@@ -48,4 +48,74 @@ class Group_test extends CI_TestCase {
 		$this->assertEquals(2, count($jobs));
 	}
 
+	// ------------------------------------------------------------------------
+
+	/**
+	 * @see ./mocks/schema/skeleton.php
+	 */
+	public function test_having_in()
+	{
+		$jobs = $this->db->select('name')
+				->from('job')
+				->group_by('name')
+				->having_in('SUM(id)', array(1, 2, 5))
+				->get()
+				->result_array();
+
+		$this->assertEquals(2, count($jobs));
+	}
+
+	// ------------------------------------------------------------------------
+
+	/**
+	 * @see ./mocks/schema/skeleton.php
+	 */
+	public function test_or_having_in()
+	{
+		$jobs = $this->db->select('name')
+				->from('job')
+				->group_by('name')
+				->or_having_in('SUM(id)', array(1, 5))
+				->or_having_in('SUM(id)', array(2, 6))
+				->get()
+				->result_array();
+
+		$this->assertEquals(2, count($jobs));
+	}
+
+	// ------------------------------------------------------------------------
+
+	/**
+	 * @see ./mocks/schema/skeleton.php
+	 */
+	public function test_having_not_in()
+	{
+		$jobs = $this->db->select('name')
+				->from('job')
+				->group_by('name')
+				->having_not_in('SUM(id)', array(3, 6))
+				->get()
+				->result_array();
+
+		$this->assertEquals(3, count($jobs));
+	}
+
+	// ------------------------------------------------------------------------
+
+	/**
+	 * @see ./mocks/schema/skeleton.php
+	 */
+	public function test_or_having_not_in()
+	{
+		$jobs = $this->db->select('name')
+				->from('job')
+				->group_by('name')
+				->or_having_not_in('SUM(id)', array(1, 2, 3))
+				->or_having_not_in('SUM(id)', array(1, 3, 4))
+				->get()
+				->result_array();
+
+		$this->assertEquals(2, count($jobs));
+	}
+
 }
\ No newline at end of file
diff --git a/user_guide_src/source/database/query_builder.rst b/user_guide_src/source/database/query_builder.rst
index 3135f76da..2d954c7a8 100644
--- a/user_guide_src/source/database/query_builder.rst
+++ b/user_guide_src/source/database/query_builder.rst
@@ -452,6 +452,52 @@ setting it to FALSE.
 
 Identical to having(), only separates multiple clauses with "OR".
 
+**$this->db->having_in()**
+
+Generates a HAVING field IN ('item', 'item') SQL query joined with AND if
+appropriate
+
+::
+
+	$names = array('Frank', 'Todd', 'James');
+	$this->db->having_in('username', $names);
+	// Produces: HAVING username IN ('Frank', 'Todd', 'James')
+
+
+**$this->db->or_having_in()**
+
+Generates a HAVING field IN ('item', 'item') SQL query joined with OR if
+appropriate
+
+::
+
+	$names = array('Frank', 'Todd', 'James');
+	$this->db->or_having_in('username', $names);
+	// Produces: OR username IN ('Frank', 'Todd', 'James')
+
+**$this->db->having_not_in()**
+
+Generates a HAVING field NOT IN ('item', 'item') SQL query joined with
+AND if appropriate
+
+::
+
+	$names = array('Frank', 'Todd', 'James');
+	$this->db->having_not_in('username', $names);
+	// Produces: HAVING username NOT IN ('Frank', 'Todd', 'James')
+
+
+**$this->db->or_having_not_in()**
+
+Generates a HAVING field NOT IN ('item', 'item') SQL query joined with OR
+if appropriate
+
+::
+
+	$names = array('Frank', 'Todd', 'James');
+	$this->db->or_having_not_in('username', $names);
+	// Produces: OR username NOT IN ('Frank', 'Todd', 'James')
+
 ****************
 Ordering results
 ****************