Mechanism for indicating foreign key relationships in the table and query page URLs

[simonw]

Datasette currently only inflates foreign keys (into names hyperlinks) if it detects them as foreign key constraints in the underlying database.

It would be useful if you could specify additional "foreign keys" using both metadata.json and the querystring - similar time how you can pass ?_fts_table=x https://datasette.readthedocs.io/en/stable/full_text_search.html#configuring-full-text-search-for-a-table-or-view

[simonw]

Added bonus: this would mean faceting results could be enhanced with a "more" link that points to a custom paginated SQL query - but that query could maintain the ability to expand the labels on foreign keys.

[simonw]

There are three pieces of information that need to be described here: the column, the other table and the other table column.

We already have a piece of API design that is similar to this: the _through= parameter, which looks like this:

?_through={"table":"m2m_characteristics","column":"characteristic_id","value":"1"}

I'm rethinking this syntax in #621 though to support a non-JSON variant that looks more like this:

?_through.roadside_attraction_characteristics.characteristic_id=1

[simonw]

So for foreign key definitions it could look like this:

/db/table?_fk.article_id=articles.id

Or for columns and table names that themselves contain dots it could be:

/db/table?_fk.article_id={"table":"articles","column":"id"}

The value (before the =) is unambiguous -it's ?fk.XXX where XXX could be a column name that includes periods without breaking anything.

Added bonus: if you're referencing another table's single primary key you can omit the .id entirely (since it can be automatically detected) - so you could do ?_fk.article_id=articles.

[simonw]

This new ?_fk.column_name= syntax makes me wonder if the various filters should be ?colname.contains=x rather than ?colname__contains=x - but that's a conversation for another time.

[simonw]

I think I should move the ds.expand_foreign_keys() method somewhere else:

datasette/datasette/app.py

Lines 355 to 390 in c2779e5

	async def expand_foreign_keys(self, database, table, column, values, fks=None):
	"Returns dict mapping (column, value) -> label"
	labeled_fks = {}
	db = self.databases[database]
	foreign_keys = fks or await db.foreign_keys_for_table(table)
	# Find the foreign_key for this column
	try:
	fk = [
	foreign_key
	for foreign_key in foreign_keys
	if foreign_key["column"] == column
	][0]
	except IndexError:
	return {}
	label_column = await db.label_column_for_table(fk["other_table"])
	if not label_column:
	return {(fk["column"], value): str(value) for value in values}
	labeled_fks = {}
	sql = """
	select {other_column}, {label_column}
	from {other_table}
	where {other_column} in ({placeholders})
	""".format(
	other_column=escape_sqlite(fk["other_column"]),
	label_column=escape_sqlite(label_column),
	other_table=escape_sqlite(fk["other_table"]),
	placeholders=", ".join(["?"] * len(set(values))),
	)
	try:
	results = await self.execute(database, sql, list(set(values)))
	except QueryInterrupted:
	pass
	else:
	for id, value in results:
	labeled_fks[(fk["column"], id)] = value
	return labeled_fks

Moving it to Database makes sense to me. The question we then ask it is:

"Given this column containing these values, give me back a dictionary mapping each column and value to a label - (column, value) -> label"

Passing in the foreign keys we have calculated as an argument makes sense.

[simonw]

This may be obsoleted by #1293 - it looks like I may be able to auto-detect these foreign keys for arbitrary queries after all.