Merge branch 'develop' of git://github.com/EllisLab/CodeIgniter into develop
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 */
diff --git a/user_guide_src/source/database/active_record.rst b/user_guide_src/source/database/active_record.rst
index 1600f0b..c04e67d 100644
--- a/user_guide_src/source/database/active_record.rst
+++ b/user_guide_src/source/database/active_record.rst
@@ -45,7 +45,7 @@
$query, which can be used to show the results::
$query = $this->db->get('mytable');
-
+
foreach ($query->result() as $row)
{
echo $row->title;
@@ -57,31 +57,31 @@
$this->db->get_compiled_select()
================================
-Compiles the selection query just like `$this->db->get()`_ but does not *run*
+Compiles the selection query just like `$this->db->get()`_ but does not *run*
the query. This method simply returns the SQL query as a string.
Example::
$sql = $this->db->get_compiled_select('mytable');
echo $sql;
-
+
// Produces string: SELECT * FROM mytable
-
-The second parameter enables you to set whether or not the active record query
+
+The second parameter enables you to set whether or not the active record query
will be reset (by default it will be—just like `$this->db->get()`)::
echo $this->db->limit(10,20)->get_compiled_select('mytable', FALSE);
- // Produces string: SELECT * FROM mytable LIMIT 20, 10
+ // Produces string: SELECT * FROM mytable LIMIT 20, 10
// (in MySQL. Other databases have slightly different syntax)
-
+
echo $this->db->select('title, content, date')->get_compiled_select();
// Produces string: SELECT title, content, date FROM mytable
-
-The key thing to notice in the above example is that the second query did not
-utilize `$this->db->from()`_ and did not pass a table name into the first
-parameter. The reason for this outcome is because the query has not been
-executed using `$this->db->get()`_ which resets values or reset directly
+
+The key thing to notice in the above example is that the second query did not
+utilize `$this->db->from()`_ and did not pass a table name into the first
+parameter. The reason for this outcome is because the query has not been
+executed using `$this->db->get()`_ which resets values or reset directly
using `$this->db->reset_query()`_.
@@ -116,7 +116,7 @@
::
- $this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
+ $this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
$query = $this->db->get('mytable');
@@ -130,7 +130,7 @@
$this->db->select_max('age');
$query = $this->db->get('members'); // Produces: SELECT MAX(age) as age FROM members
-
+
$this->db->select_max('age', 'member_age');
$query = $this->db->get('members'); // Produces: SELECT MAX(age) as member_age FROM members
@@ -195,7 +195,7 @@
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');
$query = $this->db->get();
-
+
// Produces:
// SELECT * FROM blogs JOIN comments ON comments.id = blogs.id
@@ -224,7 +224,7 @@
::
- $this->db->where('name', $name); // Produces: WHERE name = 'Joe'
+ $this->db->where('name', $name); // Produces: WHERE name = 'Joe'
Notice that the equal sign is added for you.
@@ -236,7 +236,7 @@
$this->db->where('name', $name);
$this->db->where('title', $title);
$this->db->where('status', $status);
- // WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
+ // WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
#. **Custom key/value method:**
You can include an operator in the first parameter in order to
@@ -245,7 +245,7 @@
::
$this->db->where('name !=', $name);
- $this->db->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45
+ $this->db->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45
#. **Associative array method:**
@@ -253,7 +253,7 @@
$array = array('name' => $name, 'title' => $title, 'status' => $status);
$this->db->where($array);
- // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
+ // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
You can include your own operators using this method as well:
@@ -354,7 +354,7 @@
::
- $this->db->like('title', 'match'); // Produces: WHERE title LIKE '%match%'
+ $this->db->like('title', 'match'); // Produces: WHERE title LIKE '%match%'
If you use multiple function calls they will be chained together with
AND between them::
@@ -371,7 +371,7 @@
$this->db->like('title', 'match', 'before'); // Produces: WHERE title LIKE '%match'
$this->db->like('title', 'match', 'after'); // Produces: WHERE title LIKE 'match%'
- $this->db->like('title', 'match', 'both'); // Produces: WHERE title LIKE '%match%'
+ $this->db->like('title', 'match', 'both'); // Produces: WHERE title LIKE '%match%'
#. **Associative array method:**
@@ -443,7 +443,7 @@
possible syntaxes, 1 argument or 2::
$this->db->having('user_id = 45'); // Produces: HAVING user_id = 45
- $this->db->having('user_id', 45); // Produces: HAVING user_id = 45
+ $this->db->having('user_id', 45); // Produces: HAVING user_id = 45
You can also pass an array of multiple values as well::
@@ -486,7 +486,7 @@
::
$this->db->order_by("title", "desc");
- $this->db->order_by("name", "asc"); // Produces: ORDER BY title DESC, name ASC
+ $this->db->order_by("name", "asc"); // Produces: ORDER BY title DESC, name ASC
.. note:: order_by() was formerly known as orderby(), which has been
@@ -518,7 +518,7 @@
echo $this->db->count_all_results('my_table'); // Produces an integer, like 25
$this->db->like('title', 'match');
$this->db->from('my_table');
- echo $this->db->count_all_results(); // Produces an integer, like 17
+ echo $this->db->count_all_results(); // Produces an integer, like 17
$this->db->count_all()
======================
@@ -529,6 +529,54 @@
echo $this->db->count_all('my_table'); // Produces an integer, like 25
**************
+Query grouping
+**************
+
+Query grouping allows you to create groups of WHERE clauses by enclosing them in parentheses. This will allow
+you to create queries with complex WHERE clauses. Nested groups are supported. Example:
+
+ $this->db->select('*')->from('my_table')
+ ->group_start()
+ ->where('a', 'a')
+ ->or_group_start()
+ ->where('b', 'b')
+ ->where('c', 'c')
+ ->group_end()
+ ->group_end()
+ ->where('d', 'd')
+ ->get();
+
+ // Generates:
+ // SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'
+
+.. note:: groups need to be balanced, make sure every group_start() is matched by a group_end().
+
+$this->db->group_start()
+========================
+
+Starts a new group by adding an opening parenthesis to the WHERE clause of the query.
+
+$this->db->or_group_start()
+===========================
+
+Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'OR'.
+
+$this->db->not_group_start()
+============================
+
+Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'NOT'.
+
+$this->db->or_not_group_start()
+===============================
+
+Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'OR NOT'.
+
+$this->db->group_end()
+======================
+
+Ends the current group by adding an closing parenthesis to the WHERE clause of the query.
+
+**************
Inserting Data
**************
@@ -544,7 +592,7 @@
'name' => 'My Name',
'date' => 'My date'
);
-
+
$this->db->insert('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')
@@ -560,7 +608,7 @@
var $date = 'My Date';
}
*/
-
+
$object = new Myclass;
$this->db->insert('mytable', $object);
// Produces: INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date')
@@ -572,7 +620,7 @@
$this->db->get_compiled_insert()
================================
-Compiles the insertion query just like `$this->db->insert()`_ but does not
+Compiles the insertion query just like `$this->db->insert()`_ but does not
*run* the query. This method simply returns the SQL query as a string.
Example::
@@ -582,27 +630,27 @@
'name' => 'My Name',
'date' => 'My date'
);
-
+
$sql = $this->db->set($data)->get_compiled_insert('mytable');
echo $sql;
-
+
// Produces string: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')
-The second parameter enables you to set whether or not the active record query
+The second parameter enables you to set whether or not the active record query
will be reset (by default it will be--just like `$this->db->insert()`_)::
-
+
echo $this->db->set('title', 'My Title')->get_compiled_insert('mytable', FALSE);
-
+
// Produces string: INSERT INTO mytable (title) VALUES ('My Title')
-
+
echo $this->db->set('content', 'My Content')->get_compiled_insert();
// Produces string: INSERT INTO mytable (title, content) VALUES ('My Title', 'My Content')
-
-The key thing to notice in the above example is that the second query did not
-utlize `$this->db->from()`_ nor did it pass a table name into the first
-parameter. The reason this worked is because the query has not been executed
-using `$this->db->insert()`_ which resets values or reset directly using
+
+The key thing to notice in the above example is that the second query did not
+utlize `$this->db->from()`_ nor did it pass a table name into the first
+parameter. The reason this worked is because the query has not been executed
+using `$this->db->insert()`_ which resets values or reset directly using
`$this->db->reset_query()`_.
$this->db->insert_batch()
@@ -624,7 +672,7 @@
'date' => 'Another date'
)
);
-
+
$this->db->insert_batch('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')
@@ -652,7 +700,7 @@
$this->db->set('name', $name);
$this->db->set('title', $title);
$this->db->set('status', $status);
- $this->db->insert('mytable');
+ $this->db->insert('mytable');
**set()** will also accept an optional third parameter ($escape), that
will prevent data from being escaped if set to FALSE. To illustrate the
@@ -674,7 +722,7 @@
'title' => $title,
'status' => $status
);
-
+
$this->db->set($array);
$this->db->insert('mytable');
@@ -687,7 +735,7 @@
var $date = 'My Date';
}
*/
-
+
$object = new Myclass;
$this->db->set($object);
$this->db->insert('mytable');
@@ -709,7 +757,7 @@
'name' => $name,
'date' => $date
);
-
+
$this->db->where('id', $id);
$this->db->update('mytable', $data);
// Produces: // UPDATE mytable // SET title = '{$title}', name = '{$name}', date = '{$date}' // WHERE id = $id
@@ -723,7 +771,7 @@
var $date = 'My Date';
}
*/
-
+
$object = new Myclass;
$this->db->where('id', $id);
$this->db->update('mytable', $object);
@@ -764,14 +812,14 @@
)
);
- $this->db->update_batch('mytable', $data, 'title');
+ $this->db->update_batch('mytable', $data, 'title');
- // Produces:
+ // Produces:
// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
- // `date` = CASE
+ // `date` = CASE
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
@@ -810,7 +858,7 @@
$this->db->where('id', $id);
$this->db->delete('mytable');
-
+
// Produces:
// DELETE FROM mytable
// WHERE id = $id
@@ -847,17 +895,17 @@
$this->db->from('mytable');
$this->db->truncate();
-
- // or
-
+
+ // or
+
$this->db->truncate('mytable');
-
+
// Produce:
- // TRUNCATE mytable
+ // TRUNCATE mytable
.. note:: If the TRUNCATE command isn't available, truncate() will
execute as "DELETE FROM table".
-
+
$this->db->get_compiled_delete()
================================
This works exactly the same way as ``$this->db->get_compiled_insert()`` except
@@ -917,11 +965,11 @@
$this->db->stop_cache();
$this->db->get('tablename');
//Generates: SELECT `field1` FROM (`tablename`)
-
+
$this->db->select('field2');
$this->db->get('tablename');
//Generates: SELECT `field1`, `field2` FROM (`tablename`)
-
+
$this->db->flush_cache();
$this->db->select('field2');
$this->db->get('tablename');
@@ -935,13 +983,13 @@
$this->db->reset_query()
========================
-Resetting Active Record allows you to start fresh with your query without
-executing it first using a method like $this->db->get() or $this->db->insert().
-Just like the methods that execute a query, this will *not* reset items you've
+Resetting Active Record allows you to start fresh with your query without
+executing it first using a method like $this->db->get() or $this->db->insert().
+Just like the methods that execute a query, this will *not* reset items you've
cached using `Active Record Caching`_.
-This is useful in situations where you are using Active Record to generate SQL
-(ex. ``$this->db->get_compiled_select()``) but then choose to, for instance,
+This is useful in situations where you are using Active Record to generate SQL
+(ex. ``$this->db->get_compiled_select()``) but then choose to, for instance,
run the query::
// Note that the second parameter of the get_compiled_select method is FALSE