diff options
author | Luis Ramón López <[email protected]> | 2014-05-18 20:25:56 +0200 |
---|---|---|
committer | Luis Ramón López <[email protected]> | 2014-05-18 20:39:05 +0200 |
commit | 3f86f09173e4acf0443018121207cffb0e4fe6c7 (patch) | |
tree | e6b87f014e6b84ef34157920e7280deb6eee74fa | |
parent | ddb8a305a59592101f8fd08060a67bb100393fa1 (diff) |
Multiple OR'ed conditions support
Multiple OR'ed conditions
-------------------------
You can add simple ORed conditions to the same WHERE clause using
``where_any_is``. You should specify multiple conditions using an
array of items. Each item will be an associative array that contains
a multiple conditions.
```php
<?php
$people = ORM::for_table('person')
->where_any_is(array(
array('name' => 'Joe', 'age' => 10),
array('name' => 'Fred', 'age' => 20)))
->find_many();
// Creates SQL:
SELECT * FROM `widget` WHERE (( `name` = 'Joe' AND `age` = '10' ) OR ( `name` = 'Fred' AND `age` = '20' ));
```
By default, it uses the equal operator for every column, but it can be
overriden for any column using a second parameter:
```php
<?php
$people = ORM::for_table('person')
->where_any_is(array(
array('name' => 'Joe', 'age' => 10),
array('name' => 'Fred', 'age' => 20)), array('age' => '>'))
->find_many();
// Creates SQL:
SELECT * FROM `widget` WHERE (( `name` = 'Joe' AND `age` > '10' ) OR ( `name` = 'Fred' AND `age` > '20' ));
```
If you want to set the default operator for all the columns, just pass it as
the second parameter:
```php
<?php
$people = ORM::for_table('person')
->where_any_is(array(
array('score' => '5', 'age' => 10),
array('score' => '15', 'age' => 20)), '>')
->find_many();
// Creates SQL:
SELECT * FROM `widget` WHERE (( `score` > '5' AND `age` > '10' ) OR ( `score` > '15' AND `age` > '20' ));
```
-rw-r--r-- | docs/querying.rst | 48 | ||||
-rw-r--r-- | idiorm.php | 37 | ||||
-rw-r--r-- | test/QueryBuilderTest.php | 24 |
3 files changed, 109 insertions, 0 deletions
diff --git a/docs/querying.rst b/docs/querying.rst index 8541fa8..f86f162 100644 --- a/docs/querying.rst +++ b/docs/querying.rst @@ -306,6 +306,54 @@ Similarly, to add a ``WHERE ... NOT LIKE`` clause, use: <?php $people = ORM::for_table('person')->where_not_like('name', '%bob%')->find_many(); +Multiple OR'ed conditions +''''''''''''''''''''''''' + +You can add simple OR'ed conditions to the same WHERE clause using ``where_any_is``. You +should specify multiple conditions using an array of items. Each item will be an +associative array that contains a multiple conditions. + +.. code-block:: php + + <?php + $people = ORM::for_table('person') + ->where_any_is(array( + array('name' => 'Joe', 'age' => 10), + array('name' => 'Fred', 'age' => 20))) + ->find_many(); + + // Creates SQL: + SELECT * FROM `widget` WHERE (( `name` = 'Joe' AND `age` = '10' ) OR ( `name` = 'Fred' AND `age` = '20' )); + +By default, it uses the equal operator for every column, but it can be overriden for any +column using a second parameter: + +.. code-block:: php + + <?php + $people = ORM::for_table('person') + ->where_any_is(array( + array('name' => 'Joe', 'age' => 10), + array('name' => 'Fred', 'age' => 20)), array('age' => '>')) + ->find_many(); + + // Creates SQL: + SELECT * FROM `widget` WHERE (( `name` = 'Joe' AND `age` > '10' ) OR ( `name` = 'Fred' AND `age` > '20' )); + +If you want to set the default operator for all the columns, just pass it as the second parameter: + +.. code-block:: php + + <?php + $people = ORM::for_table('person') + ->where_any_is(array( + array('score' => '5', 'age' => 10), + array('score' => '15', 'age' => 20)), '>') + ->find_many(); + + // Creates SQL: + SELECT * FROM `widget` WHERE (( `score` > '5' AND `age` > '10' ) OR ( `score` > '15' AND `age` > '20' )); + Set membership: ``where_in`` and ``where_not_in`` ''''''''''''''''''''''''''''''''''''''''''''''''' @@ -1213,6 +1213,43 @@ } /** + * Allows adding a WHERE clause that matches any of the conditions + * specified in the array. Each element in the associative array will + * be a different condition, where the key will be the column name. + * + * By default, an equal operator will be used against all columns, but + * it can be overriden for any or every column using the second parameter. + * + * Each condition will be ORed together when added to the final query. + */ + public function where_any_is($values, $operator='=') { + $data = array(); + $query = array("(("); + $first = true; + foreach ($values as $item) { + if ($first) { + $first = false; + } else { + $query[] = ") OR ("; + } + $firstsub = true; + foreach($item as $key => $item) { + $op = is_string($operator) ? $operator : (isset($operator[$key]) ? $operator[$key] : '='); + if ($firstsub) { + $firstsub = false; + } else { + $query[] = "AND"; + } + $query[] = $this->_quote_identifier($key); + $data[] = $item; + $query[] = $op . " ?"; + } + } + $query[] = "))"; + return $this->where_raw(join($query, ' '), $data); + } + + /** * Add a WHERE ... LIKE clause to your query. */ public function where_like($column_name, $value=null) { diff --git a/test/QueryBuilderTest.php b/test/QueryBuilderTest.php index 3a4a997..3400fb6 100644 --- a/test/QueryBuilderTest.php +++ b/test/QueryBuilderTest.php @@ -82,6 +82,30 @@ class QueryBuilderTest extends PHPUnit_Framework_TestCase { $this->assertEquals($expected, ORM::get_last_query()); } + public function testWhereAnyIs() { + ORM::for_table('widget')->where_any_is(array( + array('name' => 'Joe', 'age' => 10), + array('name' => 'Fred', 'age' => 20)))->find_many(); + $expected = "SELECT * FROM `widget` WHERE (( `name` = 'Joe' AND `age` = '10' ) OR ( `name` = 'Fred' AND `age` = '20' ))"; + $this->assertEquals($expected, ORM::get_last_query()); + } + + public function testWhereAnyIsOverrideOneColumn() { + ORM::for_table('widget')->where_any_is(array( + array('name' => 'Joe', 'age' => 10), + array('name' => 'Fred', 'age' => 20)), array('age' => '>'))->find_many(); + $expected = "SELECT * FROM `widget` WHERE (( `name` = 'Joe' AND `age` > '10' ) OR ( `name` = 'Fred' AND `age` > '20' ))"; + $this->assertEquals($expected, ORM::get_last_query()); + } + + public function testWhereAnyIsOverrideAllOperators() { + ORM::for_table('widget')->where_any_is(array( + array('score' => '5', 'age' => 10), + array('score' => '15', 'age' => 20)), '>')->find_many(); + $expected = "SELECT * FROM `widget` WHERE (( `score` > '5' AND `age` > '10' ) OR ( `score` > '15' AND `age` > '20' ))"; + $this->assertEquals($expected, ORM::get_last_query()); + } + public function testLimit() { ORM::for_table('widget')->limit(5)->find_many(); $expected = "SELECT * FROM `widget` LIMIT 5"; |