Triggering a computation (SQL calls cells that depend on a select input somewhere else)

Community Help
1

I have the following code flow

  1. A user cans select a category(through a select input)

  2. This triggers a SQL query which creates/alters table(X) to select from a main (M) table filtering on a category. It’s a hairy filter …

  3. Several cells have queries on X, and some subsequently some tables and plots. These need to be re-run but they don’t directly depend on the select input in (1) - rather indirectly via X . Not the calls don’t explicitly need the information from the select, they just do some whole computation on X.

In summary, selecting from the select drop down triggers the ALTER/CREATE SQL call to update X but doesn’t trigger all the other cells that query X.

How can I force a dependency on this?(or is my design wrong?)

Thanks much

2

Dependencies are treated as a DAG and transitives dependencies should update when any of their predecessors is updated. But the system needs to see these dependencies, so if you are only using side effects to do the updates, they will not be detected automatically.

You’re not sharing code so I have to guess what is happening, but my advice would be to make the SQL query that “creates/alters table(X)” return a variable, and subsequent queries to be dependent on that variable.

For example:

```sql id=updated
CREATE OR REPLACE X(…)
```

```sql
FROM X WHERE ${(updated, 1)}
```
4

I simplified it further and took your suggestion

Load the table twice, once to run db.query style and once to enable sql cells. Im guessing this won’t double the read time? If so, can I get a DBClient.sql from a. DBClient.of?

Also pre-create actiontable

```js
// npm upgrade @observablehq/framework - > to upgrade
//const db = await DuckDBClient.of({afm: FileAttachment("./data/download_data.duckdb")});
// ^^ in a few weeks
import {DuckDBClient} from "npm:@observablehq/duckdb";
const db =  await DuckDBClient.of({
        pr: FileAttachment("./data/a_csv_data.csv"),
        meta: FileAttachment("./data/a_meta.csv"),
        actiontable: FileAttachment("./data/a_csv_data.csv")
    });
const sql =  await DuckDBClient.sql({
        pr: FileAttachment("./data/a_csv_data.csv"),
        meta: FileAttachment("./data/a_meta.csv"),
        actiontable: FileAttachment("./data/a_csv_data.csv")
    });

Set the multiple choice task chooser

```js
const types =  [ {'task_types': 'ALL'},...await db.query("select distinct(task_types) from pr order by 1")].map(x =>  x.task_types)
const types_chooser = view(Inputs.select(types,{label: "Types",value:"ALL",multiple: 10}))

Based on choices create my where logic (simplified here)

```js
var a = '';
if( !( (types_chooser.length==0 || types_chooser.some(x =>  x=='ALL') ))){
  a =  `where task_types in ("${types_chooser.join("','")}")`;
}else{
  a =  `where task_types in ('${types.join("','")}')`;
}

const task_filter_sql = a ;

The SQL ought to run when task_filter_sql is modified, the number of rows is put into id=actiontable

```sql id=actiontable_updated
CREATE OR REPLACE TABLE actiontable as select * from prompts ${task_filter_sql}

Q1. the last cell throws an error

Error: Parser Error: syntax error at or near "?"
LINE 1: ...E actiontable as select * from prompts ?

Q2: Now I can have other cells depend on actiontable_updated - what sort of dependence is required? if I a line In the cell such as

```js
let a = actiontable_updated;
  ``

and I do nothing else with a is that enough for the cell to be run when the drop downs have been selected?

Thanks much

5

And slight alternative, instead of sql cells, direct code (I found it easier to reason about).

import {DuckDBClient} from "npm:@observablehq/duckdb";
const db =  await DuckDBClient.of({
        prompts: FileAttachment("./data/a_csv_data.csv"),
        meta: FileAttachment("./data/a_meta.csv"),
        actiontable: FileAttachment("./data/a_csv_data.csv")
    });

Choose task types

```js
const types =  [ {'task_types': 'ALL'},...await db.query("select distinct(task_types) from prompts order by 1")].map(x =>  x.task_types)
const types_chooser = view(Inputs.select(types,{label: "Types",value:"ALL",multiple: 10}))

task_filter_sql will change when user chooses tasks

```js
var a = '';
if( !( (types_chooser.length==0 || types_chooser.some(x =>  x=='ALL') ))){
  a =  `where task_types in ('${types_chooser.join("','")}')`;
}else{
  a =  `where task_types in ('${types.join("','")}')`;
}

const task_filter_sql = a;

this cell depends on task_filter_sql (couldn’t figure out template literals)

```js
const [actiontable]= await db.query("CREATE OR REPLACE TABLE actiontable as select * from prompts "+task_filter_sql)

this cell has a useless dependenc on actiontable (above) but updates whenever the actiontable is updated which is whenevr user chooses input

```js
// ReDo metrics
const dummy1 = actiontable['Count'] + 1;
let atomic_metrics_table_sql = `
CREATE OR REPLACE TABLE atomic_metrics as select

  from actiontable
  group by base
  order by base
`
await db.query(atomic_metrics_table_sql);
const atomic_metrics_table = await db.query("select * from atomic_metrics")

and this updates when atomic_metrics_table updates

```js
display(Inputs.table(atomic_metrics_table))
6

Note this is a different type of issue: when using query interpolation with DuckDB you can only interpolate values (numbers and strings), not logical parts of queries. If you want to build a full query as a string, you have to bypass the way sql`` interpolates strings and call it like this:

sql([`CREATE OR REPLACE TABLE … ${task_filter_sql}`])
1 Like
7

So duckdb sql tagged literal behaves differently? This might be a source of confusion?

thanks

8

Yes, every tagged literal behaves differently. A strong reason in this case is to guarantee that values are properly escaped—which string interpolation cannot do.

1 Like