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