Continuing from a previous post, now it's time to draw the table filled with values.
As a showcase this post is available as a github project: https://github.com/mfa/showcase-datasette-billboardjs.
For plotting I used billboard.js the first time.
First version: use the API directly with the table
Every table in datasette has a direct link to get the table as csv with specified fields.
I used this before, but this time I cannot filter the columns.
rowid which is a primary-key is plotted.
I found no solution within billboard.js to remove
rowid from the plot.
Second version: use a SQL query
This worked as expected, but only after I found out that I need to use
LIMIT instead of a
There is even a ticket about it: datasette#1200 - but I agree that LIMIT is good enough - when you know size doesn't work.
On the positive side some calculations are possible here, i.e. multiply a value by 1000 because no fractions are wanted in the y-axis.
An example query looks like this:
This version is clearly the preferred one because no addional Python code is needed.
Third version: use jinja template and a prepared query
The "I have a hammer"-solution.
Here a query is prepared in the
plugins/index_vars.py and the data is generated using a jinja-for-loop in the template.
The multiplication needed could be done in the SQL query or in the template (as shown in the showcase project).
This solution works, but needs addional Python code in the plugins folder which could be avoided (see second version).