Add seed values support for Query Builder order_by

(feature request #1987)

22 files changed, 166 insertions, 41 deletions

diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php
index dfaf0e0c1..4fcb6c63c 100644
--- a/system/database/DB_driver.php
+++ b/system/database/DB_driver.php
@@ -329,9 +329,9 @@ abstract class CI_DB_driver {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' RAND()';
+	protected $_random_keyword = array('RAND()', 'RAND(%d)');
 
 	/**
 	 * COUNT string
diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php
index 364397721..543d5ccdd 100644
--- a/system/database/DB_query_builder.php
+++ b/system/database/DB_query_builder.php
@@ -1144,13 +1144,16 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 */
 	public function order_by($orderby, $direction = '', $escape = NULL)
 	{
-		$direction = trim($direction);
+		$direction = strtoupper(trim($direction));
 
-		if (strtolower($direction) === 'random' OR $orderby === $this->_random_keyword)
+		if ($direction === 'RANDOM')
 		{
-			// Random ordered results don't need a field name
-			$orderby = $this->_random_keyword;
 			$direction = '';
+
+			// Do we have a seed value?
+			$orderby = ctype_digit((string) $orderby)
+				? $orderby = sprintf($this->_random_keyword[1], $orderby)
+				: $this->_random_keyword[0];
 		}
 		elseif (empty($orderby))
 		{
@@ -1158,7 +1161,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 		}
 		elseif ($direction !== '')
 		{
-			$direction = in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE) ? ' '.$direction : '';
+			$direction = in_array($direction, array('ASC', 'DESC'), TRUE) ? ' '.$direction : '';
 		}
 
 		is_bool($escape) OR $escape = $this->_protect_identifiers;
diff --git a/system/database/drivers/cubrid/cubrid_driver.php b/system/database/drivers/cubrid/cubrid_driver.php
index c89a924d3..839413951 100644
--- a/system/database/drivers/cubrid/cubrid_driver.php
+++ b/system/database/drivers/cubrid/cubrid_driver.php
@@ -64,6 +64,13 @@ class CI_DB_cubrid_driver extends CI_DB {
 	 */
 	protected $_escape_char = '`';
 
+	/**
+	 * ORDER BY random keyword
+	 *
+	 * @var	array
+	 */
+	protected $_random_keyword = array('RANDOM()', 'RANDOM(%d)');
+
 	// --------------------------------------------------------------------
 
 	/**
diff --git a/system/database/drivers/ibase/ibase_driver.php b/system/database/drivers/ibase/ibase_driver.php
index fc1c28f31..02ec354e3 100644
--- a/system/database/drivers/ibase/ibase_driver.php
+++ b/system/database/drivers/ibase/ibase_driver.php
@@ -53,9 +53,9 @@ class CI_DB_ibase_driver extends CI_DB {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' Random()';
+	protected $_random_keyword = array('RAND()', 'RAND()');
 
 	/**
 	 * IBase Transaction status flag
diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php
index a9d53fb57..53f2e55ea 100644
--- a/system/database/drivers/mssql/mssql_driver.php
+++ b/system/database/drivers/mssql/mssql_driver.php
@@ -53,9 +53,9 @@ class CI_DB_mssql_driver extends CI_DB {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' NEWID()';
+	protected $_random_keyword = array('NEWID()', 'RAND(%d)');
 
 	/**
 	 * Quoted identifier flag
diff --git a/system/database/drivers/oci8/oci8_driver.php b/system/database/drivers/oci8/oci8_driver.php
index eb01dd17e..62f919145 100644
--- a/system/database/drivers/oci8/oci8_driver.php
+++ b/system/database/drivers/oci8/oci8_driver.php
@@ -102,9 +102,9 @@ class CI_DB_oci8_driver extends CI_DB {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' ASC'; // not currently supported
+	protected $_random_keyword = array('ASC', 'ASC'); // not currently supported
 
 	/**
 	 * COUNT string
diff --git a/system/database/drivers/odbc/odbc_driver.php b/system/database/drivers/odbc/odbc_driver.php
index 1710aafae..a01a9d681 100644
--- a/system/database/drivers/odbc/odbc_driver.php
+++ b/system/database/drivers/odbc/odbc_driver.php
@@ -73,6 +73,13 @@ class CI_DB_odbc_driver extends CI_DB {
 	 */
 	protected $_like_escape_str = " {escape '%s'} ";
 
+	/**
+	 * ORDER BY random keyword
+	 *
+	 * @var	array
+	 */
+	protected $_random_keyword = array('RND()', 'RND(%d)');
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -85,8 +92,6 @@ class CI_DB_odbc_driver extends CI_DB {
 	{
 		parent::__construct($params);
 
-		$this->_random_keyword = ' RND('.time().')'; // database specific random keyword
-
 		// Legacy support for DSN in the hostname field
 		if (empty($this->dsn))
 		{
diff --git a/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php b/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php
index 4f762b91c..5a87cf0c7 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php
@@ -55,6 +55,13 @@ class CI_DB_pdo_cubrid_driver extends CI_DB_pdo_driver {
 	 */
 	protected $_escape_char = '`';
 
+	/**
+	 * ORDER BY random keyword
+	 *
+	 * @var	array
+	 */
+	protected $_random_keyword = array('RANDOM()', 'RANDOM(%d)');
+
 	// --------------------------------------------------------------------
 
 	/**
diff --git a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
index 7121819ec..a44fd3afc 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
@@ -53,9 +53,9 @@ class CI_DB_pdo_dblib_driver extends CI_DB_pdo_driver {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' NEWID()';
+	protected $_random_keyword = array('NEWID()', 'RAND(%d)');
 
 	/**
 	 * Quoted identifier flag
diff --git a/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php b/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php
index 83b9648c1..7c3836691 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php
@@ -53,9 +53,9 @@ class CI_DB_pdo_firebird_driver extends CI_DB_pdo_driver {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' RANDOM()';
+	protected $_random_keyword = array('RAND()', 'RAND()');
 
 	// --------------------------------------------------------------------
 
diff --git a/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php b/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php
index 8998670e1..03886f442 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php
@@ -51,6 +51,15 @@ class CI_DB_pdo_informix_driver extends CI_DB_pdo_driver {
 	// --------------------------------------------------------------------
 
 	/**
+	 * ORDER BY random keyword
+	 *
+	 * @var	array
+	 */
+	protected $_random_keyword = array('ASC', 'ASC'); // Currently not supported
+
+	// --------------------------------------------------------------------
+
+	/**
 	 * Class constructor
 	 *
 	 * Builds the DSN if not already set.
diff --git a/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php b/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php
index e1beb3683..051b7dec8 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php
@@ -62,9 +62,9 @@ class CI_DB_pdo_oci_driver extends CI_DB_pdo_driver {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' ASC'; // Currently not supported
+	protected $_random_keyword = array('ASC', 'ASC'); // Currently not supported
 
 	/**
 	 * COUNT string
diff --git a/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php b/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php
index f6e0e215a..05b34fa89 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php
@@ -73,6 +73,13 @@ class CI_DB_pdo_odbc_driver extends CI_DB_pdo_driver {
 	 */
 	protected $_like_escape_str = " {escape '%s'} ";
 
+	/**
+	 * ORDER BY random keyword
+	 *
+	 * @var	array
+	 */
+	protected $_random_keyword = array('RND()', 'RND(%d)');
+
 	// --------------------------------------------------------------------
 
 	/**
diff --git a/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php b/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php
index c3f5b7603..45165213f 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php
@@ -60,9 +60,9 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' RANDOM()';
+	protected $_random_keyword = array('RANDOM()', 'RANDOM()');
 
 	// --------------------------------------------------------------------
 
@@ -110,6 +110,41 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver {
 	// --------------------------------------------------------------------
 
 	/**
+	 * ORDER BY
+	 *
+	 * @param	string	$orderby
+	 * @param	string	$direction	ASC or DESC
+	 * @param	bool	$escape
+	 * @return	object
+	 */
+	public function order_by($orderby, $direction = '', $escape = NULL)
+	{
+		$direction = strtoupper(trim($direction));
+		if ($direction === 'RANDOM')
+		{
+			if ( ! is_float($orderby) && ctype_digit((string) $orderby))
+			{
+				$orderby = ($orderby > 1)
+					? (float) '0.'.$orderby
+					: (float) $orderby;
+			}
+
+			if (is_float($orderby))
+			{
+				$this->simple_query('SET SEED '.$orderby);
+			}
+
+			$orderby = $this->_random_keyword[0];
+			$direction = '';
+			$escape = FALSE;
+		}
+
+		return parent::order_by($orderby, $direction, $escape);
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
 	 * Show table query
 	 *
 	 * Generates a platform-specific query string so that the table names can be fetched
diff --git a/system/database/drivers/pdo/subdrivers/pdo_sqlite_driver.php b/system/database/drivers/pdo/subdrivers/pdo_sqlite_driver.php
index b24b13e76..f9d186f6c 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_sqlite_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_sqlite_driver.php
@@ -53,7 +53,7 @@ class CI_DB_pdo_sqlite_driver extends CI_DB_pdo_driver {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
 	protected $_random_keyword = ' RANDOM()';
 
diff --git a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
index 7bb7f8806..1a630729d 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
@@ -53,9 +53,9 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_driver {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' NEWID()';
+	protected $_random_keyword = array('NEWID()', 'RAND(%d)');
 
 	/**
 	 * Quoted identifier flag
diff --git a/system/database/drivers/postgre/postgre_driver.php b/system/database/drivers/postgre/postgre_driver.php
index 40f21b1e8..a52777b1e 100644
--- a/system/database/drivers/postgre/postgre_driver.php
+++ b/system/database/drivers/postgre/postgre_driver.php
@@ -60,9 +60,9 @@ class CI_DB_postgre_driver extends CI_DB {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' RANDOM()'; // database specific random keyword
+	protected $_random_keyword = array('RANDOM()', 'RANDOM()');
 
 	// --------------------------------------------------------------------
 
@@ -482,6 +482,41 @@ class CI_DB_postgre_driver extends CI_DB {
 	// --------------------------------------------------------------------
 
 	/**
+	 * ORDER BY
+	 *
+	 * @param	string	$orderby
+	 * @param	string	$direction	ASC or DESC
+	 * @param	bool	$escape
+	 * @return	object
+	 */
+	public function order_by($orderby, $direction = '', $escape = NULL)
+	{
+		$direction = strtoupper(trim($direction));
+		if ($direction === 'RANDOM')
+		{
+			if ( ! is_float($orderby) && ctype_digit((string) $orderby))
+			{
+				$orderby = ($orderby > 1)
+					? (float) '0.'.$orderby
+					: (float) $orderby;
+			}
+
+			if (is_float($orderby))
+			{
+				$this->simple_query('SET SEED '.$orderby);
+			}
+
+			$orderby = $this->_random_keyword[0];
+			$direction = '';
+			$escape = FALSE;
+		}
+
+		return parent::order_by($orderby, $direction, $escape);
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
 	 * Update statement
 	 *
 	 * Generates a platform-specific update string from the supplied data
diff --git a/system/database/drivers/sqlite/sqlite_driver.php b/system/database/drivers/sqlite/sqlite_driver.php
index 85411735c..23f287b7e 100644
--- a/system/database/drivers/sqlite/sqlite_driver.php
+++ b/system/database/drivers/sqlite/sqlite_driver.php
@@ -53,9 +53,9 @@ class CI_DB_sqlite_driver extends CI_DB {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' Random()'; // database specific random keyword
+	protected $_random_keyword = array('RANDOM()', 'RANDOM()');
 
 	// --------------------------------------------------------------------
 
diff --git a/system/database/drivers/sqlite3/sqlite3_driver.php b/system/database/drivers/sqlite3/sqlite3_driver.php
index 9a2c1eefc..f0818aea1 100644
--- a/system/database/drivers/sqlite3/sqlite3_driver.php
+++ b/system/database/drivers/sqlite3/sqlite3_driver.php
@@ -54,9 +54,9 @@ class CI_DB_sqlite3_driver extends CI_DB {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' RANDOM()';
+	protected $_random_keyword = array('RANDOM()', 'RANDOM()');
 
 	// --------------------------------------------------------------------
 
diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php
index 09ccc0200..a3d3177f7 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_driver.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php
@@ -53,9 +53,9 @@ class CI_DB_sqlsrv_driver extends CI_DB {
 	/**
 	 * ORDER BY random keyword
 	 *
-	 * @var	string
+	 * @var	array
 	 */
-	protected $_random_keyword = ' NEWID()';
+	protected $_random_keyword = array('NEWID()', 'RAND(%d)');
 
 	/**
 	 * Quoted identifier flag
diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst
index 1a80f76a3..d03419fbb 100644
--- a/user_guide_src/source/changelog.rst
+++ b/user_guide_src/source/changelog.rst
@@ -126,6 +126,7 @@ Release Date: Not Released
 	 - Added an optional parameter that allows to disable escaping (useful for custom fields) for methods ``join()``, ``order_by()``, ``where_in()``, ``or_where_in()``, ``where_not_in()``, ``or_where_not_in()``, ``insert()``, ``insert_batch()``.
 	 - Added support for ``join()`` with multiple conditions.
 	 - Added support for *USING* in ``join()``.
+	 - Added seed values support for random ordering with ``order_by(seed, 'RANDOM')``.
 	 - Changed ``limit()`` to ignore NULL values instead of always casting to integer.
 	 - Changed ``offset()`` to ignore empty values instead of always casting to integer.
 	 - Methods ``insert_batch()`` and ``update_batch()`` now return an integer representing the number of rows affected by them.
diff --git a/user_guide_src/source/database/query_builder.rst b/user_guide_src/source/database/query_builder.rst
index 61cd7dfed..8fb906052 100644
--- a/user_guide_src/source/database/query_builder.rst
+++ b/user_guide_src/source/database/query_builder.rst
@@ -469,25 +469,41 @@ Identical to having(), only separates multiple clauses with "OR".
 $this->db->order_by()
 =====================
 
-Lets you set an ORDER BY clause. The first parameter contains the name
-of the column you would like to order by. The second parameter lets you
-set the direction of the result. Options are asc or desc, or random.
+Lets you set an ORDER BY clause.
+
+The first parameter contains the name of the column you would like to order by.
+
+The second parameter lets you set the direction of the result.
+Options are **ASC**, **DESC** AND **RANDOM**.
 
 ::
 
-	$this->db->order_by("title", "desc");  // Produces: ORDER BY title DESC
+	$this->db->order_by('title', 'DESC');
+	// Produces: ORDER BY `title` DESC
 
 You can also pass your own string in the first parameter::
 
-	$this->db->order_by('title desc, name asc');  // Produces: ORDER BY title DESC, name ASC
+	$this->db->order_by('title DESC, name ASC');
+	// Produces: ORDER BY `title` DESC, `name` ASC
 
 Or multiple function calls can be made if you need multiple fields.
 
 ::
 
-	$this->db->order_by("title", "desc");
-	$this->db->order_by("name", "asc"); // Produces: ORDER BY title DESC, name ASC
+	$this->db->order_by('title', 'DESC');
+	$this->db->order_by('name', 'ASC');
+	// Produces: ORDER BY `title` DESC, `name` ASC
+
+If you choose the **RANDOM** direction option, then the first parameters will
+be ignored, unless you specify a numeric seed value.
+
+::
+
+	$this->db->order_by('title', 'RANDOM');
+	// Produces: ORDER BY RAND()
 
+	$this->db->order_by(42, 'RANDOM');
+	// Produces: ORDER BY RAND(42)
 
 .. note:: order_by() was formerly known as orderby(), which has been
 	removed.