diff options
| -rw-r--r-- | README.markdown | 10 | ||||
| -rw-r--r-- | idiorm.php | 20 | ||||
| -rw-r--r-- | test/test_queries.php | 10 |
3 files changed, 30 insertions, 10 deletions
diff --git a/README.markdown b/README.markdown index b40e5f1..cccccd3 100644 --- a/README.markdown +++ b/README.markdown @@ -202,12 +202,16 @@ The `limit` and `offset` methods map pretty closely to their SQL equivalents. ##### Ordering ##### -*Note that this method **does not** escape its query parameter and so this should **not** be passed directly from user input.* +*Note that these methods **do not** escape their query parameters and so these should **not** be passed directly from user input.* -Two methods are provided to add `ORDER BY` clauses to your query. These are `order_by_desc` and `order_by_asc`, each of which takes a column name to sort by. +Two methods are provided to add `ORDER BY` clauses to your query. These are `order_by_desc` and `order_by_asc`, each of which takes a column name to sort by. The column names will be quoted. $people = ORM::for_table('person')->order_by_asc('gender')->order_by_desc('name')->find_many(); +If you want to order by something other than a column name, then use the `order_by_expr` method to add an unquoted SQL expression as an `ORDER BY` clause. + + $people = ORM::for_table('person')->order_by_expr('SOUNDEX(`name`)')->find_many(); + #### Grouping #### *Note that this method **does not** escape it query parameter and so this should **not** by passed directly from user input.* @@ -297,7 +301,7 @@ The `join` methods also take an optional third parameter, which is an `alias` fo #### Raw queries #### -If you need to perform more complex queries, you can completely specify the query to execute by using the `raw_query` method. This method takes a string and an array of parameters. The string should contain placeholders, either in question mark or named placeholder syntax, which will be used to bind the parameters to the query. +If you need to perform more complex queries, you can completely specify the query to execute by using the `raw_query` method. This method takes a string and optionally an array of parameters. The string can contain placeholders, either in question mark or named placeholder syntax, which will be used to bind the parameters to the query. $people = ORM::for_table('person')->raw_query('SELECT p.* FROM person p JOIN role r ON p.role_id = r.id WHERE r.name = :role', array('role' => 'janitor')->find_many(); @@ -411,13 +411,13 @@ } /** - * Perform a raw query. The query should contain placeholders, - * in either named or question mark style, and the parameters - * should be an array of values which will be bound to the - * placeholders in the query. If this method is called, all - * other query building methods will be ignored. + * Perform a raw query. The query can contain placeholders in + * either named or question mark style. If placeholders are + * used, the parameters should be an array of values which will + * be bound to the placeholders in the query. If this method + * is called, all other query building methods will be ignored. */ - public function raw_query($query, $parameters) { + public function raw_query($query, $parameters = array()) { $this->_is_raw_query = true; $this->_raw_query = $query; $this->_raw_parameters = $parameters; @@ -749,6 +749,14 @@ } /** + * Add an unquoted expression as an ORDER BY clause + */ + public function order_by_expr($clause) { + $this->_order_by[] = $clause; + return $this; + } + + /** * Add a column to the list of columns to GROUP BY */ public function group_by($column_name) { diff --git a/test/test_queries.php b/test/test_queries.php index 6262534..0faa592 100644 --- a/test/test_queries.php +++ b/test/test_queries.php @@ -80,6 +80,10 @@ $expected = "SELECT * FROM `widget` ORDER BY `name` ASC LIMIT 1"; Tester::check_equal("ORDER BY ASC", $expected); + ORM::for_table('widget')->order_by_expr('SOUNDEX(`name`)')->find_one(); + $expected = "SELECT * FROM `widget` ORDER BY SOUNDEX(`name`) LIMIT 1"; + Tester::check_equal("ORDER BY expression", $expected); + ORM::for_table('widget')->order_by_asc('name')->order_by_desc('age')->find_one(); $expected = "SELECT * FROM `widget` ORDER BY `name` ASC, `age` DESC LIMIT 1"; Tester::check_equal("Multiple ORDER BY", $expected); @@ -124,9 +128,13 @@ $expected = "SELECT * FROM `widget` WHERE `age` = '18' AND (`name` = 'Fred' OR `name` = 'Bob') AND `size` = 'large'"; Tester::check_equal("Raw WHERE clause in method chain", $expected); + ORM::for_table('widget')->raw_query('SELECT `w`.* FROM `widget` w')->find_many(); + $expected = "SELECT `w`.* FROM `widget` w"; + Tester::check_equal("Raw query", $expected); + ORM::for_table('widget')->raw_query('SELECT `w`.* FROM `widget` w WHERE `name` = ? AND `age` = ?', array('Fred', 5))->find_many(); $expected = "SELECT `w`.* FROM `widget` w WHERE `name` = 'Fred' AND `age` = '5'"; - Tester::check_equal("Raw query", $expected); + Tester::check_equal("Raw query with parameters", $expected); ORM::for_table('widget')->select('name')->find_many(); $expected = "SELECT `name` FROM `widget`"; |