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.
