From c3a8e3e57578a84e314f7370420b9487395b8deb Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Mon, 24 Feb 2014 14:56:08 -0800 Subject: [PATCH] CRM_Utils_SQL_Select cleanup: * Add more docs * Add test for ORDER BY * Add support for LIMIT --- CRM/Utils/SQL/Select.php | 58 +++++++++++++++++++++- tests/phpunit/CRM/Utils/SQL/SelectTest.php | 34 ++++++++++++- 2 files changed, 89 insertions(+), 3 deletions(-) diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php index f8d6031cc2..3a10253a85 100644 --- a/CRM/Utils/SQL/Select.php +++ b/CRM/Utils/SQL/Select.php @@ -31,6 +31,7 @@ * - to disable any preprocessing, simply omit the variable list * - Variables may be individual values or arrays; arrays are imploded with commas * - Conditionals are AND'd; if you need OR's, do it yourself + * - Use classes/functions with documentation (rather than undocumented array-trees) */ class CRM_Utils_SQL_Select { private $selects = array(); @@ -40,27 +41,44 @@ class CRM_Utils_SQL_Select { private $groupBys = array(); private $havings = array(); private $orderBys = array(); + private $limit = NULL; + private $offset = NULL; /** * Create a new SELECT query * - * @param $from + * @param string $from table-name and optional alias * @return CRM_Utils_SQL_Select */ public static function from($from) { return new self($from); } + /** + * Create a new SELECT query + * + * @param string $from table-name and optional alias + */ public function __construct($from) { $this->from = $from; } + /** + * Add a new JOIN clause + * + * @param string $name the effective alias of the joined table + * @param string $expr the complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id") + * @param array|null $args + * @return CRM_Utils_SQL_Select + */ public function join($name, $expr, $args = NULL) { $this->joins[$name] = $this->interpolate($expr, $args); return $this; } /** + * Specify the column(s)/value(s) to return by adding to the SELECT clause + * * @param string|array $exprs list of SQL expressions * @param null|array $args use NULL to disable interpolation; use an array of variables to enable * @return CRM_Utils_SQL_Select @@ -74,6 +92,8 @@ class CRM_Utils_SQL_Select { } /** + * Limit results by adding extra condition(s) to the WHERE clause + * * @param string|array $exprs list of SQL expressions * @param null|array $args use NULL to disable interpolation; use an array of variables to enable * @return CRM_Utils_SQL_Select @@ -87,6 +107,8 @@ class CRM_Utils_SQL_Select { } /** + * Group results by adding extra items to the GROUP BY clause + * * @param string|array $exprs list of SQL expressions * @param null|array $args use NULL to disable interpolation; use an array of variables to enable * @return CRM_Utils_SQL_Select @@ -100,6 +122,8 @@ class CRM_Utils_SQL_Select { } /** + * Limit results by adding extra condition(s) to the HAVING clause + * * @param string|array $exprs list of SQL expressions * @param null|array $args use NULL to disable interpolation; use an array of variables to enable * @return CRM_Utils_SQL_Select @@ -113,6 +137,8 @@ class CRM_Utils_SQL_Select { } /** + * Sort results by adding extra items to the ORDER BY clause + * * @param string|array $exprs list of SQL expressions * @param null|array $args use NULL to disable interpolation; use an array of variables to enable * @return CRM_Utils_SQL_Select @@ -125,6 +151,26 @@ class CRM_Utils_SQL_Select { return $this; } + /** + * Set a limit on the number of records to return + * + * @param int $limit + * @param int $offset + * @return CRM_Utils_SQL_Select + * @throws CRM_Core_Exception + */ + public function limit($limit, $offset = 0) { + if ($limit !== NULL && !is_numeric($limit)) { + throw new CRM_Core_Exception("Illegal limit"); + } + if ($offset !== NULL && !is_numeric($offset)) { + throw new CRM_Core_Exception("Illegal offset"); + } + $this->limit = $limit; + $this->offset = $offset; + return $this; + } + /** * Given a string like "field_name = @value", replace "@value" with an escaped SQL string * @@ -173,6 +219,10 @@ class CRM_Utils_SQL_Select { } } + /** + * @param string|NULL $value + * @return string SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) + */ protected function escapeString($value) { return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; } @@ -203,6 +253,12 @@ class CRM_Utils_SQL_Select { if ($this->orderBys) { $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n"; } + if ($this->limit !== NULL) { + $sql .= 'LIMIT ' . $this->limit . "\n"; + if ($this->offset !== NULL) { + $sql .= 'OFFSET ' . $this->offset . "\n"; + } + } return $sql; } } diff --git a/tests/phpunit/CRM/Utils/SQL/SelectTest.php b/tests/phpunit/CRM/Utils/SQL/SelectTest.php index 5d2f9f7bf5..8428c153a2 100644 --- a/tests/phpunit/CRM/Utils/SQL/SelectTest.php +++ b/tests/phpunit/CRM/Utils/SQL/SelectTest.php @@ -51,6 +51,32 @@ class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase { $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 10)', $select->toSQL()); } + function testOrderByPlain() { + $select = CRM_Utils_SQL_Select::from('foo bar') + ->orderBy('first asc') + ->orderBy(array('second desc', 'third')); + $this->assertLike('SELECT * FROM foo bar ORDER BY first asc, second desc, third', $select->toSQL()); + } + + function testLimit_defaultOffset() { + $select = CRM_Utils_SQL_Select::from('foo bar') + ->limit(20); + $this->assertLike('SELECT * FROM foo bar LIMIT 20 OFFSET 0', $select->toSQL()); + } + + function testLimit_withOffset() { + $select = CRM_Utils_SQL_Select::from('foo bar') + ->limit(20, 60); + $this->assertLike('SELECT * FROM foo bar LIMIT 20 OFFSET 60', $select->toSQL()); + } + + function testLimit_disable() { + $select = CRM_Utils_SQL_Select::from('foo bar') + ->limit(20, 60) + ->limit(NULL, NULL); + $this->assertLike('SELECT * FROM foo bar', $select->toSQL()); + } + function testBig() { $select = CRM_Utils_SQL_Select::from('foo') ->select('foo.id') @@ -58,14 +84,18 @@ class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase { ->join('rel2', 'LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id') ->where('foo.type = @type', array('@type' => 'mytype')) ->groupBy("foo.id") - ->having('sum(rel1.stat) > 10'); + ->having('sum(rel1.stat) > 10') + ->orderBy('rel2.whiz') + ->limit(100, 300); $this->assertLike( "SELECT foo.id FROM foo" . " INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id" . " LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id " . " WHERE (foo.type = \"mytype\")" . " GROUP BY foo.id" - . " HAVING (sum(rel1.stat) > 10)", + . " HAVING (sum(rel1.stat) > 10)" + . " ORDER BY rel2.whiz" + . " LIMIT 100 OFFSET 300", $select->toSQL() ); } -- 2.25.1