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.

Note

Shortcut

As a shortcut to a tipical pattern (pop a filter panel, add a filter for a field, set a value, reload), it’s possible to write a filter string directly in the field and activate the filter/reload operation by Control-Alt-f. This will use ILIKE as search operator and is enabled where completion is enabled.

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 first token is a moment of time, the further ones are periods.

You can possibily add as a last tocken a set of week days that the computed date should shift to help in setting date as (first working date after 16 of month - in Italy we pay several taxes on that day ;-)

New in version 0.9.6.

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 m+15 {12345}:first working day after 16th

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

clear()

Destroy all filter widgets matered by this FilterPanel

short_filter(field_name, relationship_path=None)

Apply a filter getting the value from the table/mask

This is a shortcut for the longer operation:

1. add a filter widget
2. set a value
3. reload

it is meant to be called from a connect in a (varchar) widget by pressing C-M-f. It will:

1. add a filter
2. get the value for the filter from the text widget
3. set that value in the filter widget
4. disreguard the value
5. launch realod
6. disable the filter so that subsequent searches on different fields won’t be affected

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

destroy(window=None)

Destroy the FilterToold and related widgets and de-register from FilterPanel

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.