How to filter concated columns with LIKE using SQLAlchemy?

mysql, python, sqlalchemy

Solution

you were close, what you need is to construct the following query in sqla:

root@localhost [inDB]> SELECT * FROM Person;
+-----------+------------+-----------+
| person_id | first_name | last_name |
+-----------+------------+-----------+
|         1 | Bob        | Smith     |
|         2 | John       | Smith     |
+-----------+------------+-----------+
2 rows in set (0.00 sec)

root@localhost [inDB]> SELECT * FROM Person WHERE CONCAT(first_name, ' ', last_name) LIKE 'Bob Sm%';
+-----------+------------+-----------+
| person_id | first_name | last_name |
+-----------+------------+-----------+
|         1 | Bob        | Smith     |
+-----------+------------+-----------+

then it will become clear that you need a filter with concat() function:

from sqlalchemy import func
res = session.query(Person).filter(func.concat(Person.first_name, ' ', Person.last_name).like('Bob Sm%')).all()
len(res)  # is 1
res, = res
print res.first_name, res.last_name  # 'Bob Smith'

Problem

I have a users table with a `first_name` and a `last_name` column. I am trying to create a SQLAlchemy query that will perform a like on a concatanation of the two columns, IE full name. Here is an example `first_name: Bob` `last_name: Smith` `query = "bob smi"` I am looking for query something like: `session.query(Person).filter((Person.firstName + " " + Person.lastName).like(query+'%')` Such that a search for `bob smi` will return `bob smith` Thanks!

Original source