Public
Edited
Mar 10, 2023
1 fork
1 star
db_initial = DuckDBClient.of({
artowrks: { file: FileAttachment("artworks.parquet") },
artists: { file: FileAttachment("artists.parquet") },
lookup: { file: FileAttachment("lookup.parquet") }
})
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}');`
);
});
}
viewof db = Inputs.input((db_state, db_initial))
viewof db.value.query(
`
SELECT "value"
FROM default_state
WHERE "table" = 'artists'
AND "field" = 'Nationality'
AND "type" = 'like'
`
)
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
`
)
db.sql`SELECT concat('a', 'b')`
db.sql`SELECT * FROM default_state`
viewof a = Inputs.input("hello")
a
Inputs.bind(Inputs.text(), viewof a)
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.