Public
Edited
Mar 10, 2023
1 fork
1 star
Insert cell
Insert cell
Insert cell
db_initial = DuckDBClient.of({
artowrks: { file: FileAttachment("artworks.parquet") },
artists: { file: FileAttachment("artists.parquet") },
lookup: { file: FileAttachment("lookup.parquet") }
})
Insert cell
Insert cell
db_state = {
let state = default_state
.map((s) =>
s.values.map((v, i) => {
let { table, field, type } = s;
return { table, field, type, i, value: v };
})
)
.flat();

await db_initial.query(
`CREATE OR REPLACE TABLE default_state("table" STRING, "field" STRING, "type" STRING, "value" STRING)`
);

await state.forEach((s) => {
db_initial.query(
`INSERT INTO default_state VALUES ('${s.table}', '${s.field}', '${s.type}', '${s.value}');`
);
});
}
Insert cell
viewof db = Inputs.input((db_state, db_initial))
Insert cell
Insert cell
Insert cell
Insert cell
viewof db.value.query(
`
SELECT "value"
FROM default_state
WHERE "table" = 'artists'
AND "field" = 'Nationality'
AND "type" = 'like'
`
)
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db.query(
`
SELECT
Nationality
, MAX(nationalitySearch.value)
, MAX(CASE WHEN nationalitySearch.value IS NOT NULL THEN true ELSE false END) AS Searched
, MAX(CASE WHEN nationality.value IS NOT NULL THEN true ELSE false END) AS Selected
, MAX(CASE WHEN displayName.value IS NOT NULL THEN true ELSE false END) AS Possible
FROM artists AS a
LEFT OUTER JOIN default_state as displayName
ON displayName.table = 'artists'
AND displayName.field = 'DisplayName'
AND a.DisplayName = displayName.value
AND displayName.type = 'in'
LEFT OUTER JOIN default_state as nationality
ON nationality.table = 'artists'
AND nationality.field = 'Nationality'
AND a.Nationality = nationality.value
AND nationality.type = 'in'
LEFT OUTER JOIN default_state AS nationalitySearch
ON nationalitySearch.table = 'artists'
AND nationalitySearch.field = 'Nationality'
AND nationalitySearch.type = 'like'
AND a.Nationality ILIKE CONCAT('%', nationalitySearch.value, '%')
GROUP BY Nationality
ORDER BY
2 DESC
, 3 DESC
, 4 DESC
, 1
`
)
Insert cell
db.sql`SELECT concat('a', 'b')`
Insert cell
Insert cell
db.sql`SELECT * FROM default_state`
Insert cell
viewof a = Inputs.input("hello")
Insert cell
a
Insert cell
Inputs.bind(Inputs.text(), viewof a)
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