How do I query results from a previous sql query?

[zookini]

Suppose I've loaded a table fruits into sql and I run a query:

select *
from fruits
where fruit = 'apple'

Can I later query the results of this query from another sql (or js) block e.g.:

select *
from apples
where color = 'red'

[mbostock]

We don’t currently support this directly. Probably the most natural way of doing this would be to create a view, and then query the view. You can do that using DuckDBClient.sql like so:

const sql = await DuckDBClient.sql({athletes: FileAttachment("athletes.csv")});
await sql`
  CREATE VIEW cyclists AS SELECT * FROM athletes WHERE sport = 'cycling';
  CREATE VIEW gymnasts AS SELECT * FROM athletes WHERE sport = 'gymnastics';
`;

Then you can query as:

SELECT * FROM gymnasts;

Maybe we could add some syntax to make this more convenient.

[zookini]

Yeah, I think this could work if we know the query ahead of time. What if cycling or gymastics were chosen dynamically e.g. from Inputs.select? I expect if I create a view after the input, it is not assigned to a global var so may execute out of order? Perhaps I can assign the result to a var e.g. sql id=apples, that'll be an arrow table. Can I then register that arrow table so it's accessible from sql?

[Fil]

Here's a possibility:

```js
const db = await DuckDBClient.of({athletes: FileAttachment("/javascript/athletes.csv")});
const sports = await db.query(`SELECT DISTINCT(sport) FROM athletes`)
  .then(v => Array.from(v, ({sport}) => sport));
const sport = view(Inputs.select(sports, {label: "sport"}));
```

```js
const sql = db.sql.bind(db);
await db.query(`CREATE OR REPLACE VIEW selection AS FROM athletes WHERE sport = '${sport}';`);
```

```sql
SELECT * FROM selection
```

(For some reason (unknown to me) we can't use the usual escaping with ? on the CREATE statement, it errors with "This type of statement can't be prepared".)

[zookini]

Hm, does this put us in the odd situation that we cannot use sql blocks before input selection? Then once we initialise sql we can't create any other views?

I think due to Observable's reactive model, DuckDB currently doesn't feel like a good fit compared with something like arquero dataframes. Lets use a more complicated example with 2 levels of query dependencies:

const sports = aq.table({
    person: ["John", "Ben", "George"],
    sport: ["cycling", "gymanastics", "cycling"],
    height: [170, 175, 180]});

const inputSport = view(Inputs.select(Array.from(sports.select("sport").dedupe(), ({sport}) => sport)))

const sport = sports.params({"filter": inputSport}).filter((d, $) => d.sport === $.filter)

Inputs.table(sport)

const inputHeight = view(Inputs.range([150, 210], {step: 5}))

const sportHeight = sport.params({"filter": inputHeight}).filter((d, $) => d.height >= $.filter);

Inputs.table(sportHeight)

This is simple and intuitive. It seems like observable (and probably javascript data analysis in general) is heading in the direction of DuckDB. Can we make DuckDB on observable as intuitive?

[Fil]

does this put us in the odd situation

I don't think so. Here's another example using sql front matter, and two ways to query it reactively:

---
sql:
  athletes: javascript/athletes.csv
---

```sql id=sports
SELECT DISTINCT (sport) FROM athletes
```

```js
const sport = view(Inputs.select(Array.from(sports, ({sport}) => sport), {label: "sport"}));
```

```sql id=selection1
SELECT * FROM athletes WHERE sport = ${sport}
```

${selection1.numRows}

---

```js
const zeview = await sql([`CREATE OR REPLACE VIEW selection2 AS FROM athletes WHERE sport = '${sport}';`]);
```

```sql
SELECT * FROM selection2 WHERE ${!!zeview}
```

The only challenge in the second approach is to point to the name of the cell (CREATE OR REPLACE VIEW) on which we depend. (Note that !!zeview is 'true'—the important thing here is to reference zeview in the variable, it could be (zeview, 1) instead.)

[zookini]

This works but it introduces accidental complexity that grows with the number of intermediate/derived tables we use. It's complexity that we don't need to contend with in incumbent notebooks like jupyter so I can see it being a difficult sell to convince my peers to give framework a chance

[Fil]

I don't see how jupyter avoids this complexity when information needs to flow from sql to js (to create inputs), then sql again (to use the input value), etc? Can you share a concrete example where jupyter would be simple but framework would be complex?