sqlalchemy filter multiple columns

Question

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))

Answer 1

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)
    )
)