referencing parameter values in sql front matter

[c-koster]

Hi all - I've been following #245 and #1523 and have just implemented some pages with parameterized routes. I am however having a persistent issue with page loading speed.

Using the example of product sales from the docs, suppose I have a [product].md page which defines a parameterized route to look at sales measures for a particular product id. The product file is loaded in the front matter, and the product parameter is passed into the sql statement:

sql:
    product_sales: ./data/product_sales.csv

SELECT
DATE(sale_date) AS sale_day,
SUM(quantity) AS total_quantity_sold,
SUM(total_amount) AS total_sales_amount
FROM product_sales
WHERE product_id = ${observable.params.product}
GROUP BY DATE(sale_date)
ORDER BY sale_day

However, if the number of products in product_sales.csv is large, it seems like it would be better to split up each product into its own file, and load only that file as a table. I know this is now possible for passing parameters to FileAttachment but what if there were something similar for the sql front matter? e.g.:

sql:
    product_sales: ./data/product_sales_{observable.params.product}.csv

Otherwise, how do you suggest working with SQL in parameterized pages? I'm pretty sure I can do everything I need with data loaders, but it is very nice to work with the sql blocks inside the page, particularly as I've got multiple charts that rely on slightly different slices of the same data.

[mbostock]

The first option is that you can use a relative path. So if your page is at /products/[product]/index.md, you can have a data loader at /products/[product]/sales.csv.js that generates the sales data for that particular product, and then you can load the data as ./sales.csv. Because this is within the /products/[product]/ directory, the relative path to the data will be resolved to use the same parameter value.

A second option is to use DuckDBClient.sql to register the sources in JavaScript. This gives you a bit more flexibility since you can reference parameters.

```js
const sql = DuckDBClient.sql({
    product_sales: FileAttachment(`./data/product_sales_${observable.params.product}.csv`)
});
```

A third option is that you can use a page loader. In this case your page loader is at /products/[product].md.js, and since you’re in a page loader, you can generate the front matter programatically to point to ./data/product_sales_${product}.csv.

[c-koster]

Hi @mbostock,

Thanks for your suggestions. I've got things working with the first option above - it definitely did not occur to me that the directory could have a parameter in it!

[mbostock]

I realized there’s another option, probably easier than a page loader but not as easy as a relative path within a parameterized folder, which is to use DuckDBClient.sql instead of the SQL front matter. I edited my answer above.