Ability to display facet counts for many-to-many relationships

[simonw]

Parent: #354

[simonw]

Here's the query I'm playing with for facet counts:

https://datasette-m2m-work-in-progress.now.sh/russian-ads-e8e09e2?sql=select+target_id%2C+count%28*%29+as+n+from+ad_targets%0D%0Awhere%0D%0A++target_id+not+in+%28%22ec3ac%22%2C+%22e128e%22%29%0D%0A++and+ad_id+in+%28select+ad_id+from+ad_targets+where+target_id+%3D+%22ec3ac%22%29%0D%0A++and+ad_id+in+%28select+ad_id+from+ad_targets+where+target_id+%3D+%22e128e%22%29%0D%0Agroup+by+target_id+order+by+n+desc%3B

select target_id, count(*) as n from ad_targets
where
  target_id not in ("ec3ac", "e128e")
  and ad_id in (select ad_id from ad_targets where target_id = "ec3ac")
  and ad_id in (select ad_id from ad_targets where target_id = "e128e")
group by target_id order by n desc;

[simonw]

One question here is how these facets should be defined in the table page query string.

#427 started exploring this.

For any m2m facet we need to know:

• what is the join table?
• how is the join table related to our current table?
• what is the table on the other side of the relationship?
• how does that table relate to the join table?
• how should that table be displayed (what's the label column)?

The simplest form of m2m relationship can be automatically derived from just knowing the table. We can support that like so:

?_facet_m2m=tagged

This could work automatically if the following constraints turn out to apply:

• the tagged table has a foreign key back to our table, against our primary key
• the tagged table has a single other foreign key to one other table
• that other table has a single text column we can use as the label (or has a label column defined in metadata)

If any of the above rules don't hold, I think the solution is to have explicit configuration. Per #427 this will likely be done using JSON in the query string. Something like this (would be one line but indented for readability):

?_facet_m2m={
  "through":"tagged",
  "through_fk_us":"tree_id",
  "other":"tags",
  "through_fk_other":"tag_id",
  "other_label": "tag"
}

Probably also need a way of specifying the outbound column used on both us and other - if the m2m table isn't linking to the foreign keys.

I don't yet like the names of the above keys.

[simonw]

Implemented in d923d84

Documentation here: https://datasette.readthedocs.io/en/latest/facets.html#facet-by-many-to-many

Demo here: https://latest.datasette.io/fixtures/roadside_attractions

[simonw]

I went with a much more simple URL scheme: ?_facet_m2m=destination_table (it then figures out which the middle table is by looking at the foreign keys).

This can be extended to be more complicated in the future if needed.