Acces to db made easy

- Prev: Constraints
- Next: Totals

# Filters

Filtering is a very powerful feature of sqlkit. Any single field in a SqlWidget may become a filter criteria and many filters on a field are accepted. A filter panel handles the filters that may also belong to related table: in that case a join will be built when selecting (read the note on selecting through joins in Relationships)

As you can see in the image a filter panel gives also the opportunity to limit the query. Any filter can be enabled or disabled by clicking on it’s toggle.

The result of a filter operation is shown differently in Table or Mask: Table shows the result directly, Mask shows the list of selected records in the Filter Panel’s Output page:

Each record is shown with it’s __str__ representation that can be set in the way described in foreign key description & search field.

In this Output Page it’s possible to set a field and have records grouped by that field:

    t.filter_panel.tree = 'field_name'

## Filtering Foreign Keys

Filtering works also with foreign keys. In that case the filter acts on the filter that represents the record, what I call the “search” field of the record. In this case the operator used for the search defaults to regexp/match that in turn uses different operators in each database backend: ‘~*’ for postgresql, REGEXP for mysql and ILIKE for the others (well, ILIKE is not present in sqlite but it uses the sqlalchemy implementation of ilike) adding leading and trailing ‘%’ symbols.

## Adding Filters programmatically

Filters can be added programmatically via method `add_filter` that uses django_like syntax, of interactively. As an example:

    t.add_filter(numeric_field__gte=5, boolean_field=True)
    t2.add_filter(date_field__lte='y', string_field__null=False)

more examples can be found in the Constraints sections as they share the same syntax. Note that a filter can be changed by the user while a constraint is invisible to him.

### filters and join

When filtering programmatically on a join you must use the field_name as known by the mapper, i.e. composition of table_name + field_name. Look demo on join too see how it works:

    t = SqlTable(tables="movie director", dbproxy=db )
    t.add_filter(director_nation='IT') # NOTE director_nation

Here the field nation of table director is referenced as `director_nation`

## Expressions

Filter work just in the same way for real column as for expressions. Example 30 in the demo shows how to create a mapper that have a column with the number of film of a director, and you can verify that constraints and filter work on that table just as any normal column:

    class Director2(object): pass

    ## create the mapper as suggested in the SqlAlchemy tutorial...
    m = mapper(Director2, model.Director.__table__,
       properties={
        'film_number': column_property(
                select(
                    [func.count(model.Movie.__table__.c.id)],
                    model.Director.__table__.c.id == model.Movie.__table__.c.director_id
                ).label('film_number')
            )
      }
    )

    field_list = "last_name, first_name, nation, film_number"
    t = SqlTable(m, field_list=field_list, dbproxy=db)
    t.add_filter(film_number=3)
    t.add_constraint(film_number__lt = 5)

## Date filters

Date filters deserve a special chapter. It’s very common the need for a filter based on relative dates (i.e.: the beginning of the month, the year, the last month and so on), that’s the only way to allow saving queries that will behave the same all the time.

### Simple relative date algebra

a function that implements simple relative date algebra so that we can use it in bookmarks and queries.

Differently from what other packages do (as the very usefull relativedelta that is used in this module) datetools tries to use the term month as a period of length that depends on the ‘current’ month. End of february + 1month will be end of march, not 28th of march!

Allowed chars are:

    [-+ diwWmMyY @>]

letters refer to a moment of time (start/end of month, year, week) or period according to use: the forst token is a moment of time, the further ones are periods.

> d: | today
> i: | yesterday (‘ieri’ in italian, ‘y’ was already used by year)
> w: | beginning of week
> W: | end of week
> m: | beginning of month
> M: | end of month
> y: | beginning of year
> Y: | end of year

Math signs `+` and `-` work as you would expect they add and subtract period of time. When used this way the following letter refers to a period:

    m+14d

is the 15th day of the month (beginning of month + 14 days)

New in version 0.8.6.1.

If the first token is the end of the month, we try to stick to the end as much as possible, till another period is used, so that order is significant, note what follows that is extracted from the doctests, assuming the current day is June 2th:

    >>> dt.string2date('M-1m-2d')
    datetime.date(2007, 5, 29)

    >>> dt.string2date('M-2d-1m')
    datetime.date(2007, 5, 28)

You can also use a short form (compatible with previous syntax):

    m-1 == m-1m

You can use also > in which case the string is considered 2 dates, each built as already stated:

    m-1 > M+2

means a 3 months period, starting from beginnig of last month to end of next month

#### Periods

@ is a compact way to set a period:

    @m == m > M
    @m-1  == m-1 > M-1

New in version 0.8.6.1.

@ accepts also a quantity:

    @2m-1 = m-1 > M-1 +1m

that means a period of 2 months starting from beginning of last month.

#### Other examples

> m-1: | beginnning of last month
> M+1: | end of next month

### FilterPanel

The filter panel is the panel where all filter conditions can be written (remeber that constraints are different in the sense that are filters applied w/o possibility to remove them). It opens as a window separate from the main window so that it’s easy to hide or keep it at hand.

Each sqlwidget has a FilterPanel even if it doesn’t show it

class `sqlkit.widgets.common.sqlfilter.``FilterPanel`(master, visible=True)

  A panel that manages filter conditions of a query: number of records, field names and output to point & click in case of a panel of a SqlMask

  `hide`(widget=None, event=None)

    Hide the Filterpanel

    Parameters:

    - widget – not neeeded: it’ here to allow using it in callback
    - event – not needed: see above

  `show`()
    Present the filter panel
  `get_tools`(field_name)

    return a list of FilterTool for field_name

    Parameter: | field_name – name of the field

  `reload`()
    issue a reload operation on master. Callback of reload button
  `set_page`(action=None, name=None)

    set the tab in the filter. Tab name can be ‘filter’ or ‘output’. Sqlmasks only have ‘filter’.

    Parameter: | name – name of the tab in the filter widget: filter or output

  `tree`
    The name of an attribute that will work as grouping attribute. The output TreeView will show records grouped by the same attribute as parent/child. It should be improved as the parent is a the record and not a row with the only grouping attribute.
  `replace_column`(field_class, field_name='__obj__')

    Replace the column of the output treeview with a customized one

    Parameters:

    - field_class – a subclass of `sqlwidget.fields.Field` with a proper `clean_value` method
    - field_name – the field_name of the column, Default: `__obj__` (i.e. the name used for the default column of the output treeview

  `add_column`(field_name)

    Add a column among already defined fields in gui_fields

    Parameter: | field_name – the field_name of the field

class `sqlkit.widgets.common.sqlfilter.``FilterTool`(field_name, panel, master)

  A tool that handles the the filter and provides a mean to modify the query of the master (sqlwidget). With the FilterTool you can programmatically set the filter active/inactive, change the operator and the filter values.

  You will normally do all this with the `.add_filter` method of sqlwidget, but you may occasionally need to fine tune the filter in a second time

  `set_value`(value)
    set the value of the filter. It can be a string or an object (eg. a date())
  `get_value`()
    return the current value of the filter
  `set_operator`(op, value)
    Set the active operator entry in ComboBox/OptionMenu for operator choice :param op: the operator :param value: the value of the operator
  `get_operator`()
    return the active operator entry in ComboBox/OptionMenu

When the filter is used from a SqlTable, the output is displayed directly into the SqlTable. When the filter is used from a SqlMask the output is shown in a special tab of the FilterPanel that is really a View on the output that can be customized to a good degree. Default representation is `str(obj)` unless a you have defined a `format` in the database attribute description for that table.

#### Customizing the output tab

The default representation of records is a View with a single column named `__obj__` that is a field that creates a str(obj) as explained above.

If you want to change that representation you just need to substitute the column in the treeview:

    from sqlkit.import fields
    from sqlkit.db.utils import DictLike

    class CountMovies(fields.IntegerField):
        '''
        A field that counts the movies
        '''
        def clean_value(self, value):
            ## missing a field_name attribute on obj the objct itselt is passed
            return len(value.movies)

    my_mask.filter_panel.replace_column(CountMovies)

Alternatively you can add a column to the output view after creating the field and the column you would add it to the view as follows:

    count = CountMovies('n_movies')
    col = columns.VarcharColumn(t, 'n_movies', 'Movie Count', field=count)
    my_mask.filter_panel.view.add_column(col)

At this point you can sort the output on each column and even get totals in it.