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