Filter UI on table page

[simonw]

A UI for building up simple table queries by adding additional filter rules that get executed as query parameters in the URL.

[simonw]

Now that we have foreign key support (#85) this is even more important, since foreign key support actively encourages linking to filtered table views.

[simonw]

If the selected relationship is a foreign key reference, we should resolve that foreign key and display it on the page.

[simonw]

OK,I've figured out how to do an initial version of this without JavaScript. I'll provide three form fields labell d "add filter":

• a select box of all of the columns
• a select box of the available operations
• a value box

Submit those and the site will redirect you to a correctly populated querystring for that filter.

If you have filters applied, those will display as prepopulated form field triples.

For foreign key reference filters, I will display the resolved value next to the text box containing the numeric ID. In the future this can get a select2 style treatment.

[simonw]

I'm going to be a bit classier about this and auto generate a title for the page that describes the currently applied filters.

[simonw]

"Tablename: 3,567 rows where status = 3 (published) and n > 55"

[simonw]

I'll have to refactor the foreign key annotating code to be usable in other contexts - at the moment it only works for annotating displays of rows, but I need to use it to resolve selected filters as well.

[simonw]

I need a nicer abstraction around the concept of filters. It needs to be able to:

• convert querystring parameters into filters
• convert filters into a querystring
• iterate through currently applied filters
• convert selected filters into a human description (e.g. for a title)
• expand filters that involve a foreign key
• add filters
• remove filters
• define different types of filters

It should replace my current build_where_clauses implementation, in particular this bit:

datasette/datasette/utils.py

Lines 38 to 56 in a5881e1

	template = {
	'exact': '"{}" = :{}',
	'contains': '"{}" like :{}',
	'endswith': '"{}" like :{}',
	'startswith': '"{}" like :{}',
	'gt': '"{}" > :{}',
	'gte': '"{}" >= :{}',
	'lt': '"{}" < :{}',
	'lte': '"{}" <= :{}',
	'glob': '"{}" glob :{}',
	'like': '"{}" like :{}',
	'isnull': '"{}" is null',
	}[lookup]
	numeric_operators = {'gt', 'gte', 'lt', 'lte'}
	value_convert = {
	'contains': lambda s: '%{}%'.format(s),
	'endswith': lambda s: '%{}'.format(s),
	'startswith': lambda s: '{}%'.format(s),
	}.get(lookup, lambda s: s)

[simonw]

Here's where I am now. Needs a bit of UI tidy up and it will be good to release:

[simonw]

[simonw]

And with ef3eacf I'm ready to call this one done.