You may need also to make some cleansing for multiple white spaces with
replace before your final split and add some logic for middle name and add
further where clause condition...
where_clauses = []
where_clauses.append((db[request.args(0)].fieldname <=
request.vars.input_entry))
query = reduce(lambda a, b: a | b, where_clauses)
rows = db(query).select(*output_columns)
But your contains() solution may be simpler...
Richard
On Thu, Mar 9, 2017 at 4:17 PM, Richard Vézina <ml.richard.vez...@gmail.com>
wrote:
> single_input_first_last_name.split(' ')[0]
> single_input_first_last_name.split('
> ')[1] can workin case your user input first and last name in a proper order
> or you search both first and last name 4 times if you don't know in which
> order you get them with OR ('|') you will get everythings out you have in
> the database....
>
> Richard
>
> On Thu, Mar 9, 2017 at 3:46 PM, Jim Steil <ato.st...@gmail.com> wrote:
>
>> 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 <abasta...@gmail.com> 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
>>>>> web2py+unsubscr...@googlegroups.com.
>>>>> 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/to
>>> pic/web2py/3RnGSoUbxxY/unsubscribe.
>>> To unsubscribe from this group and all its topics, send an email to
>>> web2py+unsubscr...@googlegroups.com.
>>> 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 web2py+unsubscr...@googlegroups.com.
>> 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
