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.
diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php
index 530b44e..b7f4394 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 @@
*/
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 @@
$v = ' '.$this->escape($v);
}
-
+
if ( ! $this->_has_operator($k))
{
$k .= ' = ';
@@ -553,6 +557,8 @@
return;
}
+ $type = $this->_group_get_type($type);
+
if ( ! is_array($values))
{
$values = array($values);
@@ -663,6 +669,8 @@
*/
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 @@
// --------------------------------------------------------------------
/**
+ * 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 @@
return $this;
}
-
+
// --------------------------------------------------------------------
/**
@@ -963,24 +1077,24 @@
* @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 @@
return $this;
}
-
+
// --------------------------------------------------------------------
/**
@@ -1211,25 +1325,25 @@
* @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 @@
{
$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 @@
* @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 @@
{
$this->ar_from[0] = $table;
}
-
+
return TRUE;
}
@@ -1358,7 +1472,7 @@
$this->_reset_write();
return $this->query($sql);
}
-
+
// --------------------------------------------------------------------
/**
@@ -1375,22 +1489,22 @@
{
// 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 @@
$this->_reset_write();
return $this->query($sql);
}
-
+
// --------------------------------------------------------------------
/**
@@ -1474,7 +1588,7 @@
$this->ar_from[0] = $table;
}
}
-
+
// --------------------------------------------------------------------
/**
@@ -1673,7 +1787,7 @@
return $this->query($sql);
}
-
+
// --------------------------------------------------------------------
/**
@@ -1693,7 +1807,7 @@
$this->return_delete_sql = FALSE;
return $sql;
}
-
+
// --------------------------------------------------------------------
/**
@@ -1766,7 +1880,7 @@
{
$this->_reset_write();
}
-
+
if ($this->return_delete_sql === true)
{
return $sql;
@@ -1774,7 +1888,7 @@
return $this->query($sql);
}
-
+
// --------------------------------------------------------------------
/**
@@ -1854,7 +1968,7 @@
}
}
}
-
+
// --------------------------------------------------------------------
/**
@@ -2160,12 +2274,12 @@
$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 @@
}
/* 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 */