CODEWITHSUNDEEP
pythonsqldatabasesqlalchemy
teggy
teggy

Reputation: 6315

sqlalchemy filter multiple columns

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

Upvotes: 143

Views: 245835

Answers (5)

deepdows
deepdows

Reputation: 148

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

Upvotes: 1

Azharullah Shariff
Azharullah Shariff

Reputation: 357

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.

Upvotes: 6

Vlad Bezden
Vlad Bezden

Reputation: 89527

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

Upvotes: 189

gclj5
gclj5

Reputation: 1956

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

Upvotes: 76

David Johnstone
David Johnstone

Reputation: 24450

You can simply call filter multiple times:

query = meta.Session.query(User).filter(User.firstname.like(searchVar1)). \
                                 filter(User.lastname.like(searchVar2))

Upvotes: 94

Related Questions