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!
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'
This solution should work across all database types as the + operator is translated the SQL || operator when used between strings:
session.query(Person).filter((
Person.first_name + ' ' + Person.last_name
).like('{0}%'.format(query))
)
firstNameandlastName