sections: sql_queries:canned-queries-named-parameters

This data as json

id page ref title content breadcrumbs references
sql_queries:canned-queries-named-parameters sql_queries canned-queries-named-parameters Canned query parameters Canned queries support named parameters, so if you include those in the SQL you will then be able to enter them using the form fields on the canned query page or by adding them to the URL. This means canned queries can be used to create custom JSON APIs based on a carefully designed SQL statement. Here's an example of a canned query with a named parameter: 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; In the canned query configuration looks like this: [[[cog config_example(cog, """ databases: fixtures: queries: neighborhood_search: title: Search neighborhoods sql: |- 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 """) ]]] [[[end]]] Note that we are using SQLite string concatenation here - the || operator - to add wildcard % characters to the string provided by the user. You can try this canned query out here: https://latest.datasette.io/fixtures/neighborhood_search?text=town In this example the :text named parameter is automatically extracted from the query using a regular expression. You can alternatively provide an explicit list of named parameters using the "params" key, like this: [[[cog config_example(cog, """ databases: fixtures: queries: neighborhood_search: title: Search neighborhoods params: - text sql: |- 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 """) ]]] [[[end]]] ["Running SQL queries", "Canned queries"] [{"href": "https://latest.datasette.io/fixtures/neighborhood_search?text=town", "label": "https://latest.datasette.io/fixtures/neighborhood_search?text=town"}]