Datasette facet suggestions require a repeated value

A facet is useful when it groups observations that a reader can meaningfully select together. Offering a separate bucket for every row can reproduce the table's identifiers without summarizing anything. Datasette 0.33, released on 22 December, tightens column facet suggestions so that at least one value must occur in more than one record. The change concerns when Datasette proposes a facet, rather than whether a column can ever be queried.

The same release adds rowid to table filter menus and explicitly labels queries with no rows as zero results. These changes address different parts of exploration. A reader can select an individual record when that is the task, obtain useful category suggestions when grouping is appropriate and recognize an empty selection without interpreting an unexplained blank result.

I like Datasette's use of SQLite as an inspectable publication format. It can expose data without requiring the publisher to predict every question a reader will ask. That makes the quality of automatic suggestions important. A proposed control should help the reader understand the dataset, particularly when the publisher has supplied little custom interface code.

Why a distinct count was insufficient

The 0.33 column facet implementation obtains candidate values through a grouped query. It excludes null values and calculates a count for each remaining value. The suggestion logic checks that there is more than one distinct value, that the number fits the configured facet size and that it is smaller than the row count. It now also checks that at least one grouped count exceeds one.

That last condition supplies information not contained in the number of distinct values alone. Imagine five rows with three different non-null reference codes and two nulls. The distinct non-null count is smaller than the total number of rows, yet none of the reference codes repeats. A numerical comparison between distinct count and row count does not establish a repeated non-null value when missing values account for the difference.

The added condition tests the property directly. It examines the grouped counts that the query already returns. Repeated values indicate that at least one proposed bucket collects multiple rows. This does not prove that the column is meaningful to a particular reader, but it removes a specific class of suggestions that cannot supply such a grouping.

The query has a limit based on the configured facet size plus one. The implementation also gives suggestion queries their own time limit and skips candidates interrupted by that limit. Suggestion is therefore a bounded operation inside page construction. The interface does not need to perform an unrestricted analysis of every column before displaying the underlying records.

Suggestions and requested facets are separate

The same source separates the suggest method from facet_results. Explicit facet configuration can come from table metadata or request arguments. The results method calculates grouped counts for requested column facets and orders them by count. The new repeated-value test belongs to suggestion, so describing it as a ban on faceting unique data would overstate the change.

A request can still name a column facet through the existing URL argument. For a hypothetical table with a category column, the request fragment is straightforward.

/catalogue/items?_facet=category

The source builds toggle URLs using that _facet argument. Actual result buckets also receive URLs that add or remove the corresponding column value filter. This is how the grouping becomes a navigation control. A suggestion proposes the grouping, and selecting a bucket adds a concrete predicate to the table request.

The result code attempts to expand foreign keys into labels when it knows the source table. A category represented by a foreign key can consequently remain queryable by its stored value while appearing with a more useful label. That is another reason to distinguish a category facet from a list of otherwise unique identifiers. The displayed names can help explain what a repeated category represents.

Individual rows still need filters

The version-pinned changelog separately records the addition of rowid to table filtering dropdowns. Suppressing unhelpful category suggestions should not make individual records harder to select. Exposing a row identifier in the existing filter interface gives that task an explicit control.

This is a user-interface addition, not a change to SQLite's storage engine. The release describes a menu option in the publishing tool. It does not claim to add row identifiers to table kinds that lack them or to establish a new persistent application key. The scope of the improvement is access to filtering through Datasette's interface.

I favor keeping these tasks visibly distinct in the page. Grouping helps explain a dataset's distribution, while filtering by a row identifier selects a particular record. A repeated-value requirement makes automatic grouping suggestions more selective without taking that individual selection away.

Finally, explicitly displaying zero results completes the interaction when a chosen filter matches nothing. The absence of records becomes a stated query outcome. The reader can change the filter knowing that the previous request completed with an empty selection, rather than guessing what an empty region of the page means.