diff options
| -rw-r--r-- | README.markdown | 8 | ||||
| -rw-r--r-- | idiorm.php | 26 | ||||
| -rw-r--r-- | test/test_queries.php | 8 |
3 files changed, 42 insertions, 0 deletions
diff --git a/README.markdown b/README.markdown index 2c3733c..6db94ed 100644 --- a/README.markdown +++ b/README.markdown @@ -130,6 +130,14 @@ Similarly, to add a `WHERE ... NOT LIKE` clause, use: $people = ORM::for_table('person')->where_not_like('Name', '%bob%')->find_many(); +#### Set membership: `where_in` and `where_not_in` ##### + +To add a `WHERE ... IN ()` or `WHERE ... NOT IN ()` clause, use the `where_in` and `where_not_in` methods respectively. + +Both methods accept two arguments. The first is the column name to compare against. The second is an *array* of possible values. + + $people = ORM::for_table('person')->where_in('Name', array('Fred', 'Joe', 'John')->find_many(); + ##### Raw WHERE clauses ##### If you require a more complex query, you can use the `where_raw` method to specify the SQL fragment exactly. This method takes two arguments: the string to add to the query, and an array of parameters which will be bound to the string. The string should contain question marks to represent the values to be bound, and the parameter array should contain the values to be substituted into the string in the correct order. @@ -334,6 +334,14 @@ } /** + * Return a string containing the given number of question marks, + * separated by commas. Eg "?, ?, ?" + */ + protected function _create_placeholders($number_of_placeholders) { + return join(", ", array_fill(0, $number_of_placeholders, "?")); + } + + /** * Add a WHERE column = value clause to your query. Each time * this is called in the chain, an additional WHERE will be * added, and these will be ANDed together when the final query @@ -394,6 +402,24 @@ } /** + * Add a WHERE ... IN clause to your query + */ + public function where_in($column_name, $values) { + $column_name = $this->_quote_identifier($column_name); + $placeholders = $this->_create_placeholders(count($values)); + return $this->_add_where("{$column_name} IN ({$placeholders})", $values); + } + + /** + * Add a WHERE ... NOT IN clause to your query + */ + public function where_not_in($column_name, $values) { + $column_name = $this->_quote_identifier($column_name); + $placeholders = $this->_create_placeholders(count($values)); + return $this->_add_where("{$column_name} NOT IN ({$placeholders})", $values); + } + + /** * Add a raw WHERE clause to the query. The clause should * contain question mark placeholders, which will be bound * to the parameters supplied in the second argument. diff --git a/test/test_queries.php b/test/test_queries.php index 6bd22b9..aadfe46 100644 --- a/test/test_queries.php +++ b/test/test_queries.php @@ -42,6 +42,14 @@ $expected = 'SELECT * FROM `widget` WHERE `name` NOT LIKE "%Fred%"'; Tester::check_equal("where_not_like method", $expected); + ORM::for_table('widget')->where_in('name', array('Fred', 'Joe'))->find_many(); + $expected = 'SELECT * FROM `widget` WHERE `name` IN ("Fred", "Joe")'; + Tester::check_equal("where_in method", $expected); + + ORM::for_table('widget')->where_not_in('name', array('Fred', 'Joe'))->find_many(); + $expected = 'SELECT * FROM `widget` WHERE `name` NOT IN ("Fred", "Joe")'; + Tester::check_equal("where_not_in method", $expected); + ORM::for_table('widget')->limit(5)->find_one(); $expected = 'SELECT * FROM `widget` LIMIT 5'; Tester::check_equal("LIMIT clause", $expected); |