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

Wildcard support in query parameters #321

Closed
bsilverm opened this issue Jun 20, 2018 · 8 comments
Closed

Wildcard support in query parameters #321

bsilverm opened this issue Jun 20, 2018 · 8 comments
Milestone

Comments

@bsilverm
Copy link

bsilverm commented Jun 20, 2018

I haven't found a way to get the wildcard (%) inserted automatically in to a query parameter. This would be useful for cases the query parameter is followed by a LIKE clause. Wrapping the parameter name using the wildcard character within the metadata file (ie - ...where xyz like %:querystring%) does not seem to work. Can this be made possible? Or if not, can the template be extended to provide a tip to the user that they need to insert the wildcard characters themselves?

@simonw
Copy link
Owner

simonw commented Jun 21, 2018

This is a little bit fiddly, but it's possible to do it using SQLite string concatenation. Here's an example:

select * from facetable
where neighborhood like "%" || :text || "%";

Try it here: https://latest.datasette.io/fixtures-35b6eb6?sql=select+*+from+facetable+where+neighborhood+like+%22%25%22+%7C%7C+%3Atext+%7C%7C+%22%25%22%3B&text=town

2018-06-20 at 9 33 pm

@simonw simonw added this to the 0.23.1 milestone Jun 21, 2018
@simonw simonw closed this as completed in 3683a6b Jun 21, 2018
@simonw
Copy link
Owner

simonw commented Jun 21, 2018

I've added this to the unit tests and the documentation.

Docs: http://datasette.readthedocs.io/en/latest/sql_queries.html#canned-queries
Canned query demo: https://latest.datasette.io/fixtures/neighborhood_search?text=town

New unit test:

datasette/tests/test_api.py

Lines 333 to 344 in 3683a6b

def test_canned_query_with_named_parameter(app_client):
response = app_client.get(
"/fixtures/neighborhood_search.json?text=town"
)
assert [
["Corktown", "Detroit", "MI"],
["Downtown", "Los Angeles", "CA"],
["Downtown", "Detroit", "MI"],
["Greektown", "Detroit", "MI"],
["Koreatown", "Los Angeles", "CA"],
["Mexicantown", "Detroit", "MI"],
] == response.json["rows"]

datasette/tests/fixtures.py

Lines 145 to 153 in 3683a6b

'queries': {
'pragma_cache_size': 'PRAGMA cache_size;',
'neighborhood_search': '''
select neighborhood, facet_cities.name, state
from facetable
join facet_cities on facetable.city_id = facet_cities.id
where neighborhood like '%' || :text || '%'
order by neighborhood;
'''

@bsilverm
Copy link
Author

bsilverm commented Jun 21, 2018

Perfect, thank you!!

@bsilverm
Copy link
Author

bsilverm commented Jun 21, 2018

One thing I've noticed with this approach is that the query is executed with no parameters which I do not believe was the case previously. In the case the table contains a lot of data, this adds some time executing the query before the user can enter their input and run it with the parameters they want.

@bsilverm
Copy link
Author

bsilverm commented Jun 21, 2018

Those queries look identical. How can this be prevented if the queries are in a metadata.json file?

@simonw
Copy link
Owner

simonw commented Jun 21, 2018

I may have misunderstood your problem here.

I understood that the problem is that when using the "%" || :text || "%" construct the first hit to that page (with an empty string for :text) results in a where neighborhood like "%%" query which is slow because it matches every row in the database.

My fix was to add this to the where clause:

where :text != '' and ...

Which means that when you first load the page the where fails to match any rows and you get no results (and hopefully instant loading times assuming SQLite is smart enough to optimize this away). That's why you don't see any rows returned on this page: https://latest.datasette.io/fixtures-cafd088?sql=select+neighborhood%2C+facet_cities.name%2C+state%0D%0Afrom+facetable%0D%0A++++join+facet_cities+on+facetable.city_id+%3D+facet_cities.id%0D%0Awhere+%3Atext+%21%3D+%22%22+and+neighborhood+like+%27%25%27+%7C%7C+%3Atext+%7C%7C+%27%25%27%0D%0Aorder+by+neighborhood%3B

@bsilverm
Copy link
Author

bsilverm commented Jun 21, 2018

Oh I see.. My issue is that the query executes with an empty string prior to the user submitting the parameters. I'll try adding your workaround to some of my queries. Thanks again,

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

No branches or pull requests

2 participants