127

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))
denfromufa
  • 5,610
  • 13
  • 81
  • 138
teggy
  • 5,995
  • 9
  • 38
  • 41
  • 5
    I thought that this question matched the issue that I have, but the answers don't apply to my particular scenario. If firstname is "joe" and lastname is "smith", I'm looking for a filter statement that will match when the supplied searchVar is "joe smith". I.e., the fields need to be concatenated (with space added) before the test is made. Seems like a very real-world scenario. – Groovee60 May 13 '15 at 15:12
  • 2
    @Groovee60 This is exactly what I am looking for. I would appreciate it if you'd be able to share your solution, if you found one. – Lilylakshi May 30 '17 at 11:17
  • Anyone looking for a solution to the problem @Groovee60 mentioned 7 years ago can now use [Mapped attributes](https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html#sql-expressions-as-mapped-attributes) – Jeremy Jul 30 '22 at 01:03

5 Answers5

171

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)
    )
)
MarredCheese
  • 17,541
  • 8
  • 92
  • 91
Vlad Bezden
  • 83,883
  • 25
  • 248
  • 179
  • 3
    Are there any noteworthy performance differences for these different approaches? – Miek Mar 11 '18 at 00:23
  • 2
    Most of the different approaches will end up generating the same query, so in most cases you won't see a performance difference. – Asa Stallard Apr 24 '18 at 04:12
  • 3
    I'm a bit confused. The [`filter_by` docs say](https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.filter_by) 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? – tel Oct 01 '19 at 20:36
  • 1
    filter_by takes only one argument – Kais Ben Daamech Mar 03 '21 at 10:00
  • Why would one use one approach over another? – Fabien Snauwaert Feb 15 '23 at 09:25
89

You can simply call filter multiple times:

query = meta.Session.query(User).filter(User.firstname.like(searchVar1)). \
                                 filter(User.lastname.like(searchVar2))
David Johnstone
  • 24,300
  • 14
  • 68
  • 71
  • 48
    is there any performance-difference between using multiple `filter()` methods and using the combination of multiple conditions (by `or_` or `and_`) in a single `filter`, on large mysql tables? – exAres Jul 28 '14 at 09:12
  • 10
    Would multiple `filter` calls act like a logical `AND` rather than an `OR` ? – danodonovan Jun 09 '15 at 10:15
  • 11
    I wouldnt think so - when you look at str(User.filter(cond1).filter(cond2)) generates the final sql with just the conditions "and"ed. – Shankar ARUL Nov 30 '15 at 10:09
73

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)))
gclj5
  • 1,916
  • 1
  • 13
  • 16
  • 9
    You can use `|` 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. – Daniel Kluev Aug 01 '10 at 17:05
  • 1
    Shouldn't it be `filter.or_( case1, case 2)`? – fedorqui May 23 '13 at 09:09
  • 2
    This is wrong, as question is about ORM but link leads to expressions. – user2846569 Feb 19 '15 at 08:52
  • 1
    I was using multiple filter statements before which increased latency dramatically. I changed it to the or_ and it's returning MUCH faster. Thank you @gclj5 – Jimmy Dec 17 '15 at 16:44
6

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.

Azharullah Shariff
  • 349
  • 6
  • 9
0

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))
deepdows
  • 138
  • 1
  • 4