filters: show fkey label when filtering

[cldellow]

https://github.com/simonw/datasette/issues/1696

Example: https://dux-demo.fly.dev/cooking/posts?_facet=owner_user_id&owner_user_id=67

[cldellow]

The amount of spooky action at a distance for me to patch this into Datasette from a plug in scares even me, not gonna try.

[cldellow]

In https://github.com/cldellow/datasette-ui-extras/issues/38 I learned a little bit about how this code works, and am beginning to wonder if the level of spooky might be more Casper than Bloody Mary.

Assumptions:

• it's OK to make a synchronous sqlite query to look up a value
  • if we're concerned about perf, maybe we'd check to see the column is indexed and only proceed if so

Challenge: a filter only has access to the table and column: https://github.com/simonw/datasette/blob/0b4a28691468b5c758df74fa1d72a823813c96bf/datasette/filters.py#L172-L181

We'd need to know the database name, too, so that we can find a SQLite connection to use.

A challenge is that these are created here: https://github.com/simonw/datasette/blob/0b4a28691468b5c758df74fa1d72a823813c96bf/datasette/views/table.py#L357

We could patch build_where_clauses to go spelunking in the call stack (see https://stackoverflow.com/a/6618825) to pull out the db

Then we have one of these: https://github.com/simonw/datasette/blob/main/datasette/database.py

Ah, but here I think we run into an issue. The connections that are created for asyncio would not be usable by us, as their underlying sqlite3 handle might be in use by the other thread (and would need the appropriate SQLite compile-time flag to ensure that it was OK to use it from a different thread).

It would only work if the num_sql_threads was set to 0 (ref: https://github.com/simonw/datasette/blob/0b4a28691468b5c758df74fa1d72a823813c96bf/datasette/app.py#L348-L353), which I think would practically never be true for the cases I care about.

[cldellow]

A mildly more officially sanctioned option:

• monkey-patch https://github.com/simonw/datasette/blob/0b4a28691468b5c758df74fa1d72a823813c96bf/datasette/filters.py#L259 to remove the exact filter
• implement filters_from_request to add support for the exact filter. This is an awaitable, so it can do everything we need.

Hm, I note that __exact is maybe special?

eg https://dux-demo.fly.dev/cooking/posts?owner_user_id=67 and https://dux-demo.fly.dev/cooking/posts?_sort=id&owner_user_id__exact=67 seem to both work. What's up with that? Can we take over both of them?

[cldellow]

Ah, that's this bit here: https://github.com/simonw/datasette/blob/0b4a28691468b5c758df74fa1d72a823813c96bf/datasette/filters.py#L396-L402

It'd "just work", I think.

[cldellow]

Removing the filter breaks the filters UI - it no longer has the dropdowns for = and !=.

Somehow it still manages to generate SQL? Ah, also need to patch Filters._filters_by_key.

Well, breaking the UI is not ideal. I guess we can add it back in by patching Filters.lookups?

[cldellow]

We have to operate directly on request.args, so we need to handle the __exact vs absent case.