Merge pull request #3373 from jim-parry/userguide/querybuilder

User Guide update - Query Builder (fixes #2512)

2 files changed, 154 insertions, 79 deletions

diff --git a/user_guide_src/source/database/configuration.rst b/user_guide_src/source/database/configuration.rst
index 34cefffbd..9f52ad2a2 100644
--- a/user_guide_src/source/database/configuration.rst
+++ b/user_guide_src/source/database/configuration.rst
@@ -141,7 +141,8 @@ Query Builder
 
 The :doc:`Query Builder Class <query_builder>` is globally enabled or
 disabled by setting the $query_builder variable in the database
-configuration file to TRUE/FALSE (boolean). If you are not using the
+configuration file to TRUE/FALSE (boolean). The default setting is TRUE.
+If you are not using the
 query builder class, setting it to FALSE will utilize fewer resources
 when the database classes are initialized.
 
diff --git a/user_guide_src/source/database/query_builder.rst b/user_guide_src/source/database/query_builder.rst
index 5bfdfdb52..3203ff103 100644
--- a/user_guide_src/source/database/query_builder.rst
+++ b/user_guide_src/source/database/query_builder.rst
@@ -19,7 +19,9 @@ system.
 	class in your database config file, allowing the core database library
 	and adapter to utilize fewer resources.
 
-.. contents:: Page Contents
+.. contents::
+    :local:
+    :depth: 1
 
 **************
 Selecting Data
@@ -28,7 +30,7 @@ Selecting Data
 The following functions allow you to build SQL **SELECT** statements.
 
 $this->db->get()
-================
+----------------
 
 Runs the selection query and returns the result. Can be used by itself
 to retrieve all records from a table::
@@ -39,7 +41,8 @@ The second and third parameters enable you to set a limit and offset
 clause::
 
 	$query = $this->db->get('mytable', 10, 20);
-	// Produces: SELECT * FROM mytable LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)
+	// Produces: SELECT * FROM mytable LIMIT 20, 10 
+        // (in MySQL. Other databases have slightly different syntax)
 
 You'll notice that the above function is assigned to a variable named
 $query, which can be used to show the results::
@@ -54,10 +57,13 @@ $query, which can be used to show the results::
 Please visit the :doc:`result functions <results>` page for a full
 discussion regarding result generation.
 
+:returns:	DB_Result for a successful "read", 
+                TRUE for a successful "write", FALSE if an error
+
 $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::
@@ -79,14 +85,15 @@ will be reset (by default it will be reset, just like when using `$this->db->get
 	// Produces string: SELECT title, content, date FROM mytable LIMIT 20, 10
 
 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
+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()`_.
+executed using **$this->db->get()** which resets values or reset directly
+using **$this->db->reset_query()**.
 
+:returns:	The SQL select string
 
 $this->db->get_where()
-======================
+----------------------
 
 Identical to the above function except that it permits you to add a
 "where" clause in the second parameter, instead of using the db->where()
@@ -98,8 +105,11 @@ Please read the about the where function below for more information.
 
 .. note:: get_where() was formerly known as getwhere(), which has been removed
 
+:returns:	DB_Result for a successful "read", 
+                TRUE for a successful "write", FALSE if an error
+
 $this->db->select()
-===================
+-------------------
 
 Permits you to write the SELECT portion of your query::
 
@@ -119,9 +129,10 @@ with backticks. This is useful if you need a compound select statement.
 	$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
 	$query = $this->db->get('mytable');
 
+:returns:	The query builder object
 
 $this->db->select_max()
-=======================
+-----------------------
 
 Writes a "SELECT MAX(field)" portion for your query. You can optionally
 include a second parameter to rename the resulting field.
@@ -135,8 +146,7 @@ include a second parameter to rename the resulting field.
 	$query = $this->db->get('members'); // Produces: SELECT MAX(age) as member_age FROM members
 
 
-$this->db->select_min()
-=======================
+**$this->db->select_min()**
 
 Writes a "SELECT MIN(field)" portion for your query. As with
 select_max(), You can optionally include a second parameter to rename
@@ -148,8 +158,7 @@ the resulting field.
 	$query = $this->db->get('members'); // Produces: SELECT MIN(age) as age FROM members
 
 
-$this->db->select_avg()
-=======================
+**$this->db->select_avg()**
 
 Writes a "SELECT AVG(field)" portion for your query. As with
 select_max(), You can optionally include a second parameter to rename
@@ -161,8 +170,7 @@ the resulting field.
 	$query = $this->db->get('members'); // Produces: SELECT AVG(age) as age FROM members
 
 
-$this->db->select_sum()
-=======================
+**$this->db->select_sum()**
 
 Writes a "SELECT SUM(field)" portion for your query. As with
 select_max(), You can optionally include a second parameter to rename
@@ -173,9 +181,11 @@ the resulting field.
 	$this->db->select_sum('age');
 	$query = $this->db->get('members'); // Produces: SELECT SUM(age) as age FROM members
 
+:returns:	The query builder object
+
 
 $this->db->from()
-=================
+-----------------
 
 Permits you to write the FROM portion of your query::
 
@@ -186,8 +196,10 @@ Permits you to write the FROM portion of your query::
 .. note:: As shown earlier, the FROM portion of your query can be specified
 	in the $this->db->get() function, so use whichever method you prefer.
 
+:returns:	The query builder object
+
 $this->db->join()
-=================
+-----------------
 
 Permits you to write the JOIN portion of your query::
 
@@ -211,8 +223,14 @@ outer, and right outer.
 	$this->db->join('comments', 'comments.id = blogs.id', 'left');
 	// Produces: LEFT JOIN comments ON comments.id = blogs.id
 
+:returns:	The query builder object
+
+*************************
+Looking for Specific Data
+*************************
+
 $this->db->where()
-==================
+------------------
 
 This function enables you to set **WHERE** clauses using one of four
 methods:
@@ -277,9 +295,7 @@ with backticks.
 
 	$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);
 
-
-$this->db->or_where()
-=====================
+**$this->db->or_where()**
 
 This function is identical to the one above, except that multiple
 instances are joined by OR::
@@ -290,8 +306,10 @@ instances are joined by OR::
 .. note:: or_where() was formerly known as orwhere(), which has been
 	removed.
 
+:returns:	The query builder object
+
 $this->db->where_in()
-=====================
+---------------------
 
 Generates a WHERE field IN ('item', 'item') SQL query joined with AND if
 appropriate
@@ -303,8 +321,7 @@ appropriate
 	// Produces: WHERE username IN ('Frank', 'Todd', 'James')
 
 
-$this->db->or_where_in()
-========================
+**$this->db->or_where_in()**
 
 Generates a WHERE field IN ('item', 'item') SQL query joined with OR if
 appropriate
@@ -315,9 +332,10 @@ appropriate
 	$this->db->or_where_in('username', $names);
 	// Produces: OR username IN ('Frank', 'Todd', 'James')
 
+:returns:	The query builder object
 
 $this->db->where_not_in()
-=========================
+-------------------------
 
 Generates a WHERE field NOT IN ('item', 'item') SQL query joined with
 AND if appropriate
@@ -329,8 +347,7 @@ AND if appropriate
 	// Produces: WHERE username NOT IN ('Frank', 'Todd', 'James')
 
 
-$this->db->or_where_not_in()
-============================
+**$this->db->or_where_not_in()**
 
 Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR
 if appropriate
@@ -341,9 +358,15 @@ if appropriate
 	$this->db->or_where_not_in('username', $names);
 	// Produces: OR username NOT IN ('Frank', 'Todd', 'James')
 
+:returns:	The query builder object
+
+
+************************
+Looking for Similar Data
+************************
 
 $this->db->like()
-=================
+-----------------
 
 This method enables you to generate **LIKE** clauses, useful for doing
 searches.
@@ -383,8 +406,7 @@ searches.
 		// WHERE `title` LIKE '%match%' ESCAPE '!' AND  `page1` LIKE '%match%' ESCAPE '!' AND  `page2` LIKE '%match%' ESCAPE '!'
 
 
-$this->db->or_like()
-====================
+**$this->db->or_like()**
 
 This method is identical to the one above, except that multiple
 instances are joined by OR::
@@ -394,16 +416,14 @@ instances are joined by OR::
 
 .. note:: ``or_like()`` was formerly known as ``orlike()``, which has been removed.
 
-$this->db->not_like()
-=====================
+**$this->db->not_like()**
 
 This method is identical to ``like()``, except that it generates
 NOT LIKE statements::
 
 	$this->db->not_like('title', 'match');	// WHERE `title` NOT LIKE '%match% ESCAPE '!'
 
-$this->db->or_not_like()
-========================
+**$this->db->or_not_like()**
 
 This method is identical to ``not_like()``, except that multiple
 instances are joined by OR::
@@ -412,8 +432,10 @@ instances are joined by OR::
 	$this->db->or_not_like('body', 'match');
 	// WHERE `title` LIKE '%match% OR  `body` NOT LIKE '%match%' ESCAPE '!'
 
+:returns:	The query builder object
+
 $this->db->group_by()
-=====================
+---------------------
 
 Permits you to write the GROUP BY portion of your query::
 
@@ -426,8 +448,10 @@ You can also pass an array of multiple values as well::
 .. note:: group_by() was formerly known as groupby(), which has been
 	removed.
 
+:returns:	The query builder object
+
 $this->db->distinct()
-=====================
+---------------------
 
 Adds the "DISTINCT" keyword to a query
 
@@ -436,9 +460,10 @@ Adds the "DISTINCT" keyword to a query
 	$this->db->distinct();
 	$this->db->get('table'); // Produces: SELECT DISTINCT * FROM table
 
+:returns:	The query builder object
 
 $this->db->having()
-===================
+-------------------
 
 Permits you to write the HAVING portion of your query. There are 2
 possible syntaxes, 1 argument or 2::
@@ -462,13 +487,18 @@ setting it to FALSE.
 	$this->db->having('user_id',  45, FALSE);  // Produces: HAVING user_id = 45
 
 
-$this->db->or_having()
-======================
+**$this->db->or_having()**
 
 Identical to having(), only separates multiple clauses with "OR".
 
+:returns:	The query builder object
+
+****************
+Ordering results
+****************
+
 $this->db->order_by()
-=====================
+---------------------
 
 Lets you set an ORDER BY clause.
 
@@ -512,8 +542,14 @@ be ignored, unless you specify a numeric seed value.
 .. note:: Random ordering is not currently supported in Oracle and
 	will default to ASC instead.
 
+:returns:	The query builder object
+
+****************************
+Limiting or Counting Results
+****************************
+
 $this->db->limit()
-==================
+------------------
 
 Lets you limit the number of rows you would like returned by the query::
 
@@ -525,8 +561,10 @@ The second parameter lets you set a result offset.
 
 	$this->db->limit(10, 20);  // Produces: LIMIT 20, 10 (in MySQL.  Other databases have slightly different syntax)
 
+:returns:	The query builder object
+
 $this->db->count_all_results()
-==============================
+------------------------------
 
 Permits you to determine the number of rows in a particular Active
 Record query. Queries will accept Query Builder restrictors such as
@@ -537,14 +575,18 @@ where(), or_where(), like(), or_like(), etc. Example::
 	$this->db->from('my_table');
 	echo $this->db->count_all_results(); // Produces an integer, like 17
 
+:returns:	Count of all the records returned by a query
+
 $this->db->count_all()
-======================
+----------------------
 
 Permits you to determine the number of rows in a particular table.
 Submit the table name in the first parameter. Example::
 
 	echo $this->db->count_all('my_table');  // Produces an integer, like 25
 
+:returns:	Count of all the records in the specified table
+
 **************
 Query grouping
 **************
@@ -568,37 +610,34 @@ you to create queries with complex WHERE clauses. Nested groups are supported. E
 
 .. note:: groups need to be balanced, make sure every group_start() is matched by a group_end().
 
-$this->db->group_start()
-========================
+**$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()
-===========================
+**$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()
-============================
+**$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()
-===============================
+**$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()
-======================
+**$this->db->group_end()**
 
 Ends the current group by adding an closing parenthesis to the WHERE clause of the query.
 
+:returns:	The query builder object
+
 **************
 Inserting Data
 **************
 
 $this->db->insert()
-===================
+-------------------
 
 Generates an insert string based on the data you supply, and runs the
 query. You can either pass an **array** or an **object** to the
@@ -635,8 +674,11 @@ object.
 
 .. note:: All values are escaped automatically producing safer queries.
 
+:returns:	DB_Query on success, FALSE on failure
+
 $this->db->get_compiled_insert()
-================================
+--------------------------------
+
 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.
 
@@ -672,8 +714,10 @@ using `$this->db->insert()` which resets values or reset directly using
 
 .. note:: This method doesn't work for batched inserts.
 
+:returns:	The SQL insert string
+
 $this->db->insert_batch()
-=========================
+-------------------------
 
 Generates an insert string based on the data you supply, and runs the
 query. You can either pass an **array** or an **object** to the
@@ -700,8 +744,14 @@ associative array of values.
 
 .. note:: All values are escaped automatically producing safer queries.
 
+:returns:	Count of the number of records inserted on success, FALSE on failure
+
+*************
+Updating Data
+*************
+
 $this->db->replace()
-====================
+--------------------
 
 This method executes a REPLACE statement, which is basically the SQL
 standard for (optional) DELETE + INSERT, using *PRIMARY* and *UNIQUE*
@@ -729,8 +779,10 @@ will be deleted with our new row data replacing it.
 Usage of the ``set()`` method is also allowed and all fields are
 automatically escaped, just like with ``insert()``.
 
+:returns:	DB_query object on success, FALSE on failure
+
 $this->db->set()
-================
+----------------
 
 This function enables you to set values for inserts or updates.
 
@@ -788,12 +840,10 @@ Or an object::
 	$this->db->set($object);
 	$this->db->insert('mytable');
 
-*************
-Updating Data
-*************
+:returns:	The query builder object
 
 $this->db->update()
-===================
+-------------------
 
 Generates an update string and runs the query based on the data you
 supply. You can pass an **array** or an **object** to the function. Here
@@ -839,9 +889,10 @@ Or as an array::
 You may also use the $this->db->set() function described above when
 performing updates.
 
+:returns:	DB_query object on success, FALSE on failure
 
 $this->db->update_batch()
-=========================
+-------------------------
 
 Generates an update string based on the data you supply, and runs the query.
 You can either pass an **array** or an **object** to the function.
@@ -882,8 +933,10 @@ array of values, the third parameter is the where key.
 	due to the very nature of how it works. Instead, ``update_batch()``
 	returns the number of rows affected.
 
+:returns:	Count of the number of records affected on success, FALSE on failure
+
 $this->db->get_compiled_update()
-================================
+--------------------------------
 
 This works exactly the same way as ``$this->db->get_compiled_insert()`` except
 that it produces an UPDATE SQL string instead of an INSERT SQL string.
@@ -892,12 +945,14 @@ For more information view documentation for `$this->db->get_compiled_insert()`.
 
 .. note:: This method doesn't work for batched updates.
 
+:returns:	The SQL update string
+
 *************
 Deleting Data
 *************
 
 $this->db->delete()
-===================
+-------------------
 
 Generates a delete SQL string and runs the query.
 
@@ -930,17 +985,21 @@ delete data from more than 1 table.
 If you want to delete all data from a table, you can use the truncate()
 function, or empty_table().
 
+:returns:	DB_Query on success, FALSE on failure
+
 $this->db->empty_table()
-========================
+------------------------
 
 Generates a delete SQL string and runs the
 query.::
 
 	  $this->db->empty_table('mytable'); // Produces: DELETE FROM mytable
 
+:returns:	DB_Query on success, FALSE on failure
+
 
 $this->db->truncate()
-=====================
+---------------------
 
 Generates a truncate SQL string and runs the query.
 
@@ -959,13 +1018,20 @@ Generates a truncate SQL string and runs the query.
 .. note:: If the TRUNCATE command isn't available, truncate() will
 	execute as "DELETE FROM table".
 
+:returns:	DB_Query on success, FALSE on failure
+
 $this->db->get_compiled_delete()
-================================
+--------------------------------
+
 This works exactly the same way as ``$this->db->get_compiled_insert()`` except
 that it produces a DELETE SQL string instead of an INSERT SQL string.
 
 For more information view documentation for `$this->db->get_compiled_insert()`_.
 
+:returns:	The SQL delete string
+
+
+
 ***************
 Method Chaining
 ***************
@@ -994,23 +1060,25 @@ Cached calls are cumulative. If you make 2 cached select() calls, and
 then 2 uncached select() calls, this will result in 4 select() calls.
 There are three Caching functions available:
 
-$this->db->start_cache()
-========================
+**$this->db->start_cache()**
 
 This function must be called to begin caching. All Query Builder queries
 of the correct type (see below for supported queries) are stored for
 later use.
 
-$this->db->stop_cache()
-=======================
+**$this->db->stop_cache()**
 
 This function can be called to stop caching.
 
-$this->db->flush_cache()
-========================
+**$this->db->flush_cache()**
 
 This function deletes all items from the Query Builder cache.
 
+:returns:	void
+
+An example of caching
+---------------------
+
 Here's a usage example::
 
 	$this->db->start_cache();
@@ -1033,8 +1101,12 @@ Here's a usage example::
 	where, like, group_by, having, order_by, set
 
 
+***********************
+Resetting Query Builder
+***********************
+
 $this->db->reset_query()
-========================
+------------------------
 
 Resetting Query Builder allows you to start fresh with your query without
 executing it first using a method like $this->db->get() or $this->db->insert().
@@ -1063,4 +1135,6 @@ run the query::
 .. note:: Double calls to ``get_compiled_select()`` while you're using the
 	Query Builder Caching functionality and NOT resetting your queries
 	will results in the cache being merged twice. That in turn will
-	i.e. if you're caching a ``select()`` - select the same field twice.
\ No newline at end of file
+	i.e. if you're caching a ``select()`` - select the same field twice.
+
+:returns:	void