Repository: rclement/datasette-dashboards (language: python)

Issue:
Dynamic values for select filter type
It would be great if values for the `select` filter type could be dynamically filled with the results from a query.

Sample configuration:
```yaml
...
      filters:
        source:
          name: Source
          type: select
          db: test
          query: select distinct name from sources order by name asc
...
```


Discussion / hints:

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index ed2e10b..aba48c1 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -6,7 +6,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 ## [Unreleased]
 ### Added
-- Select filter type
+- Select filter type with static and dynamic values
 
 ### Fixed
 - Allow quotes in chart SQL query
diff --git a/README.md b/README.md
index 9435465..31d9d1c 100644
--- a/README.md
+++ b/README.md
@@ -48,6 +48,11 @@ plugins:
           name: My Category
           type: select
           options: [Option 1, Option 2, Option 3]
+        dynamic_category:
+          name: My Dynamic Category
+          type: select
+          db: jobs
+          query: SELECT region FROM jobs ORDER BY region ASC
       charts:
         analysis-note:
           library: markdown
@@ -101,6 +106,8 @@ Dashboard filters:
 | `max`     | `number`           | (optional) Filter maximum value                  |
 | `step`    | `number`           | (optional) Filter stepping value                 |
 | `options` | `list`             | (optional) Select filter options list            |
+| `db`      | `string`           | (optional) Dynamic select filter database        |
+| `query`   | `string`           | (optional) Dynamic select filter query           |
 
 Common chart properties for all chart types:
 
diff --git a/datasette_dashboards/__init__.py b/datasette_dashboards/__init__.py
index e155974..80bc64e 100644
--- a/datasette_dashboards/__init__.py
+++ b/datasette_dashboards/__init__.py
@@ -31,6 +31,15 @@ async def check_permission_execute_sql(request, datasette, database):
         raise Forbidden("execute-sql denied")
 
 
+async def fill_dynamic_filters(datasette, dashboard):
+    for filter in dashboard.get("filters", {}).values():
+        if filter["type"] == "select" and "db" in filter and "query" in filter:
+            values = [
+                row[0] for row in await datasette.execute(filter["db"], filter["query"])
+            ]
+            filter["options"] = values
+
+
 def get_dashboard_filters_keys(request, dashboard):
     filters_keys = (dashboard.get("filters") or {}).keys()
     return set(filters_keys) & set(request.args.keys())
@@ -95,6 +104,7 @@ async def dashboard_view(request, datasette):
             raise NotFound(f"Database does not exist: {db}")
         await check_permission_execute_sql(request, datasette, database.name)
 
+    await fill_dynamic_filters(datasette, dashboard)
     options_keys = get_dashboard_filters_keys(request, dashboard)
     query_parameters = get_dashboard_filters(request, options_keys)
     query_string = generate_dashboard_filters_qs(request, options_keys)
diff --git a/demo/metadata.yml b/demo/metadata.yml
index 66e1810..bba02e2 100644
--- a/demo/metadata.yml
+++ b/demo/metadata.yml
@@ -24,6 +24,11 @@ plugins:
           name: Source
           type: select
           options: [Apec, RegionsJob, Indeed]
+        region:
+          name: Region
+          type: select
+          db: jobs
+          query: SELECT DISTINCT region FROM offers_view WHERE region IS NOT NULL ORDER BY region ASC
       charts:
         analysis-note:
           library: markdown
@@ -61,7 +66,7 @@ plugins:
         offers-count:
           title: Total number of offers
           db: jobs
-          query: SELECT count(*) as count FROM offers_view WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] [[ AND source = :source ]];
+          query: SELECT count(*) as count FROM offers_view WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] [[ AND source = :source ]] [[ AND region = :region ]];
           library: metric
           display:
             field: count
@@ -71,7 +76,7 @@ plugins:
         offers-day:
           title: Number of offers by day
           db: jobs
-          query: SELECT date(date) as day, count(*) as count FROM offers_view WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] [[ AND source = :source ]] GROUP BY day ORDER BY day
+          query: SELECT date(date) as day, count(*) as count FROM offers_view WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] [[ AND source = :source ]] [[ AND region = :region ]] GROUP BY day ORDER BY day
           library: vega
           display:
             mark: { type: line, tooltip: true }
@@ -82,7 +87,7 @@ plugins:
         offers-source:
           title: Number of offers by source
           db: jobs
-          query: SELECT source, count(*) as count FROM offers_view WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] GROUP BY source ORDER BY count DESC
+          query: SELECT source, count(*) as count FROM offers_view WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] [[ AND region = :region ]] GROUP BY source ORDER BY count DESC
           library: vega
           display:
             mark: { type: arc, tooltip: true }
@@ -100,7 +105,7 @@ plugins:
         offers-day-source:
           title: Offers by day by source
           db: jobs
-          query: SELECT date(date) as day, source, count(*) as count FROM offers_view WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] GROUP BY day, source ORDER BY day
+          query: SELECT date(date) as day, source, count(*) as count FROM offers_view WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] [[ AND region = :region ]] GROUP BY day, source ORDER BY day
           library: vega
           display:
             mark: { type: bar, tooltip: true }


Test patch:
diff --git a/tests/conftest.py b/tests/conftest.py
index 8c538ea..5a335f4 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -62,6 +62,12 @@ def datasette_metadata():
                             "type": "select",
                             "options": ["Option 1", "Option 2", "Option 3"],
                         },
+                        "select_filter_query": {
+                            "name": "Dynamic Select Filter",
+                            "type": "select",
+                            "db": "test",
+                            "query": "SELECT DISTINCT job FROM jobs",
+                        },
                         "unknown_filter": {
                             "name": "Unknown Filter",
                             "type": "unknown",
diff --git a/tests/test_dashboard_view.py b/tests/test_dashboard_view.py
index a9b8328..b063809 100644
--- a/tests/test_dashboard_view.py
+++ b/tests/test_dashboard_view.py
@@ -29,9 +29,15 @@ async def test_dashboard_views(datasette):
             if flt["type"] == "select":
                 assert f'<select id="{key}" name="{key}">' in response.text
                 assert '<option value="" selected></option>' in response.text
-                for option in flt["options"]:
-                    assert f'<option value="{option}">'
-                    assert f"{option}</option>"
+                if "query" in flt:
+                    values = await datasette.execute("test", flt["query"])
+                    for option in values:
+                        assert f'<option value="{option[0]}"' in response.text
+                        assert f"{option[0]}</option>" in response.text
+                else:
+                    for option in flt["options"]:
+                        assert f'<option value="{option}"' in response.text
+                        assert f"{option}</option>" in response.text
             else:
                 assert (
                     f'<input id="{key}" name="{key}" type="{expected_type}"'