?_where=sql-fragment parameter for table views

[simonw]

Only available if arbitrary SQL is enabled (the default).

?_where=id in (1,2,3)&_where=id in (select tag_id from tags)

Allows any table (or view) page to have arbitrary additional extra_where clauses defined using the URL!

This would be extremely useful for building JavaScript applications against the Datasette API that only need on extra tiny bit of SQL but still want to benefit from other table view features like faceting.

Would be nice if this could take :named parameters and have them filled in via querystring as well.

[simonw]

I originally thought of this as a plugin but then realized that it's 100% compatible with Datasette's existing arbitrary SQL clauses and would make some of my other projects (especially involving custom queries that still need faceting) a whole lot easier.

[simonw]

Getting a prototype working was hardly any code at all:

http://127.0.0.1:8001/fixtures/facetable?_where=city_id+in+(select+id+from+facet_cities+where+name+like+%22%25an%25%22)

diff --git a/datasette/views/table.py b/datasette/views/table.py
index b7c9a4b..7ca9572 100644
--- a/datasette/views/table.py
+++ b/datasette/views/table.py
@@ -295,6 +295,10 @@ class TableView(RowTableShared):
         filters = Filters(sorted(other_args.items()), units, ureg)
         where_clauses, params = filters.build_where_clauses(table)
 
+        # Add _where= from querystring
+        if self.ds.config("allow_sql") and "_where" in request.args:
+            where_clauses.extend(request.args["_where"])
+
         # _search support:
         fts_table = special_args.get("_fts_table")
         fts_table = fts_table or table_metadata.get("fts_table")

Still needed:

• Unit tests
• Probably some kind of visual display on the table page so you know that extra clauses have been added (and maybe a UI for dropping them again)

I'm going to leave the :named parameter support out of the first version of this feature.

[simonw]

UI concept:

<h3>1 extra where clause:</h3>
<p><code>city_id in (select id from facet_cities where name like "%an%")</code> [<a href="#">remove</a>]</p>

[simonw]

Keeping track of these and building the "remove" links correctly is going to be a tiny bit fiddly.

[simonw]

Maybe put this section above the "view and edit SQL" link.

[simonw]

Documentation is here: https://datasette.readthedocs.io/en/latest/json_api.html#special-table-arguments

Demo:

• https://latest.datasette.io/fixtures/facetable?_where=state=%22MI%22&_where=city_id=3
• https://latest.datasette.io/fixtures/facetable?_where=city_id%20in%20(select%20id%20from%20facet_cities%20where%20name%20!=%20%22Detroit%22)

[psychemedia]

Minor UI observation:

_where= renders a [remove] link whereas _facet= gets a cross to remove it.