Using OR in SQLAlchemy
or_()
function can be useful in case of unknown number of OR query components.
For example, let's assume that we are creating a REST service with few optional filters, that should return record if any of filters return true. On the other side, if parameter was not defined in a request, our query shouldn't change. Without or_()
function we must do something like this:
query = Book.query
if filter.title and filter.author:
query = query.filter((Book.title.ilike(filter.title))|(Book.author.ilike(filter.author)))
else if filter.title:
query = query.filter(Book.title.ilike(filter.title))
else if filter.author:
query = query.filter(Book.author.ilike(filter.author))
With or_()
function it can be rewritten to:
query = Book.query
not_null_filters = []
if filter.title:
not_null_filters.append(Book.title.ilike(filter.title))
if filter.author:
not_null_filters.append(Book.author.ilike(filter.author))
if len(not_null_filters) > 0:
query = query.filter(or_(*not_null_filters))
From the tutorial:
from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))
SQLAlchemy overloads the bitwise operators &
, |
and ~
so instead of the ugly and hard-to-read prefix syntax with or_()
and and_()
(like in Bastien's answer) you can use these operators:
.filter((AddressBook.lastname == 'bulger') | (AddressBook.firstname == 'whitey'))
Note that the parentheses are not optional due to the precedence of the bitwise operators.
So your whole query could look like this:
addr = session.query(AddressBook) \
.filter(AddressBook.city == "boston") \
.filter((AddressBook.lastname == 'bulger') | (AddressBook.firstname == 'whitey'))