Here is my version info: web2py Web Framework Created by Massimo Di Pierro, Copyright 2007-2017 Version 2.14.6-stable+timestamp.2016.05.09.19.18.48 Database drivers available: pymysql, imaplib, MySQLdb, sqlite3, pg8000, pyodbc
Code used to be: queries = [db.auth_user.id > 0] queries.append((db.auth_user.firstLast.contains(searchText)) | (db.auth_user.lastFirst.contains(searchText))) query = reduce(lambda a, b: (a & b), queries) I'd hadn't figured out that I could pass text like that to my sub-query. On Thu, Mar 9, 2017 at 2:27 PM, Anthony <[email protected]> wrote: > What did your code look like, and are you using an older version of web2py? > > On Thursday, March 9, 2017 at 12:08:10 PM UTC-5, Jim S wrote: >> >> Holy Cow! I got it working. >> >> Not exactly what you specified Anthony, but got me on the right track >> >> With your method I got this traceback: >> >> Traceback (most recent call last): >> File "C:\dev\web2py\gluon\restricted.py", line 216, in restricted >> exec(ccode, environment) >> File "C:\dev\web2py\applications\connect\controllers/user.py:index", line >> 281, in <module> >> File "C:\dev\web2py\gluon\globals.py", line 405, in <lambda> >> self._caller = lambda f: f() >> File "C:\dev\web2py\gluon\tools.py", line 4299, in f >> return action(*a, **b) >> File "C:\dev\web2py\applications\connect\controllers/user.py:index", line >> 72, in index >> AttributeError: 'MySQL' object has no attribute 'CONCAT' >> >> >> However, I then tried this: >> >> queries = [db.auth_user.id > 0] >> queries.append((db.auth_user.firstLast.contains(searchText)) | >> (db.auth_user.lastFirst.contains(searchText)) | >> ("CONCAT(first_name, \' \', last_Name) LIKE '%%%s%%'" % (searchText))) >> query = reduce(lambda a, b: (a & b), queries) >> >> ...and passed the query and it worked. >> >> Much appreciated! >> >> -Jim >> >> >> >> >> On Thu, Mar 9, 2017 at 10:37 AM, Anthony wrote: >> >>> I haven't tried it, but maybe something like this: >>> >>> def search(sfields, keywords): >>> keywords = keywords.strip().replace("'", "''") >>> return "CONCAT(first_name, ' ', last_name) LIKE '%%%s%%'" % >>> keywords >>> >>> grid = SQLFORM.grid(db.mytable, searchable=search) >>> >>> Actually, although not documented as part of the public API, each >>> adapter has a CONCAT method, which produces the correct syntax for each >>> database. So, the last line of the search function above could be: >>> >>> return "%s LIKE '%%%s%%'" % (db._adapter.CONCAT('first_name', ' >>> ', 'last_name'), keywords) >>> >>> Anthony >>> >>> On Tuesday, March 7, 2017 at 11:00:04 PM UTC-5, Jim S wrote: >>>> >>>> Hi >>>> >>>> I have a search form where the user types in generic search text. I >>>> want to be able to return to them a list of matching users. >>>> >>>> Sample Data >>>> >>>> First Last >>>> - ---------- ---------- >>>> 1 Jim Sanders >>>> 2 Bill Van Der Wall >>>> 3 John St James >>>> 4 Peter Williams >>>> 5 Jim Hensen >>>> 6 John Adams >>>> 7 William Tell >>>> 8 Adam Johnson >>>> >>>> >>>> Based on the data entered in the search box, these records should be >>>> returned >>>> >>>> >>>> Search Text Rows Returned >>>> - ---------------- ------------------ >>>> 1 Jim 1, 5 >>>> 2 John 3, 6, 8 >>>> 3 Adam 6, 8 >>>> 4 Bill 2 >>>> 5 Jim Sanders 1 >>>> 6 Adam John 8 >>>> 7 John St James 3 >>>> >>>> >>>> I can't seem to come up with a query or anything to make this happen. >>>> With SQL I might to this: >>>> >>>> 'SELECT * FROM auth_user WHERE first_name LIKE \'%s*\' OR last_name >>>> LIKE \'%s*\' OR concat(first_name, ' ', last_name) LIKE \'%s*\'' % >>>> (search_text, search_text, search_text) >>>> >>>> But, I want to build this as a query for SQLFORM.grid. >>>> >>>> Anyone have any ideas? >>>> >>>> -Jim >>>> >>>> -- >>> Resources: >>> - http://web2py.com >>> - http://web2py.com/book (Documentation) >>> - http://github.com/web2py/web2py (Source code) >>> - https://code.google.com/p/web2py/issues/list (Report Issues) >>> --- >>> You received this message because you are subscribed to a topic in the >>> Google Groups "web2py-users" group. >>> To unsubscribe from this topic, visit https://groups.google.com/d/to >>> pic/web2py/3RnGSoUbxxY/unsubscribe. >>> To unsubscribe from this group and all its topics, send an email to >>> [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to a topic in the > Google Groups "web2py-users" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/web2py/3RnGSoUbxxY/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
