The avatar for @danyx
Daniel Bachler
Published
OWID Datasette
Edited
Jan 5, 2022
1 fork
2 stars
OWID Datasette
Our World In Data - outdated charts finderOur World In Data posts browser
Our World In Data Datasette search UI
Our World In Data charts visualisationOur World In Data posts histogram
Insert cell
Insert cell
Insert cell
Insert cell
owid
select id, name
from variables
where name LIKE ${debouncedLikeText}
order by id desc
Insert cell
owid
select id,
printf("https://ourworldindata.org/grapher/%s", JSON_EXTRACT(config, '$.slug')) as url,
JSON_EXTRACT(config, '$.type') as type,
JSON_EXTRACT(config, '$.title') as title,
JSON_EXTRACT(config, '$.subtitle') as subtitle,
JSON_EXTRACT(config, '$.note') as footnote
from charts
where JSON_EXTRACT(config, '$.subtitle') LIKE ${debouncedLikeText}
OR JSON_EXTRACT(config, '$.title') LIKE ${debouncedLikeText}
OR JSON_EXTRACT(config, '$.note') LIKE ${debouncedLikeText}
order by id desc
Insert cell
owid
select id,
printf("https://ourworldindata.org/%s", slug) as url,
title,
content
from posts
where title like ${debouncedLikeText}
order by id desc
Insert cell
Insert cell
### Shortcomings of the current solution
The 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).
We 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
Insert cell
firstTenCharts = Charts.slice(0, 10).map(
(row) => html`<a href=${row.url}>${row.title}</a>`
)
Insert cell
firstTenPosts = Posts.slice(0, 10).map(
(row) => html`<a href="${row.url}">${row.title}</a>`
)
Insert cell
debouncedSearchText = debounce(viewof searchText)
Insert cell
debouncedLikeText = `%${debouncedSearchText}%`
Insert cell
owid = new DatasetteClient(
"https://owid-datasette-y43qr.ondigitalocean.app/owid"
)
Insert cell
//Inputs.table(variables.query(`SELECT * from variables ${whereClause}`))
Insert cell
whereClause = {
const whereClause = debouncedSearchText.split(" ").filter(s => s !== "").map(s => `name LIKE '%${s}%'`).join(" AND ")
if (whereClause === "") return ""
else return `WHERE ${whereClause}`
}
Insert cell
import {DatasetteClient} from "@ambassadors/datasette-client"
Insert cell
import {debounce} from "@mbostock/debouncing-input"
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more