added query grouping to Active Record

this is a feature that has been lacking for a very long time. lots of people complained
about it over the years, but it never got added so you'd have to resort to handcrafted
queries when you needed this feature.

This is a port of code from DataMapper, in use since CI 1.6.

1 files changed, 186 insertions, 72 deletions

diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php
index 530b44e09..b7f43945c 100644
--- a/system/database/DB_active_rec.php
+++ b/system/database/DB_active_rec.php
@@ -5,9 +5,9 @@
  * An open source application development framework for PHP 5.1.6 or newer
  *
  * NOTICE OF LICENSE
- * 
+ *
  * Licensed under the Open Software License version 3.0
- * 
+ *
  * This source file is subject to the Open Software License (OSL 3.0) that is
  * bundled with this package in the files license.txt / license.rst.  It is
  * also available through the world wide web at this URL:
@@ -40,42 +40,44 @@
  */
 class CI_DB_active_record extends CI_DB_driver {
 
-	protected $return_delete_sql	= FALSE;
-	protected $reset_delete_data	= FALSE;
-	
-	protected $ar_select			= array();
-	protected $ar_distinct			= FALSE;
-	protected $ar_from				= array();
-	protected $ar_join				= array();
-	protected $ar_where				= array();
-	protected $ar_like				= array();
-	protected $ar_groupby			= array();
-	protected $ar_having			= array();
-	protected $ar_keys				= array();
-	protected $ar_limit				= FALSE;
-	protected $ar_offset			= FALSE;
-	protected $ar_order				= FALSE;
-	protected $ar_orderby			= array();
-	protected $ar_set				= array();
-	protected $ar_wherein			= array();
-	protected $ar_aliased_tables	= array();
-	protected $ar_store_array		= array();
+	protected $return_delete_sql		= FALSE;
+	protected $reset_delete_data		= FALSE;
+
+	protected $ar_select				= array();
+	protected $ar_distinct				= FALSE;
+	protected $ar_from					= array();
+	protected $ar_join					= array();
+	protected $ar_where					= array();
+	protected $ar_like					= array();
+	protected $ar_groupby				= array();
+	protected $ar_having				= array();
+	protected $ar_keys					= array();
+	protected $ar_limit					= FALSE;
+	protected $ar_offset				= FALSE;
+	protected $ar_order					= FALSE;
+	protected $ar_orderby				= array();
+	protected $ar_set					= array();
+	protected $ar_wherein				= array();
+	protected $ar_aliased_tables		= array();
+	protected $ar_store_array			= array();
+	protected $ar_where_group_started	= FALSE;
+	protected $ar_where_group_count		= 0;
 
 	// Active Record Caching variables
-	protected $ar_caching			= FALSE;
-	protected $ar_cache_exists		= array();
-	protected $ar_cache_select		= array();
-	protected $ar_cache_from		= array();
-	protected $ar_cache_join		= array();
-	protected $ar_cache_where		= array();
-	protected $ar_cache_like		= array();
-	protected $ar_cache_groupby		= array();
-	protected $ar_cache_having		= array();
-	protected $ar_cache_orderby		= array();
-	protected $ar_cache_set			= array();
-	
-	protected $ar_no_escape 		= array();
-	protected $ar_cache_no_escape	= array();
+	protected $ar_caching				= FALSE;
+	protected $ar_cache_exists			= array();
+	protected $ar_cache_select			= array();
+	protected $ar_cache_from			= array();
+	protected $ar_cache_join			= array();
+	protected $ar_cache_where			= array();
+	protected $ar_cache_like			= array();
+	protected $ar_cache_groupby			= array();
+	protected $ar_cache_having			= array();
+	protected $ar_cache_orderby			= array();
+	protected $ar_cache_set				= array();
+
+	protected $ar_no_escape 			= array();
+	protected $ar_cache_no_escape		= array();
 
 	// --------------------------------------------------------------------
 
@@ -412,6 +414,8 @@ class CI_DB_active_record extends CI_DB_driver {
 	 */
 	protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL)
 	{
+		$type = $this->_group_get_type($type);
+
 		if ( ! is_array($key))
 		{
 			$key = array($key => $value);
@@ -441,7 +445,7 @@ class CI_DB_active_record extends CI_DB_driver {
 
 					$v = ' '.$this->escape($v);
 				}
-				
+
 				if ( ! $this->_has_operator($k))
 				{
 					$k .= ' = ';
@@ -553,6 +557,8 @@ class CI_DB_active_record extends CI_DB_driver {
 			return;
 		}
 
+		$type = $this->_group_get_type($type);
+
 		if ( ! is_array($values))
 		{
 			$values = array($values);
@@ -663,6 +669,8 @@ class CI_DB_active_record extends CI_DB_driver {
 	 */
 	protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '')
 	{
+		$type = $this->_group_get_type($type);
+
 		if ( ! is_array($field))
 		{
 			$field = array($field => $match);
@@ -713,6 +721,112 @@ class CI_DB_active_record extends CI_DB_driver {
 	// --------------------------------------------------------------------
 
 	/**
+	 * Starts a query group.
+	 *
+	 * @param	string (Internal use only)
+	 * @param	string (Internal use only)
+	 * @return	object
+	 */
+	public function group_start($not = '', $type = 'AND ')
+	{
+		$type = $this->_group_get_type($type);
+
+		$this->ar_where_group_started = TRUE;
+
+		$prefix = (count($this->ar_where) == 0 AND count($this->ar_cache_where) == 0) ? '' : $type;
+		$value =  $prefix . $not . str_repeat(' ', ++$this->ar_where_group_count) . ' (';
+
+		$this->ar_where[] = $value;
+		if ($this->ar_caching)
+		{
+			$this->ar_cache_where[] = $value;
+		}
+
+		return $this;
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * Starts a query group, but ORs the group
+	 *
+	 * @return	object
+	 */
+	public function or_group_start()
+	{
+		return $this->group_start('', 'OR ');
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * Starts a query group, but NOTs the group
+	 *
+	 * @return	object
+	 */
+	public function not_group_start()
+	{
+		return $this->group_start('NOT ', 'AND ');
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * Starts a query group, but OR NOTs the group
+	 *
+	 * @return	object
+	 */
+	public function or_not_group_start()
+	{
+		return $this->group_start('NOT ', 'OR ');
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * Ends a query group
+	 *
+	 * @return	object
+	 */
+	public function group_end()
+	{
+		$value = str_repeat(' ', $this->ar_where_group_count--) . ')';
+
+		$this->ar_where[] = $value;
+		if ($this->ar_caching)
+		{
+			$this->ar_cache_where[] = $value;
+		}
+
+		$this->ar_where_group_started = FALSE;
+
+		return $this;
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * Group_get_type
+	 *
+	 * Called by group_start(), _like(), _where() and _where_in()
+	 *
+	 * @param	string
+	 * @return	string
+	 */
+	protected function _group_get_type($type)
+	{
+		if ($this->ar_where_group_started)
+		{
+			$type = '';
+			$this->ar_where_group_started = FALSE;
+		}
+
+		return $type;
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
 	 * GROUP BY
 	 *
 	 * @param	string
@@ -950,7 +1064,7 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		return $this;
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -963,24 +1077,24 @@ class CI_DB_active_record extends CI_DB_driver {
 	 * @param	boolean	TRUE: resets AR values; FALSE: leave AR vaules alone
 	 * @return	string
 	 */
-	public function get_compiled_select($table = '', $reset = TRUE) 
+	public function get_compiled_select($table = '', $reset = TRUE)
 	{
 		if ($table != '')
 		{
 			$this->_track_aliases($table);
 			$this->from($table);
 		}
-		
+
 		$select =  $this->_compile_select();
-		
+
 		if ($reset === TRUE)
 		{
 			$this->_reset_select();
 		}
-		
+
 		return $select;
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1197,7 +1311,7 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		return $this;
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1211,25 +1325,25 @@ class CI_DB_active_record extends CI_DB_driver {
 	 * @return	string
 	 */
 	public function get_compiled_insert($table = '', $reset = TRUE)
-	{	
+	{
 		if ($this->_validate_insert($table) === FALSE)
 		{
 			return FALSE;
 		}
-		
+
 		$sql = $this->_insert(
 			$this->_protect_identifiers(
 				$this->ar_from[0], TRUE, NULL, FALSE
 			),
-			array_keys($this->ar_set), 
+			array_keys($this->ar_set),
 			array_values($this->ar_set)
 		);
-		
+
 		if ($reset === TRUE)
 		{
 			$this->_reset_write();
 		}
-		
+
 		return $sql;
 	}
 
@@ -1251,24 +1365,24 @@ class CI_DB_active_record extends CI_DB_driver {
 		{
 			$this->set($set);
 		}
-		
+
 		if ($this->_validate_insert($table) === FALSE)
 		{
 			return FALSE;
 		}
-		
+
 		$sql = $this->_insert(
 			$this->_protect_identifiers(
 				$this->ar_from[0], TRUE, NULL, FALSE
-			), 
-			array_keys($this->ar_set), 
+			),
+			array_keys($this->ar_set),
 			array_values($this->ar_set)
 		);
 
 		$this->_reset_write();
 		return $this->query($sql);
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1282,7 +1396,7 @@ class CI_DB_active_record extends CI_DB_driver {
 	 * @param	string	the table to insert data into
 	 * @return	string
 	 */
-	protected function _validate_insert($table = '') 
+	protected function _validate_insert($table = '')
 	{
 		if (count($this->ar_set) == 0)
 		{
@@ -1308,7 +1422,7 @@ class CI_DB_active_record extends CI_DB_driver {
 		{
 			$this->ar_from[0] = $table;
 		}
-		
+
 		return TRUE;
 	}
 
@@ -1358,7 +1472,7 @@ class CI_DB_active_record extends CI_DB_driver {
 		$this->_reset_write();
 		return $this->query($sql);
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1375,22 +1489,22 @@ class CI_DB_active_record extends CI_DB_driver {
 	{
 		// Combine any cached components with the current statements
 		$this->_merge_cache();
-	
+
 		if ($this->_validate_update($table) === FALSE)
 		{
 			return FALSE;
 		}
-		
+
 		$sql = $this->_update($this->_protect_identifiers($this->ar_from[0], TRUE, NULL, FALSE), $this->ar_set, $this->ar_where, $this->ar_orderby, $this->ar_limit);
-		
+
 		if ($reset === TRUE)
 		{
 			$this->_reset_write();
 		}
-		
+
 		return $sql;
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1433,7 +1547,7 @@ class CI_DB_active_record extends CI_DB_driver {
 		$this->_reset_write();
 		return $this->query($sql);
 	}
-		
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1474,7 +1588,7 @@ class CI_DB_active_record extends CI_DB_driver {
 			$this->ar_from[0] = $table;
 		}
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1673,7 +1787,7 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		return $this->query($sql);
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1693,7 +1807,7 @@ class CI_DB_active_record extends CI_DB_driver {
 		$this->return_delete_sql = FALSE;
 		return $sql;
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1766,7 +1880,7 @@ class CI_DB_active_record extends CI_DB_driver {
 		{
 			$this->_reset_write();
 		}
-		
+
 		if ($this->return_delete_sql === true)
 		{
 			return $sql;
@@ -1774,7 +1888,7 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		return $this->query($sql);
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1854,7 +1968,7 @@ class CI_DB_active_record extends CI_DB_driver {
 			}
 		}
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -2160,12 +2274,12 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		$this->ar_no_escape = $this->ar_cache_no_escape;
 	}
-	
+
 	// --------------------------------------------------------------------
 
 	/**
 	 * Reset Active Record values.
-	 * 
+	 *
 	 * Publicly-visible method to reset the AR values.
 	 *
 	 * @access	public
@@ -2253,4 +2367,4 @@ class CI_DB_active_record extends CI_DB_driver {
 }
 
 /* End of file DB_active_rec.php */
-/* Location: ./system/database/DB_active_rec.php */
\ No newline at end of file
+/* Location: ./system/database/DB_active_rec.php */