Don't suggest column for faceting if all values are 1

[simonw]

https://www.niche-museums.com/museums/museums?_facet=wikipedia_url

Challenge is how to do this efficiently, since suggested facet queries need to be lightning fast.

[simonw]

We currently use select distinct wikipedia_url ... to suggest facets. This query would only return rows which are represented twice or more:

select
  wikipedia_url, count(*) as n
from
  museums
where
  wikipedia_url is not null
group by wikipedia_url
having n > 1

https://www.niche-museums.com/museums?sql=select%0D%0A++wikipedia_url%2C+count%28*%29+as+n%0D%0Afrom%0D%0A++museums%0D%0Awhere%0D%0A++wikipedia_url+is+not+null%0D%0Agroup+by+wikipedia_url%0D%0Ahaving+n+%3E+1

[simonw]

It's not as simple as that - planet_int should be a suggested facet on https://latest.datasette.io/fixtures/facetable?_facet=planet_int because it returns two filters, even though one of those two is a value of 1. Switching to the new proposed SQL statement misses this.

[simonw]

Demo: https://latest.datasette.io/fixtures/facetable doesn't suggest distinct_some_null as a facet.