how does search work on sql grid? (search over a whole table instead of a field)

[Kenneth]

I am curious how I can implement the search widget used on sql grid.

It seems to me that sql grid searche widget searches a keword over a whole table and show the result.

Can anyone tell me how to create a search widget to search a keyword on a table instead of selecting fields?

Thank you.

[Anthony]

See https://groups.google.com/d/msg/web2py/WvGH9XAH160/TjzYVCDBzQ0J

Anthony

[Kenneth]

Hi Anthony,

Thank you for your reply as always. I believe the forwarded post is about how to show only a search option for searching throught a whole table. so unfortunately that post doesn't answer my question.

Do you know how the search works behind of sql grid form? 

Thank you.

[Anthony]

Sorry, you said you want to "search a keyword on a table instead of selecting fields"? Perhaps you can clarify what you are trying to achieve.

Anthony

[Kenneth]

Hope this clarifies my question.

Basically I have 5 fields (last_name, middle_name, first_name, mother_full_name, father_full_name) in a table called names,

When I search a keyword "john", I would like to search it on a whole table. For example, is there any way to just do db(db.names.contains("%john%")) and it gives a list of rows with a searched keyword stored in it instead of doing this ==> db((db.names.last_name.contains("%john%")&(db.names.first_name.contains("%john%")&(db.names.mother_full_name.contains("%john%")....))

Thank you.

[Anthony]

Basically I have 5 fields (last_name, middle_name, first_name, mother_full_name, father_full_name) in a table called names,

When I search a keyword "john", I would like to search it on a whole table. For example, is there any way to just do db(db.names.contains("%john%")) and it gives a list of rows with a searched keyword stored in it instead of doing this ==> db((db.names.last_name.contains("%john%")&(db.names.first_name.contains("%john%")&(db.names.mother_full_name.contains("%john%")....))

db.names.contains(...) is not valid DAL code nor would it be able to produce valid SQL (i.e., you have to specify fields). So, you need something like your second example, but replace all the "&" operators with "|". Anyway, there is no need to do that, because the grid search already does exactly that, which is why all you have to do is disable the Javascript search widget (as described in the linked post) to get the behavior you want.

Alternatively, you can code your own search function:

def mysearch(sfields, keywords):
    keywords = keywords.strip()
    return reduce(lambda a, b: a | b, [field.contains(keywords) for field in db.names])

Then pass that as the "searchable" argument to the grid.

Anthony

[Kenny Chung]

Thank you! your customer search function example answers my question.

just curious, does SQL grid search function work same way as your search function example?

--
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/MKKxc41Nq7w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony]

The built-in search works similarly, though it includes additional logic to handle advanced search syntax, which it passes off to the smart_query function in the DAL.

Anthony