Facets #255
Comments
|
Facet counts will be generated by extra SQL queries with their own aggressive time limit. |
|
Adding some TODOs to the original description (so they show up as a todo progress bar) |
|
Initial documentation: http://datasette.readthedocs.io/en/latest/facets.html |
|
I need to decide how to display these. They currently look like this:
|
|
I may be able to run the SQL for all of the facet counts in one go using a WITH CTE query - will have to microbenchmark this to make sure it is worthwhile: https://datasette-facets-demo.now.sh/fivethirtyeight-2628db9?sql=with+blah+as+%28select+*+from+%5Bcollege-majors%2Fall-ages%5D%29%0D%0Aselect+*+from+%28select+%22Major_category%22%2C+Major_category%2C+count%28*%29+as+n+from%0D%0Ablah+group+by+Major_category+order+by+n+desc+limit+10%29%0D%0Aunion+all%0D%0Aselect+*+from+%28select+%22Major_category2%22%2C+Major_category%2C+count%28*%29+as+n+from%0D%0Ablah+group+by+Major_category+order+by+n+desc+limit+10%29 |
|
It would be neat if there was a mechanism for calculating aggregates per facet - e.g. calculating the sum() of specific columns against each facet result on https://datasette-facets-demo.now.sh/fivethirtyeight-2628db9/nba-elo%2Fnbaallelo?_facet=lg_id&_facet=fran_id&lg_id=ABA&_facet=team_id |
|
Can I get facets working across many2many relationships? This would be fiendishly useful, but the querystring and |
|
To decide which facets to suggest: for each column, is the unique value count less than the number of rows matching the current query or is it less than 20 (if we are showing more than 20 rows)? Maybe only do this if there are less than ten non-float columns. Or always try for foreign keys and booleans, then if there are none of those try indexed text and integer fields, then finally try non-indexed text and integer fields but only if there are less than ten. |
To indicate if there was more than 20 distinct values. Refs #255
|
Activity has now moved to this branch: https://github.com/simonw/datasette/commits/suggested-facets |
|
New demo (published with After turning on a couple of suggested facets... https://datasette-suggested-facets-demo.now.sh/fivethirtyeight-2628db9/comic-characters%2Fmarvel-wikia-data?_facet=SEX&_facet=ID
|
|
I updated that demo to demonstrate the new foreign key label expansions: https://datasette-suggested-facets-demo.now.sh/sf-trees-02c8ef1/Street_Tree_List?_facet=qLegalStatus
|
|
Maybe |
|
Latest demo - now with multiple columns: https://datasette-suggested-facets-demo.now.sh/sf-trees-02c8ef1/Street_Tree_List?_facet=qCaretaker&_facet=qCareAssistant&_facet=qLegalStatus
|
To indicate if there was more than 20 distinct values. Refs #255
|
This is now landed in master, ready for the next release. |
If the user requests some _facet= options that do not successfully execute in
the configured facet_time_limit_ms, we now show a warning message like this:
These facets timed out: rowid, Title
To build this I had to clean up our SQLite interrupted logic. We now raise a
custom InterruptedError exception when SQLite terminates due to exceeding a
time limit.
In implementing this I found and fixed a logic error where invalid SQL was
being generated in some cases for our faceting calculations but the resulting
sqlite3.OperationalError had been incorrectly captured and treated as a
timeout.
Refs #255
Closes #269
|
This shipped in Datasette 0.22. Here's my blog post about it: https://simonwillison.net/2018/May/20/datasette-facets/ |
Ability to display facets and facet counts on the table view.
Facets can be specified in the URL with
?_facet=column&_facet=othercolumnor the default facets for a table can be set using a new"facets": [...]property inmetadata.json?_facet=metadata.jsonfacetskeyThe text was updated successfully, but these errors were encountered: