Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

?_searchmode=raw option for running FTS searches without escaping characters #676

Closed
tunguyenatwork opened this issue Feb 20, 2020 · 9 comments · Fixed by #686
Closed

?_searchmode=raw option for running FTS searches without escaping characters #676

tunguyenatwork opened this issue Feb 20, 2020 · 9 comments · Fixed by #686
Labels

Comments

@tunguyenatwork
Copy link

tunguyenatwork commented Feb 20, 2020

After the version 0.34. I am not able to use the wildchar in the _search option( or the full text search). It will not return any result unless I specify the whole word for text search.

If I use 'match :search || "*" ' in the sql statement then it will work as expected.

@simonw
Copy link
Owner

simonw commented Feb 22, 2020

I'm afraid this is by design: supporting wildcards in that mode was more-or-less accidental and often resulted in unexpected user-facing syntax errors when users accidentally entered search text that included characters with special meaning to SQLite.

You can still execute wildcard searches but you have to do so using custom SQL statements.

I'm open to suggestions for more friendly ways to support this functionality. Would an alternative _search_wildcard= URL parameter be worth supporting I wonder? What's your specific use-case for wildcards?

@tunguyenatwork
Copy link
Author

tunguyenatwork commented Feb 22, 2020

Thanks Simon,
My use case is using Datasette for full text search type ahead. That was working pretty well. The _search_wildcard= option will be awesome. Thanks

@simonw simonw added the feature label Feb 23, 2020
@simonw simonw changed the title _search with wildchar ?_search_raw= option for running FTS searches without escaping characters Feb 23, 2020
@simonw
Copy link
Owner

simonw commented Feb 23, 2020

I'm going to call it ?_search_raw= because it will pass through the value as a raw (unprocessed) argument to SQLite FTS, which means it will expose features other than just wildcards: https://www.sqlite.org/fts5.html#full_text_query_syntax

@simonw
Copy link
Owner

simonw commented Feb 23, 2020

?_search_raw= doesn't work because it clashes with an existing feature - you can use ?_search_colname= to search just within a specific column, and there could possibly be a column called raw.

I could go with ?_searchraw= - or maybe have an additional "mode" selection like ?_search=foo*&_searchmode=raw

@simonw
Copy link
Owner

simonw commented Feb 23, 2020

Nice thing about the "search mode" concept is that I can optionally reflect it in the HTML UI in some way - maybe with a checkbox for "raw" mode that only shows up if the user hits the page with ?_searchmode=raw in the querystring.

It also hints at potentially adding further search modes in the future. Not sure if I'd do that but it's an interesting potential avenue to explore.

@simonw simonw changed the title ?_search_raw= option for running FTS searches without escaping characters ?_searchmode=raw option for running FTS searches without escaping characters Feb 23, 2020
simonw added a commit that referenced this issue Feb 23, 2020
@simonw
Copy link
Owner

simonw commented Feb 23, 2020

You can try this right now like so:

pip install https://github.com/simonw/datasette/archive/search-raw.zip

Then use the following:

?_search=foo*&_searchmode=raw`

@simonw
Copy link
Owner

simonw commented Feb 23, 2020

Still needs documentation before I can land it in master.

@tunguyenatwork
Copy link
Author

tunguyenatwork commented Feb 24, 2020

@simonw
Copy link
Owner

simonw commented Feb 25, 2020

OK, this is in master now. You can install master using:

pip install https://github.com/simonw/datasette/archive/master.zip

Documentation here: https://datasette.readthedocs.io/en/latest/json_api.html#special-table-arguments

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants