From 98e46cf96447a2a6448d8dc984948a8694dbf747 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 13 Nov 2012 03:01:42 +0200 Subject: Add seed values support for Query Builder order_by (feature request #1987) --- system/database/DB_driver.php | 4 +-- system/database/DB_query_builder.php | 13 +++++--- system/database/drivers/cubrid/cubrid_driver.php | 7 ++++ system/database/drivers/ibase/ibase_driver.php | 4 +-- system/database/drivers/mssql/mssql_driver.php | 4 +-- system/database/drivers/oci8/oci8_driver.php | 4 +-- system/database/drivers/odbc/odbc_driver.php | 9 +++-- .../drivers/pdo/subdrivers/pdo_cubrid_driver.php | 7 ++++ .../drivers/pdo/subdrivers/pdo_dblib_driver.php | 4 +-- .../drivers/pdo/subdrivers/pdo_firebird_driver.php | 4 +-- .../drivers/pdo/subdrivers/pdo_informix_driver.php | 9 +++++ .../drivers/pdo/subdrivers/pdo_oci_driver.php | 4 +-- .../drivers/pdo/subdrivers/pdo_odbc_driver.php | 7 ++++ .../drivers/pdo/subdrivers/pdo_pgsql_driver.php | 39 ++++++++++++++++++++-- .../drivers/pdo/subdrivers/pdo_sqlite_driver.php | 2 +- .../drivers/pdo/subdrivers/pdo_sqlsrv_driver.php | 4 +-- system/database/drivers/postgre/postgre_driver.php | 39 ++++++++++++++++++++-- system/database/drivers/sqlite/sqlite_driver.php | 4 +-- system/database/drivers/sqlite3/sqlite3_driver.php | 4 +-- system/database/drivers/sqlsrv/sqlsrv_driver.php | 4 +-- 20 files changed, 142 insertions(+), 34 deletions(-) (limited to 'system/database') 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 @@ -50,6 +50,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 * 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()'); // -------------------------------------------------------------------- @@ -109,6 +109,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 * 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()'); // -------------------------------------------------------------------- @@ -481,6 +481,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 * 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 -- cgit v1.2.3-24-g4f1b