FilterHandler

Populate filter values

FilterHandler lets you create dropdowns for filters from data.

Creating filters is a non-trivial problem. Take this table:

By default, filters should show:

• city: Rome | Oslo
• product: Doll | Cap | Belt

But if the user SELECTS Rome, the filters should show:

• city: Rome | Oslo. Though Olso is selected, Rome is visible. That’s to allow CHANGING the city
• product: Doll | Belt. Not Cap, since they are not sold in Oslo

FilterHandler returns the list of filter values to display when a selection is made.

FilterHandler usage

Here is a sample configuration to filter data columns from a CSV file:

url:
  flags:
    pattern: /filter
    handler: FilterHandler
    kwargs:
      url: $YAMLPATH/city-products.csv

/filter returns {}, since we haven’t specified any columns to return

/filter?_c=city returns unique values for city:

{
  "city": [{ "city": "Oslo" }, { "city": "Rome" }]
}

/filter?_c=city&_c=product returns unique values for city and product:

{
  "city": [{ "city": "Oslo" }, { "city": "Rome" }],
  "product": [
    { "product": "Belt" },
    { "product": "Cap" },
    { "product": "Doll" }
  ]
}

/filter?_c=city&_c=product&_sort=-city&_sort=-product returns unique values for city and product, sorting both in descending order alphabetically:

{
  "city": [{ "city": "Rome" }, { "city": "Oslo" }],
  "product": [
    { "product": "Doll" },
    { "product": "Cap" },
    { "product": "Belt" }
  ]
}

/filter?_c=city&_c=product&city=Oslo returns unique values for city and product when city=Oslo is selected. Note that Cap is missing.

{
  "city": [{ "city": "Oslo" }, { "city": "Rome" }],
  "product": [{ "product": "Belt" }, { "product": "Doll" }]
}

FilterHandler example

FilterHandler Features

FilterHandler supports most FormHandler filters

• ?_c=Name ► unique values of Name column
• ?_c=Continent&_c=Name ► unique values of Continent and Name
• ?_c=Continent&_c=Name&Name=Andorra ► unique Continent by filtering Name=Andorra, and unique Name without filtering Name=Andorra
• ?_c=Name&_sort=-Name&_limit=10 ► 10 Names sorted descending
• ?_c=c1|min ► minimum of c1
• ?_c=c1|max ► maximum of c1
• ?_c=c1|sum ► sum of c1
• ?_c=c1|avg ► average of c1

To control the output, you can use these control arguments:

• Limit rows: ?_c=Name&_limit=10 ► show only 10 rows
• Sort order: ?_c=Continent&_sort=-Continent ► sort Continents (descending)

FilterHandler supports all files, databases and options supported by FormHandler. That includes:

• Connecting to files and databases
• Rendering different formats
• Support for downloads
• Transforms using prepare, function, or modify.
• Rendering templates

FilterHandler hierarchies

v1.85. You can create a single filters for multiple columns. /filter?_c=city,product returns unique values for the city and product combination:

{
  "city,product": [
    { "city": "Oslo", "product": "Belt" },
    { "city": "Oslo", "product": "Doll" },
    { "city": "Rome", "product": "Cap" },
    { "city": "Rome", "product": "Doll" }
  ]
}

This is particularly useful to create a filter with hierarchies like:

FilterHandler hierarchies example

FilterHandler Ranges

v1.86. FilterHandler can return ranges of values for a column using the _c=<col>|range syntax.

FilterHandler range example

For example, ?_c=c1|range&_c=c2|range returns the min and max values of columns c1 and c2:

{
  "c1|range": [
    {
      "c1|min": 0,
      "c1|max": 97
    }
  ],
  "c2|range": [
    {
      "c2|min": 0,
      "c2|max": 50
    }
  ]
}

This is useful for range filters like:

<input
  type="range"
  min="${filter['c1|range'][0]['c1|min']}"
  max="${filter['c1|range'][0]['c1|max']}"
/>

FilterHandler in memory

v1.86. FilterHandler runs a database query for each column that you request.

For slow database connections, you can speed this up with in_memory: true. For example:

url:
  flags:
    pattern: /filter
    handler: FilterHandler
    kwargs:
      url: $YAMLPATH/city-products.csv
      in_memory: true

When you request ?_c=city&_c=product, FilterHandler fetches all unique combinations of city and product into memory. Then it further creates combinations.

This only runs a single query, but uses a bit more memory.