Basic join support for table view

[simonw]

I think it would be possible to support basic foreign key joins on the table page.

The user could specify columns that should result in a join (from a set of suggestions similar to how facets work right now) and they could then be passed as ?_join=city_id arguments.

This feature will make a lot of sense when combined with the ability to show / hide / customize columns, see #292

[simonw]

Consider https://latest.datasette.io/fixtures/facetable

With ?_join=city_id the underlying query would become this:

select
  facetable.pk as 'facetable.pk',
  facetable.created as 'facetable.created',
  facetable.planet_int as 'facetable.planet_int',
  facetable.on_earth as 'facetable.on_earth',
  facetable.state as 'facetable.state',
  facetable.city_id as 'facetable.city_id',
  facetable.neighborhood as 'facetable.neighborhood',
  facetable.tags as 'facetable.tags',
  facetable.complex_array as 'facetable.complex_array',
  facet_cities.id as 'facet_cities.id',
  facet_cities.name as 'facet_cities.name'
from
  facetable
  join facet_cities on facetable.city_id = facet_cities.id

https://latest.datasette.io/fixtures?sql=select%0D%0A++facetable.pk+as+%27facetable.pk%27%2C%0D%0A++facetable.created+as+%27facetable.created%27%2C%0D%0A++facetable.planet_int+as+%27facetable.planet_int%27%2C%0D%0A++facetable.on_earth+as+%27facetable.on_earth%27%2C%0D%0A++facetable.state+as+%27facetable.state%27%2C%0D%0A++facetable.city_id+as+%27facetable.city_id%27%2C%0D%0A++facetable.neighborhood+as+%27facetable.neighborhood%27%2C%0D%0A++facetable.tags+as+%27facetable.tags%27%2C%0D%0A++facetable.complex_array+as+%27facetable.complex_array%27%2C%0D%0A++facet_cities.id+as+%27facet_cities.id%27%2C%0D%0A++facet_cities.name+as+%27facet_cities.name%27%0D%0Afrom%0D%0A++facetable%0D%0A++join+facet_cities+on+facetable.city_id+%3D+facet_cities.id

I'm using select foo.bar as 'foo.bar' here to ensure that every column has an unambiguous name, to avoid any weirdness from joining against a table where both tables have a 'name' column.