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 <abasta...@gmail.com> 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/
> topic/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.
