How do I combine two columns and apply filter? For example, I want to search in both the "firstname" and "lastname" columns at the same time. Here is how I have been doing it if searching only one column:
query = meta.Session.query(User).filter(User.firstname.like(searchVar))
There are number of ways to do it:
Using filter() (and operator)
query = meta.Session.query(User).filter(
User.firstname.like(search_var1),
User.lastname.like(search_var2)
)
Using filter_by() (and operator)
query = meta.Session.query(User).filter_by(
firstname.like(search_var1),
lastname.like(search_var2)
)
Chaining filter() or filter_by() (and operator)
query = meta.Session.query(User).\
filter_by(firstname.like(search_var1)).\
filter_by(lastname.like(search_var2))
Using or_(), and_(), and not()
from sqlalchemy import and_, or_, not_
query = meta.Session.query(User).filter(
and_(
User.firstname.like(search_var1),
User.lastname.like(search_var2)
)
)
filter_by docs say that it's for filtering by keyword argument: query(Foo).filter_by(bar='baz'). How does that relate to the syntax you used in your answer above?
You can simply call filter multiple times:
query = meta.Session.query(User).filter(User.firstname.like(searchVar1)). \
filter(User.lastname.like(searchVar2))
filter() methods and using the combination of multiple conditions (by or_ or and_) in a single filter, on large mysql tables?
filter calls act like a logical AND rather than an OR ?
You can use SQLAlchemy's or_ function to search in more than one column (the underscore is necessary to distinguish it from Python's own or).
Here's an example:
from sqlalchemy import or_
query = meta.Session.query(User).filter(or_(User.firstname.like(searchVar),
User.lastname.like(searchVar)))
| operator instead of or_, like this - (User.firstname.like(searchVar)) | (User.lastname.like(searchVar)), however you should be careful with | precedence, without parenthesis it can produce VERY unexpected results when mixed with comparsion operators.
filter.or_( case1, case 2)?
A generic piece of code that will work for multiple columns. This can also be used if there is a need to conditionally implement search functionality in the application.
search_key = 'abc'
search_args = [col.ilike('%%%s%%' % search_key) for col in ['col1', 'col2', 'col3']]
query = Query(table).filter(or_(*search_args))
session.execute(query).fetchall()
Note: the %% are important to skip % formatting the query.
To make the filter work for the full name, you can modify the query to use the concat function provided by the database engine. Here's an example of how you can update your code to make it work:
from sqlalchemy import func
query = meta.Session.query(User).filter(func.concat(User.firstname, ' ', User.lastname).contains(searchVar))
