Issue: Chaining separate .where() clauses with OR instead of AND
Author: bryhoyt
State: closed
Created: 2014-07-13 23:40:26

I have an existing query in my code that I wish to further extend the query with additional WHERE criteria, but chained with OR instead of AND.

I can't find a way to do this in peewee. Am I missing something? I am aware that I can dynamically chain multiple clauses with OR using `reduce(operator.or_, clauses)`, but this is a different thing -- it doesn't help me to extend an additional query.

I believe I can achieve this with something like `query._where = query._where | additional_criteria`, but this feels like a hack that requires knowledge of peewee's implementation details, not the published API -- always a bad idea.

I'm currently tinkering with a helper function something like the following:

```
def orwhere(query, *expressions):
    import operator
    query = query.clone()
    reduced = reduce(operator.and_, expressions)
    query._where = (query._where or True) | reduced  # `or True` so that if it's the first expression in the clause, it still behaves consistently by broadening the queryset rather than restricting it.
    return query
```

What do you think about adding .orwhere() to the query API? Or is there a better way that I'm missing?


I think I'm going to leave the API as-is, but thank you for the suggestion.


Thanks for getting back to me really quickly. This does leave me with a couple questions:

I love Peewee, and I love how using an ORM means I can factor my queries by using separate functions to further refine a query, without the messy string manipulation that would be necessary to factor plain-old-sql queries. But (without hacking peewee's internals) I can only refine existing queries with further restrictions, not extend them with further allowable criteria.

In most cases, this isn't a problem, because you can write your query from scratch. But I have a use-case in flask-admin, where I want to override a ModelView's `get_list()` method to further extend the search. I have a `Picture` model, where each `Picture` has multiple `Tags`. The builtin flask-admin search will filter on various columns I give it, but I want to include pictures that have tags included in the search keywords.

Can you make a better suggestion than hacking into peewee's internals? 


Sorry for necroposting but I have exactly the same issue. Actually this problem is not related with peewee. E.g. django ORM has the same API. If you want to extend your query you need to work with expression instead of query:

    expression = (Model.a == 'x') & (Model.b == 'y')
    if something:
        expression |= (Model.c == 'z')

    result = Model.select().where(expression)


Yes, necroposting but this really helped me @askabelin. 
Following up on that I'll post how I solve my problem:

```
for item in list:
    if item is not None:
        expression &= (getattr(Model,item) == 'x')

result = Model.select().where(expression)
```

The best way is to:

```python
import operator
from functools import reduce

expr_list = [
    (Foo.bar == 1),
    (Foo.baz == 2),
    (Foo.biz == 4)]

orexpr = reduce(operator.or_, expr_list)
```

You can use `operator.and_` to AND- them together.

The original problem here was not chaining expressions together, but extending the WHERE clause of a full query. If you have a situation where you're provided with an existing query (`query = Model.select().where()`), there's no clear way to extend that query with an OR clause, when you don't have direct access to the underlying WHERE expression.

@coleifer added `.orwhere()` for this purpose in https://github.com/coleifer/peewee/commit/f5ca758358f4de88e337399cd156b044208e167c but I notice that `.orwhere()` no longer exists in the latest peewee.py (unless I'm missing something). Did you decide it wasn't worth keeping in there?


I've re-added orwhere, thanks for the comment and the clarification:

d8ff4d2bffd7ed1db97a6ddd016e8db3a73b3437

@askabelin Thank You for the answer. I need to write a conditioning script, which should change due to global parameter. Now I have a long if-else case:
```
        if q is not None and ordering.find('starting_time'):
            tasks = Task.select().where(Task.name.contains(q)).order_by(Task.start_time).paginate(page, per_page)
        elif q is None and ordering.find('starting_time'):
            tasks = Task.select().order_by(Task.start_time)
```
Can You help how can I make it easier?
Thank You in advance!


```python
tasks = Task.select().order_by(Task.start_time)
if q is not None:
    tasks = tasks.where(Task.name.contains(q))
```