AutomaticFilters: Simple filtering

The AutomaticFilters extension provides a simpler approach to building filters using Ingredient.build_filter.

class recipe.AutomaticFilters(*args, **kwargs)[source]

Automatic generation and addition of Filters to a recipe.

Automatic filters take a dictionary of keys and values. For each key in the dictionary, if the key is the id of a Dimension on the shelf, a filter will be added to the recipe containing the values.

apply_automatic_filters(value)[source]

Toggles whether automatic filters are applied to a recipe. The following will disable automatic filters for this recipe:

recipe.apply_automatic_filters(False)
automatic_filters(value)[source]

Sets a dictionary of automatic filters to apply to this recipe. If your recipe uses a shelf that has dimensions ‘state’ and ‘gender’ you could filter the data to Men in California and New Hampshire with:

shelf = Shelf({
    'state': Dimension(Census.state),
    'gender': Dimension(Census.gender),
    'population': Metric(func.sum(Census.population)),
})
recipe = Recipe(shelf=shelf)
recipe.dimensions('state').metrics('population').automatic_filters({
    'state': ['California', 'New Hampshire'],
    'gender': 'M'
})

Automatic filter keys can optionally include an operator.

List operators

If the value provided in the automatic_filter dictionary is a list, the following operators are available. The default operator is in:

in (default)
notin
quickselect (applies multiple conditions matching the
  named quickselect, quickselects are ORed together)
between (requires a list of two items)

Scalar operators

If the value provided in the automatic_filter dictionary is a scalar (a string, integer, or number), the following operators are available. The default operator is eq:

eq (equal) (the default)
ne (not equal)
lt (less than)
lte (less than or equal)
gt (greater than)
gte (greater than or equal)
like (SQL LIKE)
ilike (Case insensitive LIKE)
quickselect (applies the condition matching the named quickselect)

An example using operators

Here’s an example that filters to states that start with the letters A-C:

shelf = Shelf({
    'state': Dimension(Census.state),
    'gender': Dimension(Census.gender),
    'population': Metric(func.sum(Census.population)),
})
recipe = Recipe(shelf=shelf)
recipe.dimensions('state').metrics('population').automatic_filters({
    'state__lt': 'D'
})

Compound filters

If the key provided in the automatic_filter dictionary contains a comma, the filters will be treated as compound. Compound operators will be matched to the values by splitting the key on the commas then zipping the keys to values.

For instance, you could find newborns in California and 20 year olds in New Hampshire with:

shelf = Shelf({
    'state': Dimension(Census.state),
    'age': Dimension(Census.age),
    'population': Metric(func.sum(Census.population)),
})
recipe = Recipe(shelf=shelf)
recipe.dimensions('state').metrics('population').automatic_filters({
    'state,age': [['California',0], ['New Hampshire',20]]
})

This would generate a SQL where clause that looked like:

WHERE
  (Census.state = 'California' and Census.age = 0) OR
  (Census.state = 'New Hampshire' and Census.age = 20)

Not all keys need to match in compound filters and may be provided. For instance, the following example uses operators and “unbalanced” keys:

recipe.dimensions('state').metrics('population').automatic_filters({
    'state,age__notin': [['California'], ['New Hampshire',[20,21,22,23]]]
})

This would generate a SQL where clause that looked like:

WHERE
  (Census.state = 'California') OR
  (Census.state = 'New Hampshire' and Census.age NOT IN (20,21,22,23))

Note: Using large numbers of compound filters is not efficient and may generate extremely large SQL.

exclude_automatic_filter_keys(*keys)[source]

A “blacklist” of automatic filter keys to exclude. The following will cause 'state' to be ignored if it is present in the automatic_filters dictionary:

recipe.exclude_automatic_filter_keys('state')
include_automatic_filter_keys(*keys)[source]

A “whitelist” of automatic filter keys to use. The following will only use 'state' for automatic filters regardless of what is provided in the automatic_filters dictionary:

recipe.include_automatic_filter_keys('state')

The AutomaticFilters extension.