Merge pull request #847 from WanWizard/feature/ar_parentheses

added query grouping to Active Record

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 */