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 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.
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.
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
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 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.
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
@ 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.
m-1: beginnning of last month M+1: end of next month m+15 {12345}: first working day after 16th
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
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 the Filterpanel
Parameters: |
|
---|
return a list of FilterTool for field_name
Parameter: | field_name – name of the field |
---|
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 |
---|
Replace the column of the output treeview with a customized one
Parameters: |
|
---|
Add a column among already defined fields in gui_fields
Parameter: | field_name – the field_name of the field |
---|
Apply a filter getting the value from the table/mask
This is a shortcut for the longer operation:
- add a filter widget
- set a value
- reload
it is meant to be called from a connect in a (varchar) widget by pressing C-M-f. It will:
- add a filter
- get the value for the filter from the text widget
- set that value in the filter widget
- disreguard the value
- launch realod
- disable the filter so that subsequent searches on different fields won’t be affected
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
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.
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.