10

I'm building an interface on Flask using SQLAlchemy and part of it is a search API. Essentially a type-ahead input is calling the server with its value (for example an email) and then the server is performing a SQLalchemy query using .like in a filter like below

q = session.query(User).filter(User.email.like('%'+term+'%')).all()

This query isn't really returning anything useful and after the first few characters, nothing at all. But if I perform the same query with the term hardcoded, like so:

q = session.query(User).filter(User.email.like('%mysearchterm%')).all()

It will return results perfectly fine, so there's something with how I'm putting the term into the like() method but I really can't figure out what the issue is. The term is coming in from an ajax POST and the value is there on the server-side, just .like() isn't using it correctly.

By "nothing useful" I mean that the first sets of results coming back have nothing to do with the actual term being inputted, after a term of length higher than 3-4 no results are returned back despite matching items existing in the DB.

Any help greatly appreciated.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
jduren
  • 630
  • 5
  • 11
  • 1
    Are you SURE that term is what you expect? If you create the like string '%'+term+'%' in a variable, and print the variable, are you getting what you expect? Also, what does "This query isn't really returning anything userful" mean? Finally, consider [turning on logging](http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html#configuring-logging) and seeing what the SQL generated actually is. – Mark Hildreth Sep 14 '12 at 05:15
  • In the method inside my flask app i'm printing the term before the query is executed and I can see the term pop up in my console so the term is making it into the function. I just tried print '%'+term'%' and its outputting %myterm% in the console as well. By not really useful I mean the first sets of results coming back have nothing to do with the term, after abotu 3-4 characters in, no results are returned. – jduren Sep 14 '12 at 05:45

1 Answers1

7

Issues been resolved. This query sat inside of a larger query builder function which applied limit and offset to the query later in the function, because the limit and offset were higher than the amount of results back the returned result set was empty.

Can chock this one up to bad human error and lack of sleep.

jduren
  • 630
  • 5
  • 11