Table view should support filtering via many-to-many relationships

[simonw]

Parent: #354

[simonw]

I started playing with this in the m2m branch - work so far: 295d005...af4ce46

Here's a demo: https://datasette-m2m-work-in-progress.now.sh/russian-ads-e8e09e2/ads?_m2m_ad_targets__target_id=ec3ac&_m2m_ad_targets__target_id=e128e

[simonw]

So the design I have so far is:

?_m2m_linktablename__linktablecolumn=value

I'm concerned that this doesn't take tables or columns with __ in their name into account. Does that matter? Could I support this without them?

[simonw]

The alternative would be to use JSON:

?_m2m={"table":"ad_targets","column":"target_id","value":"ec3ac"}

[simonw]

I think I like this better. I don't think ?_m2m= is the correct name for it though.

?_through={"table":"ad_targets","column":"target_id","value":"ec3ac"} feels a little more accurate.

[simonw]

This assumes that our current table has a single, unambiguous foreign key relationship with the table indicated by the ?through= parameter.

I think that's reasonable. The JSON format could be extended to allow that side of the relationship to optionally be defined there (if the foreign key relationship is missing).

[simonw]

Filtering through one table already works - you need to know that table's primary key, then you do ?column_id=pk against the first table.

Filtering through a m2m table will be handled by the new ?_through= parameter.

I'm going to leave out filtering through a second levels of joins for the moment. Potentially that could be added later as some extra complicated JSON.

[simonw]

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

[simonw]

Demo: https://latest.datasette.io/fixtures/roadside_attractions?_through={%22table%22:%22roadside_attraction_characteristics%22,%22column%22:%22characteristic_id%22,%22value%22:%221%22}

[simonw]

I'm re-opening because we need a UI mechanism for deselecting this:

[simonw]

I think an approach similar to how ?_where= works would do the job here.

Can address this feedback from @psychemedia while I'm at it: #429 (comment)