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/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.
