vrana/notorm — Issue #35: Better support for OR operator
Author: xpavp03
Created: 2011-12-12 11:06:27+00:00
State: open

NotORM is a great piece of work. It saves my time and makes my code cleaner. Thank you!

The following is a feature suggestion. Current way of handling conditions with OR in between is incomplete, in my opinion.

Flaws of the current method:
1) NotORM_Result->where() doesn't support arrays and NotORM results if they're used along with parameter markers
```where("(id IN ?) OR (attr = ?)", $array, $scalar)```
doesn't work, as opposed to a simple call
```where('id', $array)```

2) conditions joined with OR cannot be built gradually with the same comfort as those joined with AND

To resolve (1), we could rewrite where() to treat all cases the same way. The way to go would probably be to isolate parameters treatment into a separate method. While this would be quite easy to implement, it still wouldn't make using OR operators as natural as AND.

A solution that would target both issues would unfortunately require new API commands and changes throughout NotORM_Result. My idea is something like:
```$db->my_table->or()->where('id', $subQuery)->where('title LIKE ?', '%foo%'))->and()->where('title LIKE ?', '%bar%');```

This would execute the following query:
```SELECT * FROM my_table WHERE ((id IN (...)) OR (title LIKE '%foo%')) AND (title LIKE '%bar%')```

NotORM would start in AND mode (the current behaviour) and calls to or()/and() would switch the mode back and forth.

An alternative way to pass OR conditions would be via a multi dimensional array:

$db->my_table->where(array(
'or' => array(
'id' => $subQuery,
'title LIKE ?' => '%foo%'
),
'title LIKE ?' => '%bar%'
));

To add another OR at the end, you'd have to rewrite the parameters as follows:

$db->my_table->where(array(
'or' => array(
'and' => array(
'or' => array(
'id' => $subQuery,
'title LIKE ?' => '%foo%'
),
'title LIKE ?' => '%bar%'
),
'title' => 'something',
),
));

Please let me know what you think.
Petr