31:[] 2f:{"title":"Our World In Data Datasette search UI","cells":[{"id":0,"value":"# Our World In Data Datasette search UI\n\nThis is an example notebook that shows how to query the Datasette instance containing the text of all OWID posts, the metadata of all the variables and the configurations of all charts. It demonstrates how a given search text can be queried simultaneously in variables, posts and charts.","pinned":false,"hidden":false,"mode":"md"},{"id":8,"value":"viewof searchText = Inputs.text({label: \"Search text\", placeholder: \"e.g. Poverty share\"}) //html``","pinned":false,"hidden":false,"mode":"ojs"},{"id":167,"value":"html`

Posts\n

${Posts.length} posts found

${tag}

Charts\n

${Charts.length} charts found

${tag}

Variables\n

${Variables.length} charts found

${variable.name}

\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":24,"value":"select id, name \nfrom variables \nwhere name LIKE ${debouncedLikeText}\norder by id desc","pinned":true,"hidden":false,"mode":"sql","output":"Variables","database":"var:owid"},{"id":105,"value":"select id, \n printf(\"https://ourworldindata.org/grapher/%s\", JSON_EXTRACT(config, '$.slug')) as url,\n JSON_EXTRACT(config, '$.type') as type,\n JSON_EXTRACT(config, '$.title') as title,\n JSON_EXTRACT(config, '$.subtitle') as subtitle,\n JSON_EXTRACT(config, '$.note') as footnote\nfrom charts \nwhere JSON_EXTRACT(config, '$.subtitle') LIKE ${debouncedLikeText} \n OR JSON_EXTRACT(config, '$.title') LIKE ${debouncedLikeText} \n OR JSON_EXTRACT(config, '$.note') LIKE ${debouncedLikeText}\norder by id desc","pinned":true,"hidden":false,"mode":"sql","output":"Charts","database":"var:owid"},{"id":124,"value":"select id, \n printf(\"https://ourworldindata.org/%s\", slug) as url, \n title, \n content \nfrom posts\nwhere title like ${debouncedLikeText}\norder by id desc","pinned":true,"hidden":false,"mode":"sql","output":"Posts","database":"var:owid"},{"id":136,"value":"## Internals below","pinned":false,"hidden":false,"mode":"md"},{"id":145,"value":"### Shortcomings of the current solution\nThe search currently works by doing full phrase queries which are of limited usefullness. It would be nice to switch to a breakdown by word and then AND combine a like query per word but this is a bit invovled. SQL cells can not be used for fully dynamic SQL statements (variable replacement is transpiled into SQL parameters and so can be used only for individual values, not arbitrary fragments of the SQL query). \nWe might want to go away from SQL cells anyhow though because while they have paging they are missing a few features - e.g. they can't render URLs","pinned":true,"hidden":false,"mode":"md"},{"id":162,"value":"firstTenCharts = Charts.slice(0, 10).map(\n (row) => html`${row.title}`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":155,"value":"firstTenPosts = Posts.slice(0, 10).map(\n (row) => html`${row.title}`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":9,"value":"debouncedSearchText = debounce(viewof searchText)","pinned":true,"hidden":false,"mode":"ojs"},{"id":101,"value":"debouncedLikeText = `%${debouncedSearchText}%`","pinned":true,"hidden":false,"mode":"ojs"},{"id":21,"value":"owid = new DatasetteClient(\n \"https://owid-datasette-y43qr.ondigitalocean.app/owid\"\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":44,"value":"//Inputs.table(variables.query(`SELECT * from variables ${whereClause}`))","pinned":true,"hidden":false,"mode":"ojs"},{"id":37,"value":"whereClause = { \n const whereClause = debouncedSearchText.split(\" \").filter(s => s !== \"\").map(s => `name LIKE '%${s}%'`).join(\" AND \") \n if (whereClause === \"\") return \"\"\n else return `WHERE ${whereClause}`\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":3,"value":"import {DatasetteClient} from \"@ambassadors/datasette-client\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":6,"value":"import {debounce} from \"@mbostock/debouncing-input\"","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}